/build/static/layout/Breadcrumb_cap_w.png

Emailing users when machines deleted

Quick SQL question. I've run the rule to not email users with closed tickets and its working. I want to add additional ticket statuses to the SQL statement - will the following work?


select HD_TICKET_CHANGE.ID from
HD_TICKET JOIN HD_TICKET_CHANGE ON HD_TICKET.ID=HD_TICKET_ID and HD_TICKET_CHANGE.ID=<CHANGE_ID>
JOIN HD_STATUS ON HD_STATUS_ID=HD_STATUS.ID
WHERE
(DESCRIPTION LIKE '%Changed Ticket Machine from "%" to %Unassigned%'
OR DESCRIPTION LIKE '%Changed Ticket Submitter from "%" to Unassigned%'
OR OWNERS_ONLY_DESCRIPTION LIKE '%Changed Ticket Machine from "%" to %Unassigned%'
OR OWNERS_ONLY_DESCRIPTION LIKE '%Changed Ticket Submitter from "%" to Unassigned%')
and ( COMMENT LIKE '%Machine % was deleted%' OR COMMENT LIKE 'User % was deleted%')
and HD_STATUS.STATE = 'Closed' or 'Resolved' or 'Awaiting Dispatch' or 'Work In Progress' or 'Time permitted / Projects'

0 Comments   [ + ] Show comments

Answers (4)

Posted by: GillySpy 12 years ago
7th Degree Black Belt
0
Not quite, also note that STATE is not the same as status. You can create any status that you want but there are only 3 states. The states are "Closed", "Opened", "Stalled".

For you use HD_STATUS.NAME IN ('Closed', 'Resolved', 'etc')
or
HD_STATUS.NAME =any('Closed','Resolved','etc')

ref: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in
Posted by: ddevore 12 years ago
Fourth Degree Green Belt
0
Thanks for getting back to me so quickly. So in I should be able to keep it simple by just changing the last line to

AND HD_STATUS.STATE = 'Closed' or 'Opened' or 'Stalled'

This should block any emails when workstations are deleted, right?

Thanks again!
Dennis
Posted by: GillySpy 12 years ago
7th Degree Black Belt
0
If you want it to fire on any status then just take out the line completely. To limit it then put something like this

and HD_STATUS.STATE IN ('Closed','Opened')
Posted by: RichB 12 years ago
Second Degree Brown Belt
0
The rule is mostly working for me too as described here: http://www.kace.com/support/kb/index.php?action=artikel&cat=8&id=794

Email addresses added to the CC List on a ticket are still getting emails though when a user that was the submitter is deleted. We have many users that submitted tickets with an older email address format that we have changed so the old emails bounce if a ticket is updated. I'd like to clean up all the older accounts but this is preventing me from doing that.

Can this update query be modified to include email notifications sent to the CC List? Here is the update query:

update HD_TICKET_CHANGE
set
HD_TICKET_CHANGE.NOTIFY_USERS=', /*remove all users from notification list */
OWNERS_ONLY_DESCRIPTION=
TRIM(BOTH '\n' FROM(REPLACE (CONCAT(TRIM(BOTH '\n' FROM
OWNERS_ONLY_DESCRIPTION), '\n',
'Ticket Rule: Suppressing email notification for user/machine deletion'
,'\n') ,'\n\n','\n')))
where
HD_TICKET_CHANGE.ID in (<TICKET_IDS>)
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ