KBOX report showing "Ticket Created" and by whom
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
Thanks
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
h2opolo25
9 years ago
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
Comments:
-
Submitter won't always be the same as creator. - chucksteel 9 years ago
-
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? - ColMstrd 9 years ago
Posted by:
chucksteel
9 years ago
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
