Custom report for all ticket touches
I'm trying to create a custom report that will tell me the number or times a User made any change to any ticket within a time frame. Essentially get work hours for an entire Queue based on ticket touches... However, I'm trying to do this by blending other posts, which has led to a great deal of failure on my part.
Anyone done something similar they might share?
Answers (2)
Top Answer
This should work for you:
SELECT USER.FULL_NAME, COUNT(DISTINCT(HD_TICKET_CHANGE.HD_TICKET_ID)) as '# Changed',
GROUP_CONCAT(DISTINCT(HD_TICKET_CHANGE.HD_TICKET_ID)) as'Tickets'
FROM USER
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.USER_ID = USER.ID
WHERE HD_TICKET_CHANGE.TIMESTAMP > DATE_SUB(NOW(), INTERVAL 1 MONTH)
and USER.USER_NAME = "username"
GROUP BY USER.FULL_NAME
This uses username for the query instead of user full name, just set it to the appropriate username. It also gives a comma separated list of the tickets that the user changed.
Comments:
-
This should do quite nicely. I greatly appreciate it, Sir. - IDKneThing 5 years ago
-
I have added
|| HD_TICKET_CHANGE.TIMESTAMP > DATE_SUB(NOW(), INTERVAL 1 MONTH)
and USER.USER_NAME = "2nd username"
Following the initial user to expand to multiple people. Seems to be working well. Thanks again. - IDKneThing 5 years ago
If you are reporting on multiple users (which is what your comment suggests), then this query reports on users in a given label (User Services, in this example):
SELECT USER.FULL_NAME, COUNT(DISTINCT(HD_TICKET_CHANGE.HD_TICKET_ID)) as "Tickets Changed",
GROUP_CONCAT(DISTINCT(HD_TICKET_CHANGE.HD_TICKET_ID))
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
Comments:
-
That is much more efficient. Thanks again hahah - IDKneThing 5 years ago
(SELECT COUNT(ID) FROM HD_TICKET where
HD_CATEGORY_ID = HD_CATEGORY.ID
and HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 1 MONTH)GROUP BY HD_CATEGORY.ID) as Tickets,
USER.FULL_NAME as "Default Owner"
FROM HD_CATEGORY
LEFT JOIN HD_QUEUE on HD_QUEUE.ID = HD_CATEGORY.HD_QUEUE_ID
LEFT JOIN USER on USER.ID = HD_CATEGORY.DEFAULT_OWNER_ID
GROUP BY HD_CATEGORY.ID
ORDER BY HD_QUEUE.NAME, HD_CATEGORY.NAME
This is somewhat what I want, and mostly what I was basing my guesses off of, but I don't actually care for Category info. All attempts at editing this to show Count if tickets edited by a user in a particular Queue fail to even Save. - IDKneThing 5 years ago