Report to show tickets created by a technician and immediately assigned to another technician
I am looking for a report that will show me when a technician creates a ticket (they are not the submitter) and instead of having the ticket user be unassigned and the Help Desk assign they immediately assign to another technician. I need to see who the submitter is, who the tech was that created the ticket and who the ticket was directly assigned to.
1 Comment
[ + ] Show comment
Answers (1)
Please log in to answer
Posted by:
chucksteel
10 years ago
This SQL statement should do the trick:
SELECT HD_TICKET.ID, OWNER.USER_NAME AS OWNERUSER, SUBMITTER.USER_NAME AS SUBMITTERUSER, TECHNICIAN.USER_NAME AS TECHNICIANUSER FROM ORG1.HD_TICKET JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID and C.DESCRIPTION = "Ticket Created" JOIN USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID JOIN USER TECHNICIAN on TECHNICIAN.ID = C.USER_ID WHERE TECHNICIAN.USER_NAME != SUBMITTER.USER_NAME and TECHNICIAN.USER_NAME != OWNER.USER_NAME
You have to pull information from the HD_TICKET_CHANGE table to get the user that entered the ticket and I do that by joining the change that created the ticket (Description = "Ticket Created". I'm pretty sure this is the information that you're looking to find.
If they make themselves the owner and then change it to another tech, that would be recorded.
Not sure if anyone else has any ideas but that you ask may not be doable as the KBOX isn't collecting the information you are asking for unless there is a change to the ticket. - nshah 10 years ago