Custom Email to Submitter on Owner Change
I am trying to write a Service Desk rule to send a custom email when a ticket is New and the owner is changed from a value of 'DefaultTicketOwners' to an actual owner. We do not use the 'Unassigned' value and have a different rule to auto-assign any of those tickets to 'DefaultTicketOwners'. Here is what I have been able to put together so far:
select distinct HD_TICKET.ID,
HD_TICKET.OWNER_ID as OWNER_ID,
HD_TICKET.SUBMITTER_ID as SUBMITTER_ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
OWNER.USER_NAME as OWNER_NAME,
OWNER.FULL_NAME as OWNER_FULLNAME,
OWNER.EMAIL as OWNER_EMAIL,
UPDATER.USER_NAME as UPDATERNAME,
UPDATER.EMAIL as UPDATEREMAIL,
SUBMITTER.FULL_NAME as SUBMITTER_FULLNAME,
SUBMITTER.EMAIL as SUBMITTER_EMAIL
from (HD_TICKET)
left join HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID = <CHANGE_ID>
left join HD_TICKET_CHANGE_FIELD on HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID = HD_TICKET_CHANGE.ID
left join USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID
left join USER OLD_OWNER on OLD_OWNER.ID = HD_TICKET_CHANGE.OWNER_ID
left join USER UPDATER on UPDATER.ID = HD_TICKET_CHANGE.USER_ID
left join USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
where HD_TICKET_CHANGE.HD_TICKET_ID= HD_TICKET.ID
and OLD_OWNER.FULL_NAME like 'DefaultTicketOwner'
The error I get is [1054: Unknown column 'HD_TICKET_CHANGE.OWNER_ID' in 'on clause'] when the rule is fired. Is there something I am missing? I know that HD_TICKET_CHANGE.OWNER_ID is no longer there, but I'm unsure on where to look for ownership changed that isn't in the comment. Any help would be appreciated.
Answers (2)
You can access the change to the owner ID by adding the following join statement:
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'
You can now reference HD_TICKET_CHANGE_FIELD.BEFORE_VALUE for the before value and HD_TICKET_CHANGE_FIELD.AFTER_VALUE for the after value. I have a rule configured to inform a technician when a ticket is re-assigned to another technician using these fields.
I decided to take a different route.
select HD_TICKET.ID, HD_TICKET.ID as TICKNUM, HD_TICKET.TITLE, U1.USER_NAME as OWNER_NAME, U3.USER_NAME as LASTINPUTNAME, 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, STATE, U1.FULL_NAME as OWNER_FULLNAME, U1.EMAIL as OWNER_EMAIL, U2.USER_NAME as SUBMITTER_NAME, U2.FULL_NAME as SUBMITTER_FULLNAME, U2.EMAIL as SUBMITTER_EMAIL, U3.EMAIL as UPDATEREMAIL, UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP) 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> 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_CHANGE.USER_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 HD_TICKET_CHANGE.DESCRIPTION LIKE 'Changed ticket Owner from "DefaultTicketOwners" to%'
This way I can just look to see when the owner changes to something other than the default. What this doesn't do is if the ticket then get's reassigned. Maybe I'll create a different rule for that one.
Comments:
-
You can wildcard the first part of the description:
HD_TICKET_CHANGE.DESCRIPTION LIKE 'Changed ticket Owner from % to %' - chucksteel 11 years ago