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.

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

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. 

Answered 07/17/2012 by: chucksteel
Red Belt

Please log in to comment
0

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.

 

Answered 07/16/2012 by: billy.ellison
Orange Belt

  • You can wildcard the first part of the description:
    HD_TICKET_CHANGE.DESCRIPTION LIKE 'Changed ticket Owner from % to %'
Please log in to comment
Answer this question or Comment on this question for clarity