/bundles/itninjaweb/img/Breadcrumb_cap_w.png

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.


Answer Summary:
2 Comments   [ + ] Show comments

Comments

  • 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.
  • 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.

Answer Chosen by the Author

1
Here is a straight dump of all tickets created in the past year for all queues that includes columns for Ticket ID, Title, DateTime Created, Creator and 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
WHERE T.CREATED > DATE_SUB(NOW(), INTERVAL 1 YEAR)

Answered 06/11/2018 by: chucksteel
Red Belt

  • This worked! Ill be able to get the information out of this report. Thank you!

All Answers

0
Hi,

try this:-

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.SUBMITTER_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

The Created_By field is usually populated from the User table
Answered 06/07/2018 by: Druis
Second Degree Green Belt

  • This returned "No results found."?
    • 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?
      • This returned the same thing. Unfortunately I don't know SQL, so I don't even know where to begin.

Share