How to use ticket rules to suppress emails about machine or user deletes on closed tickets (4263353)

Because this occurs on all tickets that meet this criteria, this causes a problem for some folks who use the email on events notifications and do not want emails to be sent on closed tickets. However, they are unable to specify that email notifications only occur on open tickets so in order to get any email notifications they must live with this behavior.

Fortunately, this problem can be solved with ticket rules.

Since the delete triggers a comment, it also triggers a save event on tickets which then fires the ticket rules. A rule can check for the combination of a deletion on a closed ticket and suppress any email notifications that would normally occur.

Note that this rule has nothing to do with the ticket being reopened. If you are experiencing a problem with the ticket being reopened then you likely have another rule in place that is reopening the ticket.

CODE WRITTEN BY KACE SUPPORT FOR DEMONSTRATION PURPOSES ONLY AND NOT SUPPORTED BY KACE TECHNICAL SUPPORT.  CLIENT CONFIRMS THE NEED FOR TESTING AND ACCEPTS ALL RESPONSIBILITY FOR USE  AND MISUSE OF CODE. KACE SHALL NOT BE HELD LIABLE FOR DAMAGE OR DOWN TIME RESULTING  FROM USE OF THIS OR ANY CODE PRESENTED FOR PURPOSES OF TROUBLESHOOTING OR DEMONSTRATION.

*WARNING: Always review "Precautions when creating ticket rules" before writing any rules.

Rule Details

select C.ID
 from
 HD_TICKET
 join HD_TICKET_CHANGE C ON HD_TICKET.ID=HD_TICKET_ID
 and C.ID=
 join HD_TICKET_CHANGE_FIELD F on F.HD_TICKET_CHANGE_ID = C.ID
 and F.FIELD_CHANGED in ('MACHINE_ID','SUBMITTER_ID', 'OWNER_ID', 'APPROVER_ID')
 and F.AFTER_VALUE=0 and F.BEFORE_VALUE > 0
 join HD_TICKET_CHANGE_FIELD F2 on F2.HD_TICKET_CHANGE_ID = C.ID
 and F2.FIELD_CHANGED = 'COMMENT'
 and F2.AFTER_VALUE rlike '^(User|Machine) "[^"]*" was deleted.$'
 join HD_STATUS S ON HD_STATUS_ID=S.ID and S.STATE='Closed'
 where
 

update HD_TICKET_CHANGE
 set NOTIFY_USERS='', /*remove all users from notification list */
 DESCRIPTION = CONCAT(DESCRIPTION, '\nCustom Ticket Rule: Suppressing email notification for user/machine deletion'),
 LOCALIZED_DESCRIPTION = CONCAT(LOCALIZED_DESCRIPTION, '\nsprintf(\'Custom Ticket Rule: Suppressing email notification for user/machine deletion.\', \'\');')
 where HD_TICKET_CHANGE.ID = 

Appendix: If tickets are being reopened

This rule has nothing to do with the ticket being reopened. If you are experiencing a problem with the ticket being reopened then you likely have another rule in place that is reopening the ticket. Otherwise, if it is another rule causing this issue then you will need to modify it in a similar fashion.

Modify "reopen ticket". Note that you will no longer be able to modify "reopenTicket"

1. Click Helpdesk | Configuration | Queue Name | System Rules | Reopen Ticket. The editor for "reopen ticket" should now be open
2. Click the link that reads "Edit this ticket rule using the Custom Ticket Rule editor."
3. Disable the rule by unchecked the "enabled" box.
4. Save the rule.
5. Repeat steps 1 and 2 to reopen the rule editor.
6. Click on the Duplicate button at the bottom.
7. Browse to Helpdesk | Configuration | Queue Name | Custom Ticket Rules | Customize.
8. Click Copy of Re-open Ticket. The advanced editor for Re-open ticket should will open.
9. Modify the query by adding the SQL below to the end of it.
10. Enable it.
11. Save it.

From Step 9:

and HD_TICKET_CHANGE.COMMENT NOT like '%Machine "%" was deleted.%' and HD_TICKET_CHANGE.COMMENT NOT like '%User "%" was deleted.%' and HD_TICKET_CHANGE.DESCRIPTION NOT like '%Changed Ticket Machine from "%" to %Unassigned%' and HD_TICKET_CHANGE.DESCRIPTION NOT like '%Changed Ticket Submitter from "%" to %Unassigned%'     and HD_TICKET_CHANGE.DESCRIPTION NOT like '%Changed Ticket Approver from "%" to %Unassigned%'     and HD_TICKET_CHANGE.DESCRIPTION NOT like '%Changed Ticket Owner from "%" to %Unassigned%'