I was going through the SQL for the rules to create a SLA Timer from this series: http://www.itninja.com/blog/view/creating-true-sla-timers-in-the-kbox-helpdesk-part-iii-the-rules. I noticed that the custom fields for Modified and Last updated are working correctly, but it appears that the fields for Total Time Opened and Total Time Stalled are not updating properly. They continue to contain 0.
I see that the first rule adjusts Total Time Opened and Total Time Stalled to '0'on ticket creation. The rule that should be ticking the counter every 15 minutes has this as the select query:
JOIN HD_FIELD QF ON QF.HD_QUEUE_ID=HD_TICKET.HD_QUEUE_ID
and FIELD_LABEL='Counter Last Updated' and QF.NAME='CUSTOM_15'
/* only care about tickets where relevant time has changed */
NOW()>ifnull(cast(CUSTOM_FIELD_VALUE14 as datetime),'0000-00-00')
If the fields are set to '0', then the rule to incrementally increase these two fields will never run because they will always = 0 and never be >0. Am I right? I changed the rule to incrementally update the counter to say
and it appears to be working now.
There was also a reference to a column 'HOURS.BIZ' that hasn't been created in the tutorial yet that was giving me an error because it doesn't exist. Maybe the tutorial could be edited to add this in when the Business Hours piece is created in the next steps.
I believe the same problem might exist in the Proact to OTS Rules that adjust status. When the ticket saves if the ticket was just created in the last 15 minutes the custom fields will still = '0' and the change in status will not register. Changing it to >=0 should solve that issue here as well.