/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Helpdesk Ticket All history report

03/20/2018 1186 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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ