/build/static/layout/Breadcrumb_cap_w.png
03/20/2018 572 views
I cannot seem to find a way to get a report that shows all the ticket history for a queue.

I have gone through the reporting wizard but I don't see a field that marks ticket history.  I want something that shows me everything i would see if I looked at the History tab of a given ticket.  

I feel like I am missing something super obvious since the ticket itself shows the history.
2 Comments   [ + ] Show comments

Comments

  • Adding the comment field in the Wizard should give you the history for each ticket
  • I have tried that but all I get is the comments, I don't get who it was assigned to or any changes made to the ticket like I see on the History tab.

All Answers

0
This is what I use:
SELECT T.ID, T.TITLE, USER.USER_NAME, C.ID, C.TIMESTAMP, C.COMMENT, C.DESCRIPTION, CF.FIELD_CHANGED, CF.AFTER_VALUE
FROM ORG1.HD_TICKET T
JOIN HD_TICKET_CHANGE C on C.HD_TICKET_ID = T.ID
JOIN HD_TICKET_CHANGE_FIELD CF on CF.HD_TICKET_CHANGE_ID = C.ID
JOIN USER on USER.ID = C.USER_ID
WHERE T.ID = "62577"
It isn't pretty, but it works.

Answered 03/22/2018 by: chucksteel
Red Belt

  • You are right about not pretty. I wouldn't be able to use this as a report and have anyone but me understand it. Too much information in it this way. Thanks though
  • This is what I am currently using. I just would like to see a report that takes what is on the History Tab of a ticket and put it into the report. It is tidier and easier to figure out


    SELECT HD_TICKET.TITLE as 'Task Number', S.FULL_NAME AS 'Position Title', HD_TICKET_CHANGE.TIMESTAMP, HD_TICKET_CHANGE.COMMENT as 'Action / Decision / Enquiry', HD_TICKET_CHANGE.DESCRIPTION as 'Changes Made'
    FROM HD_TICKET
    LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
    LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
    LEFT JOIN HD_WORK ON (HD_WORK.HD_TICKET_ID = HD_TICKET.ID)
    WHERE ((HD_TICKET.HD_QUEUE_ID = 6) AND (HD_TICKET.TITLE like '%123%')) -- AND HD_TICKET_CHANGE.COMMENT != ''
    ORDER BY S.FULL_NAME, TIMESTAMP DESC