KACE: sending new owner an email when ticket is re-assigned
Hello, I have checked with KACE support, read these forums, and searched the Internet but cannot find an answer to my question.
I have a Change Management queue. Tickets are created and immediately assigned to the "CSA" user. The CSA receives an email notification due to the check mark in All Changes under Owner in the Email on Events section of the queue configurations. The CSA goes into KACE updates the ticket. I wrote a rule that automatically assigns the ticket to the "CCB" user once the CSA checks a box on the ticket form. I cannot get KACE to send an email notification to CCB. I need KACE to email a new Owner when an existing ticket is assigned to them. Like I said, I searched for an answer and KACE KB article 111222 does not help upon my reading. I don't believe Escalations are appropriate here. Any guidance is appreciated.
Thank you for your time and attention.
Answers (1)
The following query should work as a ticket rule. I haven't tested it though. It is similar to a rule I have in our setup. The email column is "OWNER_EMAIL" and should pick up the new owner. You can test with a valid change_id for your particular scenario.
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
GROUP BY OLIST.EMAIL
HAVING 1 = 1
Comments:
-
Thank you very much. How do I test with a "valid change ID?" Do I manually add that (if so, where do I get it) or is that a variable that KACE will enter? - sweetsteve 9 years ago
-
Check the query in a query browser like MySQL Workbench. Connection instructions are available at http://www.kace.com/support/resources/kb/solutiondetail?sol=114992. - grayematter 9 years ago
-
You can get a Change ID from the HD_TICKET_CHANGE table. Find an appropriate ticket id for your scenario and query that table for the change id. - grayematter 9 years ago
and HD_TICKET.HD_QUEUE_ID = 5
And this line removed (because it gave me an error):
GROUP BY OLIST.EMAIL
"Email each recipient in query results" is checked, the Subject and Message are filled out, and "Column containing email addresses" is OWNER_EMAIL.
Does that help? - ondrar 8 years ago