Any way to run a KACE report to view last comment date in the HelpDesk?
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?
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
8 years ago
Top 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.
Posted by:
aragorn.2003
8 years ago
Did you mean something like this?
select q.NAME, t.TITLE, t.SUMMARY, t.CREATED, tc.TIMESTAMP, u.FULL_NAME, tc.COMMENT
from HD_TICKET as t, HD_TICKET_CHANGE as tc, HD_QUEUE as q, USER as u
where t.ID = tc.HD_TICKET_ID
and q.ID = t.HD_QUEUE_ID
and u.ID = tc.USER_ID
and (tc.COMMENT is not null and tc.COMMENT != '')
order by tc.TIMESTAMP desc
