KACE Product Support

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