So the ability to set the due date and to see on a ticket list if the ticket is showing as Warning or Overdue is great but has anyone tried to write reports and crack open the back-end to see how this all works?
My solution to this is simple, first let's create a custom, single select field in the ticket Queue, call it SLA Flag and populate it with three values:
- OK (Default for all new tickets)
- Warning
- Late
Make a note of the custom field value
Next we need to create two ticket rules in the relevant Queue, one to turn the default "OK" value, in our custom field, to "Warning" when the SLA time comes within a predefined margin of the due date, and a second one to change the "Warning" value to "Late" when the SLA time is breached.
Warning Ticket Rule
You can create a single rule to alert when any priority SLA is close, i.e 2 hours from SLA. If you have a 4 Hours P1 SLA that 2 hour window works well, but for an SLA of 5 days, it could be deemed a little short notice. If that is the case you will need to create a set of Warning rules, one for each defied Priority of tickets.
Sample code for the Warning SLA rule is shown below:
Select SQL
SELECT HD_TICKET.ID, HD_TICKET.TITLE, HD_TICKET.DUE_DATE, HD_PRIORITY.NAME AS PRIORITY, Q.NAME AS QUEUE_NAME, HD_STATUS.NAME AS STATUS_NAME FROM HD_TICKET JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 3) AND (((TIMESTAMP(HD_TICKET.DUE_DATE) >= NOW() AND TIMESTAMP(HD_TICKET.DUE_DATE) < DATE_ADD(NOW(),INTERVAL 1 HOUR))))
The two highlighted areas show the Queue ID and also the warning time, in this case alert 1 hour before SLA. If this rule is to be created per Priority you would have to add in HD_TICKET.HD_PRIORITY_ID = x to the Where portion of the SQL statement.
Update SQL
update HD_TICKET
set HD_TICKET.CUSTOM_FIELD_VALUE5 = 'Warning'
where
(HD_TICKET.ID in (<TICKET_IDS>))
The highlighted field reference in the update statement will need to be changed to match the Custom field you created as your SLA Flag.
It would also be wise to append a comment to the ticket such as "SLA flag set to Warning by Ticket rule" and may be also configure the rule to send an email to the ticket owner as a reminder.
So we also need to create a rule to set the flag field from "Warning" to "Late"
LATE Ticket Rule
Select Statement
SELECT HD_TICKET.ID, HD_TICKET.TITLE, HD_TICKET.DUE_DATE, HD_PRIORITY.NAME AS PRIORITY, Q.NAME AS QUEUE_NAME, HD_STATUS.NAME AS STATUS_NAME FROM HD_TICKET JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 3) AND ((HD_STATUS.NAME != 'Closed') AND ((TIMESTAMP(HD_TICKET.DUE_DATE) < NOW() OR TIMESTAMP(HD_TICKET.DUE_DATE) >= DATE_ADD(NOW(),INTERVAL 10 DAY)))) ORDER BY ID
Update Statement
update HD_TICKET
set HD_TICKET.CUSTOM_FIELD_VALUE5 = 'Late'
where
(HD_TICKET.ID in (<TICKET_IDS>))
The highlighted field reference in the update statement will need to be changed to match the Custom field you created as your SLA Flag.
As with the Warning rule it would also be wise to append a comment to the ticket such as "SLA flag set to LATE by Ticket rule" and also configure the rule to send an email to the ticket owner as a reminder.
SLA Reporting
SLA reporting now becomes straightforward as it is doen by the SLA flag field, which can be easily displayed on the ticket list layout, or within the ticket. You will also be able to create simple SLA reports from the wizard for current and closed calls as you require.
Other Blogs by Hobbsy
Have you tried the new Dell KACE Benchmark survey - Here
Wanted - Dell KACE customers to trial our new Dashboard Product - Here
BarKode Scanner for Dell KACE Asset Management - Here
Create Regular Scheduled tickets in Dell KACE - Here
Financial Asset Management in Dell KACE - Here
Patch on the first xxxday of the month - Here
Here is the code I have:
SELECT HD_TICKET.ID, HD_TICKET.TITLE, HD_TICKET.DUE_DATE, HD_PRIORITY.NAME AS PRIORITY, Q.NAME AS QUEUE_NAME, HD_STATUS.NAME AS STATUS_NAME FROM HD_TICKET JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1)
AND (HD_TICKET.HD_PRIORITY_ID = 1)
AND (((TIMESTAMP(HD_TICKET.DUE_DATE) >= NOW()
AND TIMESTAMP (HD_TICKET.DUE_DATE) < DATE_ADD(NOW(),INTERVAL 2 HOUR))))
I'm hoping someone can provide some guidance. I'd like to have a rule for each of my four priority levels (Medium, Low, High and Critical). - scarsey 7 years ago