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 Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

1
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
Red Belt

Please log in to comment

Answers

0
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
Answered 06/11/2015 by: aragorn.2003
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share