Can KACE Service Desk kick out email to owner each time owner is changed?
It should be simple and should be a canned rule inside of KACE. I want one rule to notify the owner that a ticket has been assigned to them. I don't want this just to trigger on ticket creation (which I have found), I want an email to the owner each time the owner is changed.
To reiterate, each time a new owner is selected and the ticket is saved, I want that particular owner to get an email notifying them that there is a ticket assigned to them. Why is this sophisticated software not able to do this simple task out of the box?
2 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
chucksteel
7 years ago
The system rule for email owner on ticket change should accomplish this. The new owner should be notified that the ticket was changed when it was saved. It won't be a specific, this ticket has been assigned to you message, but it should notify them.
Comments:
-
The thing is, I want it to be specific. I want my subject line to say: "Ticket xxx has just been assigned to you". Then as added bonus, I would love if the body could read: "Ticket xxx has just been assigned to you by [USERNAME]"
- Mo - mmarchese@cookcountytreasurer.com 7 years ago
Posted by:
chucksteel
7 years ago
This select statement will identify ticket changes and includes the necessary fields that you need. I use it to notify the previous owner that a ticket has been removed from their assigned tasks, but it will work for your situation.
select HD_TICKET.*, 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,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
STATE,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME,
U4.EMAIL as PREVIOUS_OWNER_EMAIL,
U5.EMAIL as NEW_OWNER_EMAIL, U6.EMAIL as CHANGER_EMAIL,U6.FULL_NAME as CHANGER_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID=<CHANGE_ID>
JOIN HD_TICKET_CHANGE_FIELD ON HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID=<CHANGE_ID>
and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED='OWNER_ID'
LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
LEFT JOIN USER U4 on U4.ID = HD_TICKET_CHANGE_FIELD.BEFORE_VALUE
LEFT JOIN USER U5 on U5.ID = HD_TICKET_CHANGE_FIELD.AFTER_VALUELEFT JOIN USER U6 on U6.ID = HD_TICKET_CHANGE.USER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID
and HD_STATUS.ID = HD_STATUS_ID
and HD_IMPACT.ID = HD_IMPACT_ID
and HD_CATEGORY.ID = HD_CATEGORY_ID
and (( (1 in (select 1 from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)) ))
You would want to specify NEW_OWNER_EMAIL for the column containing email address in your rule. For the message $changer_name will contain the name of the person that made the change.
Comments:
-
Chuck, this is not working. When I click "View KACE Ticket Search Results" i see syntax errors. I do not know if these syntax errors are the cause. I have another rule running, which does work, that also receives syntax errors. It's confusing.
Here is the last run log: 09/08/2017 12:00:20> Starting: 09/08/2017 12:00:20 09/08/2017 12:00:20> Executing Select Query... 09/08/2017 12:00:20> - mmarchese@cookcountytreasurer.com 7 years ago-
You can't use the ticket search results as the query is designed to be run on ticket save and uses the <CHANGE_ID> variable. - chucksteel 7 years ago
-
How can I test/troubleshoot this? - mmarchese@cookcountytreasurer.com 7 years ago
-
I have a queue that I use for creating new rules and perform my testing there. For trouble shooting you can turn on the Email results option in the rule and it will send you a table of the results when the rule fires. - chucksteel 7 years ago
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 OWNER.EMAIL
HAVING 1 = 1
Enable Email each recipient in query results
Subject: Whatever
Column containing email addresses: OWNER_EMAIL
Message: whatever
On Ticket Save - Druis 7 years ago
(C.DESCRIPTION LIKE '%Changed ticket Owner%') - mmarchese@cookcountytreasurer.com 7 years ago
Here is the last run log: 09/08/2017 12:03:57> Starting: 09/08/2017 12:03:57 09/08/2017 12:03:57> Executing Select Query... 09/08/2017 12:03:57> selected 0 rows - mmarchese@cookcountytreasurer.com 7 years ago