/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


KBOX report showing "Ticket Created" and by whom

02/18/2015 2506 views
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.

All 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?
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

Don't be a Stranger!

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

Sign up! or login

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