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. 




0 Comments   [ + ] Show Comments


Please log in to comment

There are no answers at this time


Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja