Currently, if we delete a machine from inventory, all Help Desk tickets related to that machine will change themselves to "Unassigned" - and then email the user for each ticket that has been changed.

I'd like to have a Ticket Rule that stops emails from being sent out in this scenario. Rather than butcher the SQL myself, I figured I'd post here and see if anyone has done this yet.
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

1
Here ya go. This is a helpdesk custom ticket rule that is run on ticket save.

SELECT QUERY:
select HD_TICKET_CHANGE.ID from
HD_TICKET JOIN HD_TICKET_CHANGE ON HD_TICKET.ID=HD_TICKET_ID
JOIN HD_STATUS ON HD_STATUS_ID=HD_STATUS.ID
JOIN (select MAX(ID) ID from HD_TICKET_CHANGE GROUP BY HD_TICKET_ID) MAXHTC ON MAXHTC.ID=HD_TICKET_CHANGE.ID

WHERE
(DESCRIPTION LIKE '%Changed Ticket Machine from "%" to %Unassigned%'
OR 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'


UPDATE QUERY:
update HD_TICKET_CHANGE
set
HD_TICKET_CHANGE.NOTIFY_USERS=', /*remove all users from notification list */
OWNERS_ONLY_DESCRIPTION=CONCAT('Ticket Rule: Suppressing email notification for user/machine deletion') /*explain to owners what is happening*/
where
HD_TICKET_CHANGE.ID in (<TICKET_IDS>)
Answered 11/24/2009 by: cblake
Red Belt

Please log in to comment
1
Great work, cblake! We've only been using the KBOX for a few months, so we haven't run into this problem much yet. I'm going to create a rule for this though - just to avoid the future headache. [;)]
Answered 11/24/2009 by: airwolf
Tenth Degree Black Belt

Please log in to comment
1
Thanks Chris!

I wonder why a Ticket Rule cannot be exported like other Resources. That would make the process much simpler to import a rule that works and is shared. Enhancement Request time.
Answered 11/24/2009 by: RichB
Third Degree Green Belt

Please log in to comment
1
Rich, I know KACE is working on developing the ability to export many different types of things for us to share on these forums. However, ticket rules are fairly easy to copy and paste (just a SQL select query and update statement).
Answered 11/24/2009 by: airwolf
Tenth Degree Black Belt

Please log in to comment
1
We have one that also doesn't email if you delete a user (technician) that was assigned to a ticket

select HD_TICKET_CHANGE.ID from
HD_TICKET JOIN HD_TICKET_CHANGE ON HD_TICKET.ID=HD_TICKET_ID
JOIN HD_STATUS ON HD_STATUS_ID=HD_STATUS.ID
JOIN (select MAX(ID) ID from HD_TICKET_CHANGE GROUP BY HD_TICKET_ID) MAXHTC ON MAXHTC.ID=HD_TICKET_CHANGE.ID

WHERE
(DESCRIPTION LIKE '%Changed Ticket Machine from "%" to Unassigned%'
OR 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'

on ticket save

Update query

update HD_TICKET_CHANGE
set
HD_TICKET_CHANGE.NOTIFY_USERS=', /*remove all users from notification list */
OWNERS_ONLY_DESCRIPTION=CONCAT('Ticket Rule: Suppressing email notification for user/machine deletion') /*explain to owners what is happening*/
where
HD_TICKET_CHANGE.ID in (<TICKET_IDS>)
Answered 11/25/2009 by: jg1000c
Orange Belt

Please log in to comment
1
Thanks - not sure what happened but it didn't work for me at first, it does now though.

I would think the easiest way to KACE to add the ability to copy/paste a custom ticket rule would be to have "Add SQL Rule" in the drop-down, similar to "Add New SQL Report".
Answered 11/25/2009 by: TJSmithCIQ
Orange Belt

Please log in to comment
1
That's wonderful- thanks!
Answered 11/25/2009 by: cblake
Red Belt

Please log in to comment
1
cblake's rule is good for all versions of KBOX to date. 5.0 (and up) syntax would want the query to be:

select C.ID
from HD_TICKET
JOIN HD_TICKET_CHANGE C ON HD_TICKET.ID=C.HD_TICKET_ID and C.ID=<CHANGE_ID>
JOIN HD_TICKET_CHANGE_FIELD F ON F.HD_TICKET_CHANGE_ID=C.ID and F.FIELD_CHANGED='COMMENT'
JOIN HD_STATUS ON HD_STATUS_ID=HD_STATUS.ID
WHERE
AFTER_VALUE RLIKE '^(User|Machine).*was deleted.$'
and HD_STATUS.STATE = 'Closed'

Benefits of this syntax:
* faster
* easier to read (arguable)
* can't make the "run now" mistake
* avoids the "click save twice" problem (which would never happen in this case tho)
* more future proof

But...
harder to test
Answered 11/25/2009 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
1
Can someone tell me why I get this error? I only get this error on 1 of our queues.

27:17> Starting: Wed, 31 Aug 2011 10:27:17 -0500
27:17> Executing Select Query...
27:17> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=<CHANGE_ID>
JOIN HD_STATUS ON HD_STATUS_ID=HD_STATUS.ID
WHERE
(DESCRIPTION L' at line 2] in EXECUTE("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'")


SQL:
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'

Thanks for any help
Answered 08/31/2011 by: RBakerNoble
Senior Yellow Belt

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