Email when ticket assigned to Owner - not working since upgrade to 6.3
We have a rule in place, that when a ticket is assigned from one owner to the next (i.e. transferred with in the team) that the new owner is notified via email that they have the ticket.
The rule is:
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://OURKBOX/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
GROUP BY OLIST.EMAIL
HAVING 1 = 1
I have put in bold Group By Olist.Email, as that appears to be where the issue is - guessing the syntax changed. Below is the log error:
02/27/2015 09:41:15> Starting: 02/27/2015 09:41:15 02/27/2015 09:41:15> Executing Select Query... 02/27/2015 09:41:15> mysql error: [1054: Unknown column 'OLIST.EMAIL' in 'group statement'] in EXECUTE("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 = 11422 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 GROUP BY OLIST.EMAIL HAVING 1 = 1 and (HD_TICKET.ID = 4851) ")
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
hutcha4113
9 years ago
Never mind. Noticed that we have an email option in the queue setup, that will look after what we need. Disabled the original rule.