/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: JasonEgg 6 years ago
Red Belt
0
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
 
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