We all appreciate the hard work that those guys at Dell KACE do to make our lives easier as Tech’s and System Administrators, in particular the inclusion of formal SLA measures and working time calculations in the latest 6.4 release was, in my eyes, a long awaited “God-send”. However as with most technology firms the software is good, but does not always translate into day to day business usage, just as it is with the SLA functionality.

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 ())

 

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 ())

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 done 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.