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

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

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
Answered 11/01/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
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
Answered 11/01/2011 by: ddevore
Fourth Degree Green Belt

Please log in to comment
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')
Answered 11/01/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
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>)
Answered 11/08/2011 by: RichB
Third Degree Green Belt

Please log in to comment
Answer this question or Comment on this question for clarity