Custom Ticket Rule - email alert on ticket w/o owner (hourly)
So, unfortunately, we don't have any SQL gurus on staff, and I went through the wizard, which effectively does what I want, but it returns way too much data to make the alert useable. I need an email to be sent to the help desk manager when a new ticket is created and hasn't been assigned an owner (after a 1 hour grace period). The code that KACE created for me works very well, EXCEPT it give me way too many variables. ALl I need is the ticket ID/# and the title (and the submitter would be nice). Here's the code it gives me (with 61 variables per ticket):
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 - Not Started') AND HD_TICKET.OWNER_ID = '0') and HD_TICKET.HD_QUEUE_ID = 5 )
Any help is greatly appreciated, thanks!
Alex
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
svmay
7 years ago
Hello Alex,
have you set the checkbox "Send an e-mail to each recipient in query results." ?
In there - you can configure the information you want to have to - with variables.
have you set the checkbox "Send an e-mail to each recipient in query results." ?
In there - you can configure the information you want to have to - with variables.
Posted by:
chucksteel
7 years ago
It sounds like you are using the Email Results option, which isn't exactly what you want in this case. You should be using the Email each recipient in query results option as smvay says. In order to use that option you will need to make a small modification to the select query. After this line:
Q.NAME as QUEUE_NAME add the following:
, "helpdeskmanager@company.com" as HELPDESKMANAGER
Use the actual email address for the helpdesk manager (and don't forget the comma).
Now you can use HELPDESKMANAGER as the value for Column Containing email addresses in the option for sending the email. Also, all of the columns selected will be available as variables for the email subject and message as $variable. The email you are currently receiving should show you those columns.
Comments:
-
I added in the new line, and edited the email address - this is working properly (in as much as I am getting emails). However, for one ticket without an owner, I received 4 email alerts.
Also, can you give a bit more clarity about the variable names I can use? I tried to use $HD_TICKET and $SUBMITTER_NAME but those came through as text, not as the values. - amoratorio 7 years ago-
The variables need to be in lower case, that should fix $submitter_email. There isn't a column called HD_TICKET, that's a table name. If you want ID that would be $id.
Any columns selected in the statement will be made available as variables. If you look through the statement most of the lines end with AS SOMETHING, that SOMETHING will be $something in the email. - chucksteel 7 years ago-
Fantastic - very helpful, thank you. So, it's working as designed, but what I was hoping for was a single email with all results, not an individual email for each result. Any chance that's possible? - amoratorio 7 years ago
-
Two options:
Create a report and have the report scheduled to run on a schedule.
Use the rule but change it back to the Email Results option and reduce the columns being selected in the statement. - chucksteel 7 years ago -
Can't reply to your latest response for some reason - using Email Results is the way I started (and prefer) but both you and svmay directed me to this format instead. If I wanted to go back to the single email - what portion of the SQL code should I modify to only pull three variables (ticket number, submitter and ticket title)? Thanks so much! - amoratorio 7 years ago
-
Replies can only go three levels on ITNinja.
If you just want those three columns then this is your select statement:
select HD_TICKET.ID as "Ticket Number",
HD_TICKET.TITLE,
U2.FULL_NAME as SUBMITTER_FULLNAME
from HD_TICKET
LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
WHERE ((( HD_STATUS.NAME = 'New - Not Started') AND HD_TICKET.OWNER_ID = '0') and HD_TICKET.HD_QUEUE_ID = 5 ) - chucksteel 7 years ago -
I get the following error:
05/08/2017 11:15:01> Starting: 05/08/2017 11:15:01 05/08/2017 11:15:01> Executing Select Query... 05/08/2017 11:15:01> mysqli error: [1054: Unknown column 'HD_STATUS.NAME' in 'where clause'] in EXECUTE("select HD_TICKET.ID as "Ticket Number", HD_TICKET.TITLE, U2.FULL_NAME as SUBMITTER_FULLNAME from HD_TICKET LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID WHERE ((( HD_STATUS.NAME = 'New - Not Started') AND HD_TICKET.OWNER_ID = '0') and HD_TICKET.HD_QUEUE_ID = 5 )") - amoratorio 7 years ago -
D'oh. Forgot about the status.
select HD_TICKET.ID as "Ticket Number",
HD_TICKET.TITLE,
U2.FULL_NAME as SUBMITTER_FULLNAME
from (HD_TICKET, HD_STATUS)
LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
WHERE ((( HD_STATUS.NAME = 'New - Not Started') AND HD_TICKET.OWNER_ID = '0') and HD_TICKET.HD_QUEUE_ID = 5 )
and HD_STATUS.ID = HD_STATUS_ID - chucksteel 7 years ago -
And now, this error:
05/08/2017 11:22:43> Starting: 05/08/2017 11:22:43 05/08/2017 11:22:43> Executing Select Query... 05/08/2017 11:22:43> mysqli error: [1054: Unknown column 'HD_STATUS.NAME' in 'where clause'] in EXECUTE("select HD_TICKET.ID as "Ticket Number", HD_TICKET.TITLE, U2.FULL_NAME as SUBMITTER_FULLNAME from HD_TICKET LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID WHERE ((( HD_STATUS.NAME = 'New - Not Started') AND HD_TICKET.OWNER_ID = '0') and HD_TICKET.HD_QUEUE_ID = 5 )") - amoratorio 7 years ago -
You didn't change the select statement, it's the same error. - chucksteel 7 years ago
-
arg, bummer - I changed the update SQL statement - and so now I need to start all over again (or can I leave the update statement blank as we aren't changing anything? - amoratorio 7 years ago
-
Yes, you can uncheck the box to update the ticket. - chucksteel 7 years ago
-
Thank you very much! This works exactly as I was hoping now. Really appreciate the assist! - amoratorio 7 years ago
