Creating Service Desk Reports: Last X days by Location, Top Problem Users
Howdy all,
As I'm getting used to using the service desk, I'm also trying to create the reports my upper management will want. I can already do reports for Open tickets by Owner, Closed tickets last 7 days by Owner, ect.
What I'm trying to figure out is how to show:
1: How many tickets opened the last 7 days by location
2: How many tickets opened the last 7 days by Affected User
What I would really like if possible is who are the top 5 problem usere over the last 7 days. Meaning what are the top 5 users that had the most tickets opened for them over the last 7 days.
Any help would be most appreciated.
Thanks
-
How are you recording location? Is it directly in the ticket, or the location of the device or from the user record? - Hobbsy 4 years ago
-
There is a location drop down menu in the template. - wakeside917 4 years ago
-
Ok, so a custom field drop down in the ticket, which field number is it set as? - Hobbsy 4 years ago
-
From looking at configuration the field name is CUSTOM_1 and the label is Location - wakeside917 4 years ago
Answers (1)
Top Answer
Try this one for your location report, break the report on your location field
SELECT
HD_TICKET.CUSTOM_FIELD_VALUE0,
HD_TICKET.ID
FROM HD_TICKET
WHERE (HD_TICKET.HD_QUEUE_ID = X) AND
(((TIMESTAMP(HD_TICKET.CREATED) <= NOW() AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(),INTERVAL 7 DAY))))
ORDER BY CUSTOM_FIELD_VALUE0
