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'
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)
Please log in to answer
Posted by:
GillySpy
13 years ago
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
or
ref: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in
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
13 years ago
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
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
13 years ago
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
13 years ago
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:
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.