Average time to ticket close report
Hello,
We have been using the K1000 for a couple of years but we are still trying to figure out how to accurately report on the average time taken to close a ticket. However; we do not want to include time that the ticket was "Stalled" due particular statuses (i.e. Waiting...)
Does anyone know of a current way to do this? We are on v9.0 of the appliance. I noticed there is not timers or a second timestamp to show when a ticket is no longer stalled, etc...
ideally, what I am looking for is: timestampdiff(x, tbl.created, tbl.time_closed) - (tbl.time_stalled) or something similar.
Thanks,
KM
-
I dont understand why this is not a canned report in the Kace. Surely everyone would find this report valuable - johnbodden 4 years ago
Answers (1)
Yeah, this is crazy complicated. You can find when a ticket changed to a stalled state by checking the HD_TICKET_CHANGE_FIELD table where FIELD_CHANGED is "HD_STATUS_ID". This would need to be joined to the HD_STATUS table to get the state of the status (open, stalled or closed). You would also need to join to the HD_TICKET_CHANGE table to get the timestamp of the change and the ticket ID. Of course, that just gets you the times when the state of the ticket changed, you still need to find the difference in times to determine how long a ticket was stalled.
I think that a better option might be to use a ticket rule that would use one of the custom ticket values to store the time a ticket is stalled. Perhaps a rule that matches tickets that are saved where the state has changed from a stalled state to a non-stalled state. The tricky bit would be creating an update statement capable of finding the previous change that placed the ticket in a stalled state and calculating the time from that change to this change, which can then be added to the value already in the custom field.
Creating something in a tool like PowerBI would make this easier...probably.
Comments:
-
Thank you for your answer! I just recently connected PowerBI and currently researching that option. I figured, I could map each "change" per ticket and subtract those values from the total time. I was just hoping for an "All-in-one" solution within the appliance. Perhaps it's a pipe dream =) - kmccully 5 years ago
