We need to put together a report showing the Ticket Created date/time but also who created the ticket.  We need to determine who is handling the majority of ticket creations within our org.  So far, we've not been able to find a field that displays the information contained in the first line of the first comment in a new KBOX ticket.  Can anyone provide the name of the table/field we can call to retrieve this information?  We have all the other information we need in our custom SQL already, this is the last piece of data we need.  Any assistance is appreciated!

Thanks
0 Comments   [ + ] Show Comments

Comments

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
Please log in to comment

Answers

0
You should be able to get the ticket creator from HD_TICKET.SUBMITTER_ID as well as the date created from HD_TICKET.CREATED

If you want to get the name 

select USER.USER_NAME from HD_TICKET
left join USER on USER.ID = HD_TICKET.SUBMITTER_ID
blah blah blah...the rest of your query
Answered 02/18/2015 by: h2opolo25
Red Belt

  • Submitter won't always be the same as creator.
    • Exactly...we need to find out who created the ticket and who closed the ticket. They are not necessarily the owner or the submitter. Is there a table/column we can look at to retrieve this information? Or a combination of columns/tables to run a report against?
Please log in to comment
0
You need to join to the HD_TICKET_CHANGE table and find the first change on the ticket. From there you can see who made that change. This report also includes the submitter:

SELECT T.ID, T.TITLE, T.CREATED, CREATOR.FULL_NAME as "Creator", SUBMITTER.FULL_NAME as "Submitter"
FROM ORG1.HD_TICKET T
JOIN HD_TICKET_CHANGE TC on T.ID = TC.HD_TICKET_ID and TC.ID = (select MIN(ID) FROM HD_TICKET_CHANGE WHERE HD_TICKET_ID = T.ID)
JOIN USER CREATOR ON TC.USER_ID = CREATOR.ID
JOIN USER SUBMITTER on T.SUBMITTER_ID = SUBMITTER.ID

Answered 02/19/2015 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity