I have been following the series to create an SLA timer in the Kace Service Desk by GillySpy: http://www.itninja.com/blog/view/creating-true-sla-timers-in-the-kbox-helpdesk-part-1-what-you-really-want-from-your-data

I posted a fix for the rules posted so far not updating the columns and failing to run here: http://www.itninja.com/blog/view/update-to-sla-timer-ticket-rules-series

I was working on some other ticket rules and ran into an issue that required another fix. The SLA timer creates a custom modified field which it uses to continuously increment the timer and keep track fo the time. I discovered, however, that the default Modified column that comes with Kace out-of-the-box was being updated for every ticket in the HD_TICKET table when the increment rule runs every 15 minutes.

This means that any rule that is based on the default modified field will be affected. I have escalation rules, for example, that run daily and check to see if a ticket is in the status "New" and the modified field has not been changed in 10 days.

S.NAME = 'New' AND DATEDIFF(NOW(), HD_TICKET.MODIFIED) >= 10 AND DATEDIFF(NOW(), HD_TICKET.MODIFIED) < 11

The SLA Timer rule means that this escalation rule will never find a ticket because the modified field will never be older than 15 minutes.

In order to fix the rules in a way that will not depend on this modified column, I went in search of a column that was keeping track of changes and not affected by the SLA timer. Thanks to chucksteel for helping me locate the Timestamp field in the HD_TICKET_CHANGE table and put together the SQL for fixing these rules.

Here's the Join statement to include:

JOIN HD_TICKET_CHANGE LAST_CHANGE ON LAST_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and LAST_CHANGE.ID=(select MAX(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)

and here's the previous query updated to use the Timestamp column from the HD_TICKET_CHANGE table:

S.NAME = 'New' AND DATEDIFF(NOW(), LAST_CHANGE.TIMESTAMP) >= 10 AND DATEDIFF(NOW(), LAST_CHANGE.TIMESTAMP) < 11

So, that's the fix for the problem. I still wonder whether the custom modified field is necessary for the SLA Timer or if there is a way to use the timer so that the default modified field does not get updated by the increment counter.