Adding previous owner to CC list
We set up a service desk to act like a two teir structre. I repourpsoed the priorty feild to have 2 optins "normal" and "Escalte". Upon selecting escalte a user named "teir two tech" is automatically assigned to the tickets. If the tier two tech changes the priorty back to normal it will reassign the ticket back to the orignal user. We were able to get this functionality working correctly.
The next thing we want to do is add the pervious owner to the CC list so that the orginal tech is "in the loop" for everything that is added to the ticket. But for some reason when I try to add a user to the CC list in the update query it wont accept varibles like $old_owner it gives me a couple of errors. Has anyone needed to implement something simmilar?
Example code:
update HD_TICKET, USER as T5
set HD_TICKET.CC_LIST = $old_owner,
HD_TICKET.OWNER_ID = T5.ID
where T5.USER_NAME = 'UWtier2' and
(HD_TICKET.ID in (<TICKET_IDS>))
Errors:
09/06/2013 11:24:12> Executing Update Query...
09/06/2013 11:24:12> mysql error: [1054: Unknown column '$old_owner' in 'field list'] in EXECUTE("update HD_TICKET, USER as T5
set HD_TICKET.CC_LIST = $old_owner,
HD_TICKET.OWNER_ID = T5.ID
where T5.USER_NAME = 'UWtier2' and
(HD_TICKET.ID in (3778))")
Answers (1)
Create a custom field called 'OLD_OWNER'
Create a custom ticket rule that updates OLD_OWNER = OWNER_ID where OLD_OWNER is empty.
Create a second custom ticket rule that updates CC_LIST = OLD_OWNERS email from the USER table (this will require a join of USER on USER.ID = HD_TICKET.OLD_OWNER to get the old owners email address. The WHERE portion of the update should be WHERE HD_TICKET.OWNER_ID != HD_TICKET.CUSTOM_FIELD_VALUEXX (custom field value being the old owner custom field). This will trigger the CC to be updated when the owner changes. To add to the CC_List rather than replace it would be SET CC_LIST = CC_LIST + old owner email.......
This should point you in the right direction, let me know if you need further help with the code.
Comments:
-
If I'm reading this right, everytime the owner ID changes, the OLD_OWNER field will change as well.
You need to make sure you incorporate the first rule with the rules that govern when a ticket is CREATED.
Use this kace article as guidance to setup the first rule he described:
http://www.kace.com/support/resources/kb/solutiondetail?sol=SOL111222
That way the OLD_OWNER updates on creation only.
I think I was a bit over-redundant? - Wildwolfay 11 years ago -
I want to start off by thanking you guys for your time so far. I tried what gregekeys suggested. Here is my SQL and Errors below. Might one of you be so kind as to point out my error?
Errors:
09/09/2013 16:04:09> Starting: 09/09/2013 16:04:09
09/09/2013 16:04:09> Executing Select Query...
09/09/2013 16:04:09> mysql error: [1054: Unknown column 'HD_TICKET.OLD_OWNER' in 'on
and HD_STATUS.ID = HD_STATUS_ID
and HD_IMPACT.ID = HD_IMPACT_ID
and HD_CATEGORY.ID = HD_CATEGORY_ID
and (( HD_PRIORITY.NAME = 'Escalte') and HD_TICKET.HD_QUEUE_ID = 5 )
and (HD_TICKET.ID = 4041) ")
Select Query:
select HD_TICKET.*,
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
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
LEFT JOIN USER on USER.ID = HD_TICKET.OLD_OWNER
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 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 (( HD_PRIORITY.NAME = 'Escalte') and HD_TICKET.HD_QUEUE_ID = 5 )
Update Query:
update HD_TICKET
set HD_TICKET.CC_LIST = HD_TICKET.OLD_OWNER
where
(HD_TICKET.ID in (<TICKET_IDS>)) - Atom 11 years ago