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:  

where
        (HD_TICKET.ID in ())
        and HD_TICKET.CUSTOM_FIELD_VALUE7 = Null
        or HD_TICKET.CUSTOM_FIELD_VALUE7 = ' '

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
    set HD_TICKET.CUSTOM_FIELD_VALUE13 = {fn TIMESTAMPDIFF (SQL_TSI_MINUTE,HD_TICKET.CREATED,HD_TICKET.CUSTOM_FIELD_VALUE7)}/60

 where
        (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. 

Regards,

Ron

       




0 Comments   [ + ] Show Comments

Comments

Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity