I've created a ticket rule that will e-mail detailed information of a ticket to the owner of a ticket before SLA is breached (we like to be able to have a notification of this to avoid breaching SLA and just a general reminder) and wanted to share it. You will have to modify the code depending on your escalation times and priority names.

Select Query Code:
select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
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,
HD_TICKET.ID AS TICKNUM,
CAT.NAME AS CATEGORY,
HD_TICKET.TITLE,
I.NAME AS IMPACT,
P.NAME AS PRIORITY,
STATE,
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(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
JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_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_PRIORITY.NAME = 'LOW') and unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED) > (unix_timestamp(HD_TICKET.TIME_OPENED) +432000) - unix_timestamp(HD_TICKET.TIME_OPENED) and unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED) < (unix_timestamp(HD_TICKET.TIME_OPENED) +432960) - unix_timestamp(HD_TICKET.TIME_OPENED)))
OR (HD_PRIORITY.NAME = 'MEDIUM') and unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED) > (unix_timestamp(HD_TICKET.TIME_OPENED) +108000) - unix_timestamp(HD_TICKET.TIME_OPENED) and unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED) < (unix_timestamp(HD_TICKET.TIME_OPENED) +108960) - unix_timestamp(HD_TICKET.TIME_OPENED)))
OR (HD_PRIORITY.NAME = 'HIGH') and unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED) > (unix_timestamp(HD_TICKET.TIME_OPENED) +21600) - unix_timestamp(HD_TICKET.TIME_OPENED) and unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED) < (unix_timestamp(HD_TICKET.TIME_OPENED) +22560) - unix_timestamp(HD_TICKET.TIME_OPENED)))
OR (HD_PRIORITY.NAME = 'EMERGENCY') and unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED) > (unix_timestamp(HD_TICKET.TIME_OPENED) +2700) - unix_timestamp(HD_TICKET.TIME_OPENED) and unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED) < (unix_timestamp(HD_TICKET.TIME_OPENED) +3660) - unix_timestamp(HD_TICKET.TIME_OPENED)))
and HD_TICKET.HD_QUEUE_ID = 5)


To modify this code depending on your escalation times, modify the parts in bold (HD_PRIORITY.NAME = 'EMERGENCY') and unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED) > (unix_timestamp(HD_TICKET.TIME_OPENED) +2700) - unix_timestamp(HD_TICKET.TIME_OPENED) and unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED) < (unix_timestamp(HD_TICKET.TIME_OPENED) +3660) - unix_timestamp(HD_TICKET.TIME_OPENED). +2700 is the % of the escalation time that you want the notice to go out (we do 75% of the escalation time for the priority) and +3660 is 16 minutes past the previous number to allow for a window of time since the ticket is run every 15 minutes. To determine when you want to send a notification, the +*** number, is seconds from 0 that this will send the notification of a priority, the DATE_ADD does not work for this since it is a unix_timestamp.

Frequency:
15 minutes

Send E-mail Code:
Subject
[Ticket:$ticknum] is at 75% escalation
Email Column:
OWNER_EMAIL
Body
Your Ticket:$ticknum for $submitter_fullname is at 75% escalation, you can view this ticket online at http://k1000/userui/ticket.php?ID=$ticknum

The ticket details are:
Ticket: $ticknum
Category: $category
Title: $title
Impact: $impact
Priority: $priority
Submitter: $submitter_fullname ($submitter_email)
Status: $status_name
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1
Good post, there are some things to be aware of before running the above as it will not work for everyone.

A ticket rule runs every 15 minutes on the hour while the escalation process runs every minute. So if your ticket is saved at 10:46 then the next time the rule will run in 11:00am, but the escalation timer will be running every minute before then. Depending upon your escalation settings your advance warning timer may not be very useful to you.

The following SQL could represent the crossing of 75% mark of the timer threshold but less than the threshold itself:
... WHERE ...
and NOW() >= DATE_SUB(TIME_OPENED, INTERVAL .75 * HD_PRIORITY.ESCALATION_MINUTES MINUTE)
and NOW() < DATE_SUB(TIME_OPENED, INTERVAL HD_PRIORITY.ESCALATION_MINUTES MINUTE) -- this line optionally can be removed if you want it to keep firing after escalation
Answered 12/28/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
I'm new to Kace and I've never had to do anything with MySQL before my current job (been here for 2 months) so this has all been a learning experience.

So with your code modification and NOW() >= DATE_SUB(TIME_OPENED, INTERVAL .75 * HD_PRIORITY.ESCALATION_MINUTES MINUTE) this could replace all of my extraneous code under the "where" statement and it will do the same thing? or is this going to continue running and reporting every 15 minutes after it's breached the 75% mark?
Answered 12/28/2011 by: natearms
Senior Yellow Belt

Please log in to comment
0
The way that you wrote your rule it will keep firing when the threshold is exceeded by more than X minutes (i.e. 75%). With this modification you could remove all the time comparisons and just use the one line you reference. If you want the traditional escalation timer to "take over" at 100% then use the two line version I posted.
Answered 12/28/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity