Helpdesk Ticket All history report
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
-
Adding the comment field in the Wizard should give you the history for each ticket - Druis 6 years ago
-
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. - ellisha 6 years ago
Answers (1)
Please log in to answer
Posted by:
chucksteel
6 years ago
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.
Comments:
-
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 - ellisha 6 years ago
-
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 - ellisha 6 years ago
