/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: chucksteel 7 years ago
Red Belt
0
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
Red Belt
0
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:
  • Thanks a lot for your help, I'll try this out and see how it's working.

    Thx! - dtisoft 7 years ago
  • 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

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