KACE1000 (KBOX) Report for Overall Time Spent in Each Ticket Status
Hello!
I was hoping I could get some help for a KACE Report. I would like to create a report with SQL that displays the overall time spent in each of a ticket's statuses. We have various different statuses that a ticket goes through in KBOX:
Awaiting Business Action
Awaiting Confirmation in Production
Awaiting PE Date
Cancelled
Closed
Closed - Pending Confirmation in PE
Future Consideration
Need More Info
New
Open - Approved by Business
Pending Business Approval
Quality Testing Complete
Ready for Development
Ready to Test in Development
Ready to Test in Quality
Transport to Production
Transport to Quality
Waiting for QE Date
Work in Process - CoE
Work in Process - Development
One can go through the history of a ticket and can manually see how much time a ticket has spent in a certain status, but we would like to have a report that would show the times spent in each status for all of our tickets.
Ideally, the column headers would be Ticket ID #, Ticket Title, CoE Owner, and then all of the statuses listed above.
Any help will be appreciated!
Best,
Justin
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
JasonEgg
6 years ago
Unfortunately, the database is not set up to make this type of query easy. As far as I can tell, the only place a status change is recorded is in the table "HD_TICKET_CHANGE" in a field called "DESCRIPTION" which contains information about all changes made before a person clicks "Save" or "Apply Changes". For example, here is an entry from my own instance:
Added resolution text. Changed ticket Status from "New" to "Closed". Changed ticket Owner from "Unassigned" to "Jason E.".
Parsing that for all statuses would be... very difficult. It's certainly possible, but would be a monster of a query.
Comments:
-
Thanks Jason, I figured it would be a laborious task if the query was possible. - judgieduves22 6 years ago
