/build/static/layout/Breadcrumb_cap_w.png
01/24/2017 1113 views
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

Comments


All Answers

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. 
Answered 01/25/2017 by: chucksteel
Red Belt

  • 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).
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.
Answered 01/25/2017 by: chucksteel
Red Belt

  • Thanks a lot for your help, I'll try this out and see how it's working.

    Thx!
  • 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!
    • 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).
      • 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,
      • Yes, add this so to the list of selected columns:
        , TIMESTAMPDIFF(DAY, CREATED, NOW()) as "Days Open"

        So that would go after "Created"