Any way to run a KACE report to view last comment date in the HelpDesk?
06/10/2015 2458 views
I am needing to monitor the activity on the queue. Since I do not see an option to display the last comment date, has anyone created a report to generate it?
Answer Chosen by the Author
Please log in to answer
Here's a version that specifies a queue and only reports on open tickets. I'm also using a join statement that gets the most recent change with a comment.
SELECT T.ID, T.TITLE, C.COMMENT, C.TIMESTAMP, U.USER_NAME
FROM ORG1.HD_TICKET T
JOIN HD_TICKET_CHANGE C on C.HD_TICKET_ID = T.ID and C.ID = (select MAX(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = T.ID and HD_TICKET_CHANGE.COMMENT != '')
JOIN USER U on U.ID = C.USER_ID
WHERE T.HD_QUEUE_ID = 2
and T.TIME_CLOSED = "0000-00-00 00:00:00"
Be sure to change the T.HD_QUEUE_ID = 2 line to match the queue ID for your queue.
Answered 06/11/2015 by: chucksteel