Report for tickets "Created by"
I am looking for a way to be able to tell how many tickets a helpdesk user has created in the last year. I can report on closed ticket, but I am looking for the number that they created. We have multiple queues that tickets could have been created under, so Id like it to look at all of the queues. The timeframe I am looking for is in the past 365 days.
I know I talked to a report writer in our org a while ago and he was able to see that on the back end "Created by" was just something tied in with a standard ticket update. Like there isn't a field called "CREATED_BY" or anything like that, it just shows up as a ticket update with a flag set for "Created by". I hope that makes sense.
I am ultimately looking for the ratio between tickets created and number of calls taken.
-
Do you want to know who created the ticket or who is the ticket submitter? Unless all of your tickets are entered by users via self service those are two different things. - chucksteel 6 years ago
-
I would like to know who entered the ticket. If possible, a report of all the tickets with one of the columns being who created the ticket would be ideal. As long as the date the ticket was entered is there, I can manually filter out the data needed. I wont be doing this often, so I don't mind putting in manual effort. - ISEKOLD 6 years ago
Answers (2)
Top Answer
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
WHERE T.CREATED > DATE_SUB(NOW(), INTERVAL 1 YEAR)
Comments:
-
This worked! Ill be able to get the information out of this report. Thank you! - ISEKOLD 6 years ago
-
I know this is old, but hoping you see this chucksteel...is there a way to do this same report, but with archived tickets?
I tried it myself be changing the table to HD_ARCHIVE_TICKET, but that errors out.
Thanks in advance! - trankin 1 year ago-
You need to also change the HD_TICKET_CHANGE to HD_ARCHIVE_TICKET_CHANGE:
SELECT T.ID, T.TITLE, T.CREATED,
CREATOR.FULL_NAME as "Creator",
SUBMITTER.FULL_NAME as "Submitter"
FROM ORG1.HD_ARCHIVE_TICKET T
JOIN HD_ARCHIVE_TICKET_CHANGE TC on T.ID = TC.HD_TICKET_ID and TC.ID = (select MIN(ID) FROM HD_ARCHIVE_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
WHERE T.CREATED > DATE_SUB(NOW(), INTERVAL 1 YEAR) - chucksteel 1 year ago-
Thank you chucksteel! That worked!! Appreciate it. - trankin 1 year ago
Comments:
-
This returned "No results found."? - ISEKOLD 6 years ago
-
SELECT O.FULL_NAME as Created_By,
count(HD_TICKET.ID) AS Count_of_Tickets
FROM HD_TICKET
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
WHERE (YEAR(HD_TICKET.CREATED) = YEAR(NOW()))
AND (DATE(HD_TICKET.CREATED) > '2017-06-07 00:00:00')
AND (HD_STATUS.NAME = 'Closed')
GROUP BY Created_By
Here is a slightly different query. This query looks at who owns the ticket. Does this give you anything? - Druis 6 years ago-
This returned the same thing. Unfortunately I don't know SQL, so I don't even know where to begin. - ISEKOLD 6 years ago
