KACE Product Support

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>)

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. [;)]

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.

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).

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>)

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".

That's wonderful- thanks!

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

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