/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


K1000 Reporting - Need a report to track Time Created, when ticket changed queue, and when the ticket status changed

01/27/2016 1588 views
I've been struggling with this and trying to modify other reports and sql statements I found in other posts, however, nothing is working. This is what I am looking for:

1. Ticket Number
2. Description/Problem (title)
3. Date/Time ticket was created
4. Date/Time ticket queue changed and by whom
5. Date/Time ticket status changed
6. All status'

Any assistance is appreciated!
Answer Summary:
2 Comments   [ + ] Show comments

Comments

  • What you are asking for is quite a complex query which is probably why you have not found an answer from any other posts. If I take your request at face value, to form the report we need to pull data from
    HD_TICKET,
    USER,
    HD_TICKET_CHANGE
    and HD_STATUS tables
    but before we can suggest any code I would like to understand why you need the report, why the need to record the change of queue, change of status and all other status (?) on a single report?

    Surely a single detailed ticket report showing the full history will provide you with all the detail you require?
  • A second level support group has their own queue. They would like to know how long it is taking the Help Desk to move tickets into their queue. We can live without status change as a criteria but would need current status as a column on the report. So I need to know when the ticket was created, when it was moved into queue 12 and by whom. Everything listed above except #5.

    Thank you

Answer Chosen by the Author

0
This query will show all tickets that have been transferred from one queue to another in the past month:
SELECT HD_TICKET.ID, HD_TICKET.TITLE, HD_TICKET.CREATED as "Created",
HD_TICKET_CHANGE.TIMESTAMP as "Transferred",
CHANGER.FULL_NAME as "Changer",
HD_STATUS.NAME as "Current Status"
FROM ORG1.HD_TICKET
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
JOIN HD_STATUS on HD_TICKET.HD_STATUS_ID = HD_STATUS.ID
JOIN USER CHANGER on CHANGER.ID = HD_TICKET_CHANGE.USER_ID
WHERE HD_TICKET_CHANGE.DESCRIPTION like "%Changed ticket Queue%"
and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 1 MONTH)

To show tickets being moved from a specific queue into another change the "%Changed ticket Queue%" to "%Changed ticket Queue from Tier one queue to Tier two queue%" as appropriate. Queue changes are not tracked in HD_TICKET_CHANGE_FIELD so you need to look for the change in HD_TICKET_CHANGE descriptions.


Answered 01/28/2016 by: chucksteel
Red Belt

  • THANK YOU very much for your time!

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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