Report amount of time ticket stalled
I'm trying to calculate the total amount of time spent on a ticket. I want to include stalled tickets, but can't seem to find where the start/stop times are for when a ticket is in a stalled state. This is the query I have so far. HD_TICKET.CREATED AND HD_TICKET_CLOSED only show the last action.
Select count(HD_TICKET.ID) as Total_Closed from HD_TICKET
left join HD_QUEUE on HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
where HD_QUEUE.ID = '3'
and HD_STATUS.STATE = 'closed'
and HD_TICKET.CREATED BETWEEN DATE_SUB(CURDATE(), INTERVAL 4 week) and CURDATE()
and DATEDIFF(HD_TICKET.TIME_CLOSED, HD_TICKET.CREATED) < 2
) as 'Total Closed 1 day or less',
Answers (1)
Well that is going to be a challenge for you, for the last decade, since KACE introduced SLA’s, working time etc I have been asking them to provide a method for stopping the clock so that ticket duration can be measured more accurately. Please feel free to join the chorus!!
the only way you can do this is to record the time stamps when the ticket goes into a hold state, status change and when it comes out, and then calculate that time and subtract from the overall ticket duration.
That activity can be found in the ticket Change table, but this only works if the ticket is on hold once.
The alternative is to implement some old SLA code which we use occasionally that has a counter that adds up the time on hold, if you would like a copy of that to try, please email me.
