Average time to ticket close report


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.



1 Comment   [ + ] Show comment
  • I dont understand why this is not a canned report in the Kace. Surely everyone would find this report valuable - johnbodden 3 months ago

Answers (1)

Posted by: chucksteel 2 years ago
Red Belt

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.

  • 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 2 years ago
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