K1000 Custom Report for Tickets Transferred
Hello All !
Can anyone help me with a report that will show how many Tickets are initially transferred from our Help Desk Queue to another Queue?
Here is the scenario:
ALL Customer Help Tickets come to our Queue called Help Desk.
We then either handle it (close it) or (Choose Action) and Move it to Another Queue.
I would like this report based on the person who Moves it to Another Queue.
Reason: We only get "credit" in our evaluations on how many Tickets we have Closed. But we move many, many Tickets to other Queues and don't receive credit for them. As you all know, it takes time to read/evaluate all of those Tickets (Triage them).
Thank you all in advance!
1 Comment
[ + ] Show comment
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
6 years ago
Top Answer
Here is my query for all tickets transferred 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",
HD_TICKET_CHANGE.DESCRIPTION
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_CHANGE.TIMESTAMP > DATE_SUB(NOW(), INTERVAL 1 MONTH)
For a count of tickets transferred from a specific queue to other queues, try this:
SELECT CHANGER.FULL_NAME as "Changer", COUNT(HD_TICKET.ID) as "Tickets Transferred"
FROM ORG1.HD_TICKET
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
JOIN USER CHANGER on CHANGER.ID = HD_TICKET_CHANGE.USER_ID
WHERE HD_TICKET_CHANGE.DESCRIPTION like "%Changed ticket Queue from Help Desk%"
and HD_TICKET_CHANGE.TIMESTAMP > DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY CHANGER
Here is another version that gives the count of ticket changes in the past month for users in a label:
SELECT USER.FULL_NAME, COUNT(DISTINCT(HD_TICKET_CHANGE.HD_TICKET_ID)) as "Tickets Changed"
FROM USER
JOIN USER_LABEL_JT on USER_LABEL_JT.USER_ID = USER.ID
JOIN LABEL on LABEL.ID = USER_LABEL_JT.LABEL_ID
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.USER_ID = USER.ID
WHERE LABEL.NAME = "User Services"
and HD_TICKET_CHANGE.TIMESTAMP > DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY USER.FULL_NAME
ORDER BY USER.FULL_NAME
The advantage to this report is that it will show the number of the tickets that a technician has interacted with in the past month. Not every interaction is as valuable as others, but it should give a more accurate view of work performed on tickets. Keep in mind that changes such as deleting a computer from the inventory will update tickets where that machine was referenced (if you use that field). So in the months where I clean out duplicate computers my ticket could would be inflated because of that.
Comments:
-
chucksteel, is there a way to specify only one Changer and choose an actual Date Range? - Karineh13 6 years ago
-
Sure.
SELECT USER.FULL_NAME, COUNT(DISTINCT(HD_TICKET_CHANGE.HD_TICKET_ID)) as "Tickets Changed"
FROM USER
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.USER_ID = USER.ID
WHERE USER.USER_NAME = "youruser"
and DATE(HD_TICKET_CHANGE.TIMESTAMP) BETWEEN "2018-01-01" AND "2018-04-30"
GROUP BY USER.FULL_NAME
Change the dates to suit your needs. I'm not at work, so I didn't test this, but I'm pretty sure it will work.
Edit: It's USER.USER_NAME, fixed. - chucksteel 6 years ago
SELECT COUNT(CH.HD_TICKET_ID) AS No_Of_Tickets_Transferred
FROM HD_TICKET_CHANGE CH
WHERE CH.DESCRIPTION LIKE 'Changed ticket Queue%' - Druis 6 years ago