Computing Time to Acknowledge on Incidents

I'm trying to set up some type of basis to establish a 'Time to Acknowledge' SLA measurement.  I've developed a process workflow that establishes a foundation of status updates giving the ticket a lifecycle outside the tool. Our Incident Management teams will be trained accordingly. I've established status changes in our Incident Queue to align to that process workflow. Also, a rule that once the ticket is in "Assigned" status that an Acknowledge date/time populates upon save given the select SQL Status = 'Assigned' AND Assignee is 'Not Null'.  This sets HD_TICKET.CUSTOM_FIELD_VALUE7 = {fn NOW()}.  Adding addition lines in to the update SQL:  

        (HD_TICKET.ID in ())

So my original date is only captured once and not over written upon multiple ticket saves.

Now I am setting up a second rule to perform the differential computation between 'created date/time' and 'acknowledge date/time'. The Select SQL uses the next status change HD_STATUS.NAME = 'In Progress') AND ( exists  (select 1 from USER where HD_TICKET.OWNER_ID = USER.ID and USER.USER_NAME is not null)) ) and HD_TICKET.HD_QUEUE_ID = 10 ) 

My Update SQL is very basic. I cannot explain why SQL_TSI_MINUTE computes accurately then allows /60 to convert into hours. If I use SQL_TSI_HOUR I am not getting an accurate number. (???) Anyway, my challenge is trying to determine if I can pull HD_Buisness_Hours and HD_Holidays into the update set to deduct non-business hours time? 

Current Update SQL;

update HD_TICKET

        (HD_TICKET.ID in ())

I know being this close there must be a way. I just need assistance in the SQL coding. My overall wish would be to have the HD_TICKET.CUSTOM_FIELD_VALUE13 field utilize the priorities and just reflect 'Missed' or 'Met'.  Say an evaluation that:

P1 = Responded to within 30 min. (during business hours/minus holidays)

P2 = Responded to within 90 min. (during business hours/minus holidays)

P3 = Responded to within 4 hours (during business hours/minus holidays)

I know this can be done in Excel, however, I wish for this to be done at the ticket level. Then, allowed to be extracted into Kace reporting for operational DSR discussions. If anyone can assist, as you can tell I am not officially trained in programming but have been around ITSM tools for some time and know just enough to be dangerous. Thank you in advance. 




1 Comment   [ + ] Show comment
  • Did you make any progress on this? - JC_Chi 2 years ago

Answers (0)

Be the first to answer this question

Don't be a Stranger!

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

Sign up! or login

View more:


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