ticket rule help
Hello,
Here is the situation. I have an existing rule that works. It only works when a new ticket is created. What happens is we have a single select custom field in our tickets called 'Service Area'. There are 9 values to choose from. The rule looks at what value is chosen and then emails based on that choice the appropriate distribution email list.
Here is the situation. I have an existing rule that works. It only works when a new ticket is created. What happens is we have a single select custom field in our tickets called 'Service Area'. There are 9 values to choose from. The rule looks at what value is chosen and then emails based on that choice the appropriate distribution email list.
I would like have this rule or a new rule send an email to the appropriate group if the service area is changed.
The code is below. If anyone has some advice I would GREATLY appreciate it.
Thanks,
John
John
RULE THAT IS NOT WORKING:
select HD_TICKET.CUSTOM_FIELD_VALUE1 as SERVICE_AREA,
case
when F.AFTER_VALUE = 'Service Desk: Front Office' then 'sdfo@grinnell.edu'
when F.AFTER_VALUE = 'Service Desk: Back Office' then 'sdbo@grinnell.edu'
when F.AFTER_VALUE like 'web%' then 'webguys@grinnell.edu'
when F.AFTER_VALUE = 'Networking' then 'networkteam@grinnell.edu'
when F.AFTER_VALUE = 'Phone' then 'tisteam@grinnell.edu'
when F.AFTER_VALUE = 'ASA' then 'ludwigl@grinnell.edu'
when F.AFTER_VALUE = 'CIS' then 'cisteam@grinnell.edu'
when F.AFTER_VALUE = 'CTS' then 'ctsteam@grinnell.edu'
when F.AFTER_VALUE = 'AV' then 'avteam@grinnell.edu'
else 'hammondj@grinnell.edu'
end as SERVICEAREA_EMAIL,
case
when F.AFTER_VALUE = 'Service Desk: Front Office' then 'sdfo@grinnell.edu'
when F.AFTER_VALUE = 'Service Desk: Back Office' then 'sdbo@grinnell.edu'
when F.AFTER_VALUE like 'web%' then 'webguys@grinnell.edu'
when F.AFTER_VALUE = 'Networking' then 'networkteam@grinnell.edu'
when F.AFTER_VALUE = 'Phone' then 'tisteam@grinnell.edu'
when F.AFTER_VALUE = 'ASA' then 'ludwigl@grinnell.edu'
when F.AFTER_VALUE = 'CIS' then 'cisteam@grinnell.edu'
when F.AFTER_VALUE = 'CTS' then 'ctsteam@grinnell.edu'
when F.AFTER_VALUE = 'AV' then 'avteam@grinnell.edu'
else 'hammondj@grinnell.edu'
end as SERVICEAREA_EMAIL,
HD_TICKET.TITLE,
HD_TICKET.ID as TICKNUM,
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 HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.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 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 C.DESCRIPTION LIKE 'TICKET CREATED%'
and HD_TICKET.HD_QUEUE_ID = 15
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 C.DESCRIPTION LIKE 'TICKET CREATED%'
and HD_TICKET.HD_QUEUE_ID = 15
RULE THAT IS WORKING
select HD_TICKET.CUSTOM_FIELD_VALUE1 as SERVICE_AREA,
case
when HD_TICKET.CUSTOM_FIELD_VALUE1 = 'Service Desk: Front Office' then 'sdfo@grinnell.edu'
when HD_TICKET.CUSTOM_FIELD_VALUE1 = 'Service Desk: Back Office' then 'sdbo@grinnell.edu'
when HD_TICKET.CUSTOM_FIELD_VALUE1 like 'web%' then 'webguys@grinnell.edu'
when HD_TICKET.CUSTOM_FIELD_VALUE1 = 'Networking' then 'networkteam@grinnell.edu'
when HD_TICKET.CUSTOM_FIELD_VALUE1 = 'Phone' then 'tisteam@grinnell.edu'
when HD_TICKET.CUSTOM_FIELD_VALUE1 = 'ASA' then 'asasd@grinnell.edu'
when HD_TICKET.CUSTOM_FIELD_VALUE1 = 'CIS' then 'sdcis@grinnell.edu'
when HD_TICKET.CUSTOM_FIELD_VALUE1 = 'CTS' then 'ctsteam@grinnell.edu'
when HD_TICKET.CUSTOM_FIELD_VALUE1 = 'AV' then 'avteam@grinnell.edu'
else 'hammondj@grinnell.edu'
end as SERVICEAREA_EMAIL,
case
when HD_TICKET.CUSTOM_FIELD_VALUE1 = 'Service Desk: Front Office' then 'sdfo@grinnell.edu'
when HD_TICKET.CUSTOM_FIELD_VALUE1 = 'Service Desk: Back Office' then 'sdbo@grinnell.edu'
when HD_TICKET.CUSTOM_FIELD_VALUE1 like 'web%' then 'webguys@grinnell.edu'
when HD_TICKET.CUSTOM_FIELD_VALUE1 = 'Networking' then 'networkteam@grinnell.edu'
when HD_TICKET.CUSTOM_FIELD_VALUE1 = 'Phone' then 'tisteam@grinnell.edu'
when HD_TICKET.CUSTOM_FIELD_VALUE1 = 'ASA' then 'asasd@grinnell.edu'
when HD_TICKET.CUSTOM_FIELD_VALUE1 = 'CIS' then 'sdcis@grinnell.edu'
when HD_TICKET.CUSTOM_FIELD_VALUE1 = 'CTS' then 'ctsteam@grinnell.edu'
when HD_TICKET.CUSTOM_FIELD_VALUE1 = 'AV' then 'avteam@grinnell.edu'
else 'hammondj@grinnell.edu'
end as SERVICEAREA_EMAIL,
HD_TICKET.TITLE,
HD_TICKET.ID as TICKNUM,
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 HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.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 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 C.DESCRIPTION LIKE 'TICKET CREATED%'
and HD_TICKET.HD_QUEUE_ID = 15
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 C.DESCRIPTION LIKE 'TICKET CREATED%'
and HD_TICKET.HD_QUEUE_ID = 15
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
8 years ago
Remove this line from the rule that isn't working:
and C.DESCRIPTION LIKE 'TICKET CREATED%'
With this line in place the rule will only match on changes where the ticket was created, not on updates.
