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.
Answers (1)
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 4 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 4 years ago-
Very helpful Sir ... Thank you so much. I think I'm getting closer to what I need. - raul102801 4 years ago
