Unassigned Ticket Notification Rule
Created an unassigned ticket rule for any ticket unassigned for more than 30 minutes . Trying to get it to send notification to Queue owners instead of individual email address.
second part is when emailing results to a specific email the results are not formatted.
How do i get it to email the queue owners and have the email formatted to be readable
Answers (3)
First using a ticket rule restricts you to a single email address, so if you need to email multiple people you will probably need to create a mailing list which has its own single email address.
If you hide that email in a custom field, ie make it the default value and a hidden field you can call it as a data value in the ticket rule.
Hopefully that helps
i have this for the sql query , i would like it to notify the queue owners . the query works just need statement to tie it to Email each recipient in query results with Column containing email addresses. Every time i try a different statement from other articles the query crashes
select HD_TICKET.*,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
STATE,
if(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(HD_TICKET.APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID
and HD_STATUS.ID = HD_STATUS_ID
and HD_IMPACT.ID = HD_IMPACT_ID
and HD_CATEGORY.ID = HD_CATEGORY_ID
and ((( HD_STATUS.NAME = 'New') AND HD_TICKET.OWNER_ID = '0') and HD_TICKET.HD_QUEUE_ID = 3 )
Comments:
-
Again, as I said in my first answer, I’m pretty sure you can only add in a single email address in the addressee box a single email address. So no matter how good your SQL is you will only be able to send a single email per ticket….I think - Hobbsy 2 years ago
-
I think there may be some confusion here. I am not looking to "Email the Results", that option is unchecked. We checked off "Email each recipient in query results" and are looking for statement for the creation/selections of "Email Column" - jjayko 2 years ago
Top Answer
Got it to work sending to Label using the following SQL Code
Select
HD_TICKET.*,
HD_STATUS.NAME As STATUS_NAME,
HD_IMPACT.NAME As IMPACT_NAME,
HD_CATEGORY.NAME As CATEGORY_NAME,
HD_PRIORITY.NAME As PRIORITY_NAME,
HD_STATUS.STATE,
U2.FULL_NAME As SUBMITTER_FULLNAME,
U2.EMAIL As SUBMITTER_EMAIL,
Q.NAME As QUEUE_NAME,
(Select
Group_Concat(USER.EMAIL Separator ', ') As ADDRESSLIST
From
LABEL Inner Join
USER_LABEL_JT On USER_LABEL_JT.LABEL_ID = LABEL.ID Inner Join
USER On USER.ID = USER_LABEL_JT.USER_ID
Where
LABEL.NAME = 'Helpdesk Notifications') As GROUPMAIL
From
HD_TICKET Left Join
USER U1 On U1.ID = HD_TICKET.OWNER_ID Left Join
USER U2 On U2.ID = HD_TICKET.SUBMITTER_ID Left Join
USER U3 On U3.ID = HD_TICKET.APPROVER_ID Left Join
HD_QUEUE Q On Q.ID = HD_TICKET.HD_QUEUE_ID Left Join
MACHINE M1 On M1.ID = HD_TICKET.MACHINE_ID,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY
Where
HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID And
HD_STATUS.ID = HD_TICKET.HD_STATUS_ID And
HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID And
HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID And
HD_TICKET.OWNER_ID = 0 And
HD_TICKET.HD_QUEUE_ID = 3 And
HD_STATUS.NAME = 'New'
Then just select Email each recipient in query results fill in subject variables and GROUPMAIL for The Column
Comments:
-
Thanks for this. helped me a lot. I didnt know you could use two FROM statements. I was trying to work it all into one and was failing.
Anyway, I wanted to be able to be certain it was a new ticket and I also wanted to only send when the category was something in particular. So stole your email list build and incorporated it into my rule which sends on a new ticket to a distribution list.. (removed DL). I have not tested the email yet, its in my dev server which cant send or receive email but fairly confident it builds the comma separated list as you wrote it. Quite frankly, I dont know what the two last commands are there for and if they are necessary even. This was originally provided by kace pro services a few years ago. CTEXT is the category name and C.description is the very last "Change" that was made to the ticket.. I think it uses some sort of built-in variable (<CHANGE_ID>).
EDIT: I tested this today and it does work correctly. The reason I made this change is because with the authors script if someone were to go in and say press apply changes, the email would be sent again.
SELECT
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_TICKET.TITLE, -- $title
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
-- change fields
C.COMMENT, -- $comment
C.DESCRIPTION, -- $description
-- about the updater
UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
-- about the owner
OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
-- about the submitter
SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
-- about priority
P.NAME AS PRIORITY, -- $priority
S.NAME AS STATUS, -- $status
I.NAME AS IMPACT, -- $impact
CAT.NAME AS CATEGORY, -- $category
-- group email addr Comma Separated
(SELECT GROUP_CONCAT(USER.EMAIL SEPARATOR ', ') AS ADDRESSLIST
FROM
LABEL
INNER JOIN USER_LABEL_JT ON USER_LABEL_JT.LABEL_ID = LABEL.ID
INNER JOIN USER ON USER.ID = USER_LABEL_JT.USER_ID
WHERE
LABEL.NAME = 'GRP_ACCESS_BADGE_ADMINS') AS GROUPMAIL
FROM HD_TICKET
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID AND C.ID=<CHANGE_ID>
/* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
/* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
/* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
/* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
/* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
/* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
/* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
/* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
LEFT JOIN HD_CATEGORY CTEXT ON (CTEXT.ID = HD_TICKET.HD_CATEGORY_ID)
WHERE
C.DESCRIPTION LIKE '%CREATED%' AND
HD_TICKET.OWNER_ID = 0
AND HD_TICKET.HD_QUEUE_ID = '4'
AND (CTEXT.NAME like '%Access Control%')
GROUP BY HD_TICKET.ID
HAVING 1=1 - barchetta 2 years ago