/build/static/layout/Breadcrumb_cap_w.png

Update to SLA Timer Ticket Rules Series

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:

select HD_TICKET.ID
from 
            HD_TICKET
        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'
where
  /* only care about tickets where relevant time has changed */
    NOW()>ifnull(cast(CUSTOM_FIELD_VALUE14 as datetime),'0000-00-00')
    and
    (
        LENGTH(HD_TICKET.CUSTOM_FIELD_VALUE12)>0 OR
        LENGTH(HD_TICKET.CUSTOM_FIELD_VALUE13)>0
    )

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
    (
        LENGTH(HD_TICKET.CUSTOM_FIELD_VALUE12)>=0 OR
        LENGTH(HD_TICKET.CUSTOM_FIELD_VALUE13)>=0
    )

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.


Comments

  • Hi Imland, you seem to get these SLA posts so I wanted to ask - I've basically copied the original queries to my kbox and modified them with your updates and it is counting but it seems to be counting wrong. For example the built-in 'Time opened' field reports 1 hour, then right next to it the 'Total time opened' from this SLA series shows way less, like 0.3 or 0.02 something like that. If I've basically copied and pasted and the fields are reporting then how can it be so wrong? It is actually in hours yes?
    Thanks. - twit 11 years ago
  • Hey twit,

    I'm no expert by far. My understanding is that the Time Opened field counts the moment a ticket is created until the time it is moved into a closed status. The SLA rule divides that time up between the Open and Stalled states to give a more accurate count. Hope that helps. - lmland 11 years ago
  • Thanks for the reply mate. Mine isn't working but it is reporting a value. The value on one ticket has been set at 'total hours opened' = 0.018 but the ticket's been open 17 hours. I suspect the rule for incrementally updating the counter isn't working, despite applying your >=0 fix. Not your problem but just wanted to reply to your message, cheers. - twit 11 years ago
This post is locked
 
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