/build/static/layout/Breadcrumb_cap_w.png

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.



2 Comments   [ + ] Show 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. - chucksteel 5 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 5 years ago

Answers (2)

Answer Summary:
Posted by: chucksteel 5 years ago
Red Belt
2

Top Answer

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)


Comments:
  • This worked! Ill be able to get the information out of this report. Thank you! - ISEKOLD 5 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 7 months 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 7 months ago
      • Thank you chucksteel! That worked!! Appreciate it. - trankin 7 months ago
Posted by: Druis 5 years ago
Third Degree Green Belt
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

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

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