/build/static/layout/Breadcrumb_cap_w.png

Problem with SLA Timer Custom Modified Field SOLVED

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.


Comments

  • I actually had an "opposite" issue where I have rules sending out reminder emails to ticket owners or users when a ticket is in a certain status. butttt the ticket wouldn't update so I had to add a part in the rule to set the HD_TICKET.MODIFIED to NOW() after it sent the email. - Wildwolfay 10 years ago
This post is locked

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ