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.

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

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,


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



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,


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

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
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.

Answered 10/12/2015 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share