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