/build/static/layout/Breadcrumb_cap_w.png

Is there a way to get notification every time "X" user submits a ticket into "X" Queue?

Got a strange request today from higher up. Evidently they would like to know every time a certain user submits a ticket into one of our help desk queues. Is there any clear way to make this happen?


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: ondrar 4 years ago
Black Belt
2

Top Answer

Sounds like a job for a ticket rule.  I have something like that, which could be modified for what you want:


    SELECT
      -- ticket fields
      HD_TICKET.ID, -- $id
      HD_TICKET.ID AS TICKET_NUMBER, -- $ticket_number
      HD_TICKET.TITLE AS TICKET_TITLE, -- $ticket_title
      DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
      DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
      CUSTOM_FIELD_VALUE0 AS TICKET_CUSTOM_1_VALUE, -- $ticket_custom_1_value 
      CUSTOM_FIELD_VALUE1 AS TICKET_CUSTOM_2_VALUE,-- $ticket_custom_2_value
      -- change fields
      C.COMMENT, -- $comment
      C.DESCRIPTION, -- $description
      GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
       H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://url/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
       ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
      -- about the updater
      UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
      UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
      UPDATER.EMAIL AS UPDATER_EMAIL,     -- $updater_email
      IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
      -- about the owner
      OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
      OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
      OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email
      IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
      -- about the submitter
      SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
      SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
      SUBMITTER.EMAIL AS SUBMITTER_EMAIL,     -- $submitter_email
      -- about priority
      P.NAME AS PRIORITY, -- $priority
      -- about status
      S.NAME AS STATUS,   -- $status
      -- about impact
      I.NAME AS IMPACT,   -- $impact
      -- about category
      CAT.NAME AS CATEGORY, -- $category
      -- example of static notification list
      'higher_up@contoso.com' AS NEWTICKETEMAIL -- $newticketemail
    FROM HD_TICKET
     /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
                            AND C.ID=<CHANGE_ID>
     /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
     /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
     /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
     /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
     /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
     /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
     /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
     /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
    WHERE
     C.DESCRIPTION LIKE 'TICKET CREATED%'
     AND SUBMITTER.USER_NAME LIKE 'username'
     AND HD_TICKET.HD_QUEUE_ID=# -- If you want to specify a queue (insert your queue number here)
      /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1


I think this is something I took from Quest's KB originally.  Anyway, I highlighted the important parts.  You can probably remove some of the other stuff if you don't need it.

So in the Ticket Rule, you can either check Email Results and put the higher up's email in that box and they'll get a raw email with any fields you're selecting, or check Email each recipient in query results and use the NEWTICKETEMAIL value in Column containing email addresses, then create a prettier email template.

The frequency would be On Ticket Save to notify them as soon as the ticket is created.

Note that I don't have any multi-queue ticket rules, so theoretically this will work, but you could try  AND HD_TICKET.HD_QUEUE_ID IN(10, 11, 12) for example, if you're trying to specify multiple queues.  Otherwise, you'd need to have the rule present in each queue you want reported on.

Hopefully that's clear and helps out.



Comments:
  • Hiya, could this code also be used to create emails on ALL New Tickets created for any queue and distribute to the queue owners? I 'm not great with SQL, so any help appreciated. Thanks - Frankii 4 years ago
    • Email notifications for new tickets to all queue owners is built into the Email on Events matrix. If you go to Service Desk › Configuration › Service Desk Queue Email Settings > (your queue), scroll down to Email on Events and check the boxes on the "New Ticket Via Email" and "New Ticket Via Portal" rows for the Queue Owners column. - ondrar 4 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