I would like to email a person when a ticket is assigned to them.  I have tried the built in 'Owner Change' option and that seems to just email the original owner that the ticket was updated.  I need a specific rule to tell the new owner.  Here is my rule that I cannot seem to get working.  I have tested with MySQL workbench and do not get any errors.  Any suggestions?

SELECT
    HD_TICKET.ID,
    HD_TICKET.ID AS TICKNUM,
    HD_TICKET.TITLE,
    DATE_FORMAT(HD_TICKET.CREATED,
            '%b %d %Y %I:%i:%s %p') AS CREATED,
    DATE_FORMAT(HD_TICKET.MODIFIED,
            '%b %d %Y %I:%i:%s %p') AS MODIFIED,
    C.COMMENT,
    C.DESCRIPTION,
    GROUP_CONCAT(CONCAT('----- Change by ',
                UPDATER.EMAIL,
                ' at ',
                H.TIMESTAMP,
                ' -----
                ',
                H.DESCRIPTION,
                '
                ',
                H.COMMENT,
                '
               
                Please see your ticket at http://kbox/userui/ticket.php?ID=',
                H.HD_TICKET_ID,
                '
                ')
        ORDER BY H.ID DESC
        SEPARATOR '
        ') HISTORY,
    UPDATER.USER_NAME AS UPDATER_UNAME,
    UPDATER.FULL_NAME AS UPDATER_FNAME,
    UPDATER.EMAIL AS UPDATER_EMAIL,
    IF(UPDATER.FULL_NAME = '',
        UPDATER.USER_NAME,
        UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL,
    OWNER.USER_NAME AS OWNER_UNAME,
    OWNER.FULL_NAME AS OWNER_FNAME,
    OWNER.EMAIL AS OWNER_EMAIL,
    IFNULL(OWNER.USER_NAME, 'Unassigned') OWNER_USER,
    SUBMITTER.USER_NAME AS SUBMITTER_UNAME,
    SUBMITTER.FULL_NAME AS SUBMITTER_FNAME,
    SUBMITTER.EMAIL AS SUBMITTER_EMAIL,
    P.NAME AS PRIORITY,
    S.NAME AS STATUS,
    I.NAME AS IMPACT,
    CAT.NAME AS CATEGORY,
    HD_QUEUE.NAME AS QUEUENAME
FROM
    HD_TICKET
        JOIN
    HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
        AND C.ID = <CHANGE_ID>
        JOIN
    HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
        JOIN
    HD_PRIORITY P ON P.ID = HD_PRIORITY_ID
        JOIN
    HD_STATUS S ON S.ID = HD_STATUS_ID
        JOIN
    HD_IMPACT I ON I.ID = HD_IMPACT_ID
        JOIN
    HD_CATEGORY CAT ON CAT.ID = HD_CATEGORY_ID
        LEFT JOIN
    USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
        LEFT JOIN
    USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
        LEFT JOIN
    USER UPDATER ON UPDATER.ID = C.USER_ID
        JOIN
    HD_QUEUE ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID
WHERE
    (C.DESCRIPTION LIKE '%Changed ticket Owner%')
        and OWNER.USER_NAME is not null
HAVING 1 = 1



---------------------------------------

Subject: [TICK:$ticknum] The following ticket has been assigned to you: $title

Column containing email addresses: $owner_email

Message:

The ticket, $title has been assigned to you.

For complete details, see:
http://k1000/adminui/ticket?ID=$ticknum

Thank you



3 Comments   [ + ] Show Comments

Comments

  • I noticed that I had $owner_email for the Column containing email addresses. I fixed that to owner_email without the $. I thought that would fix my issue but the new owner is still not getting an email when the owner is changed. Any ideas would be appreciated.
    • Is the ticket rule set to run on Ticket Save? Also what happens when you run the rule manually?
  • It is set on Ticket Save. Nothing happens when I run it manually.
  • We are looking for the same. We also need to email the NEW owner when an existing ticket has been assigned to them.
Please log in to comment

Community Chosen Answer

1
You appear to have selected the data correctly in your select statement

OWNER.EMAIL AS OWNER_EMAIL,

Try entering the field in your ticket rule just as capitals, so in the field 

Column containing email addresses: $owner_email

set it as

Column containing email addresses: OWNER_EMAIL
Answered 01/27/2016 by: Hobbsy
Red Belt

  • I made this change to all caps OWNER_EMAIL yet an email is still not being sent when the owner is changed.
Please log in to comment

Answers

0
The way I do this is by sending an email on ticket save.


        SELECT
          -- ticket fields
          HD_TICKET.ID, -- $id
          HD_TICKET.ID AS TICKNUM, -- $ticknum
          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
        
          -- 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://support.mycompany.com/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
        
          -- other fields
          -- -- example of static distribution list
          'supportreq@mycompany.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%'
        
          /* this is necessary when using group by functions */
        GROUP BY HD_TICKET.ID
        HAVING 1=1

Answered 01/28/2016 by: ohiosoundguy
Eighth Degree Black Belt

  • I have this SQL for other rules when the ticket is originally created. What I need now is it to email a new owner once the ticket is already open and it is reassigned to someone else.
Please log in to comment
Answer this question or Comment on this question for clarity

Share