/build/static/layout/Breadcrumb_cap_w.png

Ticket Rule- email on ticket moving to new queue

Hey all, just creating seperate queues for the first time, need some assistants with the coding. All we need is a simple rule to notify us when we have a ticket that changes queues.

 

NOTE: I found this (http://www.itninja.com/question/send-email-notification-when-ticket-is-transferred-to-queue) but even after some tinkering it didn't work.

Below is our code for a new ticket email that gets sent ont ticket save to notify us of new tickets. I'm hoping something can be modified from this (I know it's not pretty, but it gets the job done).

 

Thanks in advance:

 

SELECT
      HD_TICKET.ID, -- $id
      HD_TICKET.ID AS TICKNUM, -- $ticknum
      HD_TICKET.CUSTOM_FIELD_VALUE0 AS LOCATION,
      HD_TICKET.CUSTOM_FIELD_VALUE1 AS DEPARTMENT,
      HD_TICKET.TITLE, -- $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
      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://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
       ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
      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
      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
      SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
      SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
      SUBMITTER.EMAIL AS SUBMITTER_EMAIL,     -- $submitter_email
      P.NAME AS PRIORITY, -- $priority
      S.NAME AS STATUS,   -- $status
      I.NAME AS IMPACT,   -- $impact
      CAT.NAME AS CATEGORY, -- $category
      'ithelpdesk@ourcompany.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%'

    GROUP BY HD_TICKET.ID
    HAVING 1=1
 

Asked 11 years ago 3925 views
3 Comments   [ + ] Show comments
  • well, this rule will ONLY shoot off when a ticket is created.

    And that's really the only criteria.

    It should also be noted that the only way to test a ticket that is based off a HD_TICKET_CHANGE comment like that, is to make it happen in real time (running it in ticket rule will do nothing). - Wildwolfay 11 years ago
  • If I had seen this earlier, I would have pointed you to http://www.itninja.com/question/email-ticket-owners-label-on-new-unassigned-ticket - grayematter 11 years ago
  • pls check my statement if its correct.
    what im doing is, if CUSTOM_FIELD_VALUE8 like '%America%' then move the tickets to queue_ID =20.

    SELECT HD_TICKET.ID, HD_TICKET.TITLE,
    HD_TICKET.HD_CATEGORY_ID, HD_TICKET.HD_QUEUE_ID,
    HD_CATEGORY.ID, HD_CATEGORY.NAME
    FROM HD_TICKET
    JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
    WHERE HD_TICKET.CUSTOM_FIELD_VALUE8 like '%America%'
    AND HD_TICKET.HD_QUEUE_ID = 16
    ORDER BY HD_TICKET.ID




    UPDATE HD_TICKET
    SET HD_TICKET.HD_QUEUE_ID = 20
    WHERE HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
    AND HD_TICKET.ID = <TICKET_IDS>;


    05/11/2016 03:01:34> Starting: 05/11/2016 03:01:34 05/11/2016 03:01:34> Executing Select Query... 05/11/2016 03:01:34> selected 2 rows 05/11/2016 03:01:34> Executing Update Query... 05/11/2016 03:01:34> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '122' at line 4] in EXECUTE("UPDATE HD_TICKET SET HD_TICKET.HD_QUEUE_ID = 20 WHERE HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID AND HD_TICKET.ID = 122,122;") 05/11/2016 03:01:34> Ending: 05/11/2016 03:01:34 - rahimpal 8 years ago

Answers (1)

Answer Summary:
Posted by: Wildwolfay 11 years ago
Red Belt
1

In response to my own comment above, one of the things you can do is change 'TICKET CREATED%' to:

 

'Changed Ticket Queue from%'

 

That should give you any tickets that change queues, but remember that this rule will only run real-time on ticket save, and not in 'run now' status, so be advised.  (I would suggest using 2 test queues)

Comments:
  • If it doesn't work off the bat with your first testing, try putting the ticket rule in the RECEIVING queue and then in the SENDING QUEUE and see which one fires off the rule more solidly, as I've read of that being an issue / discussion. - Wildwolfay 11 years ago
    • Awesome! Thanks for the help. The key change was this:

      C.DESCRIPTION LIKE 'Changed Ticket Queue%'

      It sends an email no matter where it's coming from.

      THanks again! - Mark_B 11 years ago
 
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