Unassigned tickets daily report
Hi, currently my service desk is divided with different queues (one for each office location). Is it possible to create a rules that will send an email every morning and/or at night to each IT owners of each queues containing all the unassigned tickets of his respective queue? Doesn't need to be in details, but something like the ticket #, title, priority and creation date?
Thanks in advance,
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
chucksteel
7 years ago
Yes, you can do this with a scheduled report. You should be able to create the report using the wizard. Once that is done schedule it to be sent every day to the appropriate person.
Comments:
-
Thx Chucksteel for the quick reply, but with a report, the "data" will appear in attachment and not in the email body. I'm looking for something that will send an email with the list of the unassigned tickets directly in the email (and not in attachment). - dtisoft 7 years ago
Posted by:
chucksteel
7 years ago
This query will find unassigned tickets in queue ID 2:
SELECT T.ID, T.TITLE, P.NAME as "Priority", DATE(T.CREATED) as "Created"
FROM HD_TICKET T
JOIN HD_PRIORITY P on P.ID = T.HD_PRIORITY_ID
WHERE OWNER_ID = 0
AND T.HD_QUEUE_ID = 2
In the queue configuration create a new SQL rule. Enter that statement as the select query.
Check the box for Email Results
Enter the email address of the recipient
Uncheck the Run Update query box
Select the desired schedule options
Save the rule
This will email output like this:
Email Unassigned tickets | ||||
New Rule | ||||
# | Id | Title | Priority | Created |
1 | 89863 | RE: Gateway/Dickinson Today access ~ Reply SMG 6 | Normal | 2017-01-25 |
2 | 50 | Test Ticket for Permissions | Request | 2011-07-11 |
3 | 53 | Ticket with test as submitter | Request | 2011-07-12 |
4 | 72 | Testing asset type | Request | 2011-07-27 |
5 | 123 | test from minda | Normal | 2011-08-09 |
6 | 124 | Please order me a Cray XK6 | Normal | 2011-08-09 |
Note that the disadvantage to using a rule like this is that the email will be sent every time the rule runs, regardless of whether or not there are unassigned tickets. With a scheduled report there is an option to only send if there are results.
Comments:
-
-
For some reason, in the past some tickets were closed without having an "owner" so they will show up in the "report"... Is there a way to removed these one? In other word only show unassigned tickets that have "new" or "opened" status?
Thx! - dtisoft 7 years ago-
Yes, by joining to the status table we can look for specific states. This will only get tickets that are not in a closed state:
SELECT T.ID, T.TITLE, P.NAME as "Priority", DATE(T.CREATED) as "Created"
FROM HD_TICKET T
JOIN HD_PRIORITY P on P.ID = T.HD_PRIORITY_ID
JOIN HD_STATUS S on S.ID = T.HD_STATUS_ID
WHERE OWNER_ID = 0
AND T.HD_QUEUE_ID = 2
and S.STATE != 'closed'
(FYI, New is generally a "stalled" state which is why I use state is not closed). - chucksteel 7 years ago-
Is it possible to add a column with the "age" of the ticket ... so the number of days it's open since the "creation date"?
Thanks in advance, - dtisoft 7 years ago -
Yes, add this so to the list of selected columns:
, TIMESTAMPDIFF(DAY, CREATED, NOW()) as "Days Open"
So that would go after "Created" - chucksteel 7 years ago
