/build/static/layout/Breadcrumb_cap_w.png

Service Desk reports based on department

I created a custom field on my tickets that include department (department is selected from a dropdown predefined list).

We would like to create a report so we can see how many tickets were worked for a particular department in the last 7 days for example... I am not very good with SQL and after looking at this it seems it would require a custom SQL report to be created. Does anyone have any good pointers to accomplish something like this? We have multiple queues so I imagine that the queue id would like have an impact. 


0 Comments   [ + ] Show comments

Answers (1)

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

Top Answer

This query will show the number of tickets opened in the past seven days grouped by a custom field, in this case custom field 11:

SELECT CUSTOM_FIELD_VALUE10, COUNT(ID)
FROM ORG1.HD_TICKET
WHERE CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY CUSTOM_FIELD_VALUE10

You will notice that the column name for the custom field is one less than the field's name, that is because the database column names are zero based (custom field 1 is CUSTOM_FIELD_VALUE0, etc.) You will need to change that column name to match the custom field that holds your department. If you have the department field in multiple queues, then hopefully you are using the same custom field for all of them, otherwise things will be more complicated. 

Also, you said "tickets were worked", did you want a report showing tickets where "Work" was entered in the past seven days, or opened, closed, modified, etc.?


Comments:
  • Thank you so much for taking the time to respond to this inquiry. I'm still a little confused with this. We have multiple Queues and we are currently only wanting to run this report for our TELECOM queue which has an ID=12 ... Where do I incorporate that?
    To answer your question, We want to get a list of tickets opened in the past 7 days regardless of the current status.... Thanks - raul102801 5 years ago
    • To limit to use queue 12:
      SELECT CUSTOM_FIELD_VALUE10, COUNT(ID)
      FROM ORG1.HD_TICKET
      WHERE CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
      AND HD_QUEUE_ID = 12
      GROUP BY CUSTOM_FIELD_VALUE10 - chucksteel 5 years ago
      • Very helpful Sir ... Thank you so much. I think I'm getting closer to what I need. - raul102801 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