How do I email the new owner when a ticket is re-assigned to them?
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
-
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. - totero21 8 years ago
-
Is the ticket rule set to run on Ticket Save? Also what happens when you run the rule manually? - Hobbsy 8 years ago
-
It is set on Ticket Save. Nothing happens when I run it manually. - totero21 8 years ago
-
We are looking for the same. We also need to email the NEW owner when an existing ticket has been assigned to them. - kimimtt 8 years ago
Answers (2)
Comments:
-
I made this change to all caps OWNER_EMAIL yet an email is still not being sent when the owner is changed. - totero21 8 years ago
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
Comments:
-
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. - totero21 8 years ago
