KACE Product Support

Set this rule to run every 15 minutes. It won't be instantaneous, but all new tickets will have the owner properly reassigned within 15 minutes.

NOTE: I haven't tested this code, so proceed with caution.

Select Query:
SELECT * FROM HD_TICKET
JOIN HD_TICKET_CHANGE ON (HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID)
WHERE HD_TICKET.TITLE like '%transcript%' AND HD_TICKET_CHANGE.DESCRIPTION like '%Ticket Created%' AND TIMEDIFF(NOW(),HD_TICKET_CHANGE.TIMESTAMP) < (15*60)

Update Query:
UPDATE HD_TICKET T
JOIN USER O ON (T.OWNER_ID = O.ID)
SET T.OWNER_ID = O.ID
WHERE O.FULL_NAME like 'Deb Karlik' AND T.ID in (<TICKET_IDS>)

Thanks for the queries. I tried a test email and they are automatically assigned to a different person initially. I waited well over 15 minutes and it is still assigned to the initial person and not Deb Karlik.

I tried modifying the Select query so every ticket in the queue with the word transcript would have it's owner changed (there are 75 tickets matching that criteria and most are already owned by Deb) but this also failed to produce any results:

SELECT * FROM HD_TICKET
JOIN HD_TICKET_CHANGE ON (HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID)
WHERE HD_TICKET.TITLE like '%transcript%

You stated that you only wanted the rule to run on new tickets, so that's how I wrote the code. If you want to run it on tickets regardless of whether they are new or not, then use this as the SELECT QUERY:


SELECT * FROM HD_TICKET
WHERE HD_TICKET.TITLE like '%transcript%'


OK I used that as the select query and got this result:


29:53> Starting: Mon, 26 Jul 2010 15:29:53 -0600
29:53> Executing Select Query...
29:53> selected 143 rows
29:53> Executing Update Query...
29:53> updated 0 rows
29:53> Ending: Mon, 26 Jul 2010 15:29:53 -0600

When I search for tickets with "Title contains transcript" I get 75 results so I'm not sure why it found 143 rows. It still didn't update anything with the Owner yet though.

When you found your 75 results, were you looking at only active tickets? To filter out closed tickets, use this:


SELECT * FROM HD_TICKET
JOIN HD_STATUS ON (HD_TICKET.HD_STATUS_ID = HD_STATUS.ID)
WHERE HD_TICKET.TITLE like '%transcript%' AND HD_STATUS.STATE like 'opened'


Also, try this instead for the update query:


UPDATE HD_TICKET T
SET T.OWNER_ID = (SELECT ID FROM USER WHERE FULL_NAME like 'Deb Karlik')
WHERE T.ID in (<TICKET_IDS>)


When I found the 75 results I was using the Advanced Search and yes it found closed tickets too. I like the idea of only modifying active tickets. I modified the Select Query to look for "HD_STATUS.STATE not like 'closed'" since "New" tickets are in a "stalled" state and not "opened."

SELECT * FROM HD_TICKET
JOIN HD_STATUS ON (HD_TICKET.HD_STATUS_ID = HD_STATUS.ID)
WHERE HD_TICKET.TITLE like '%transcript%' AND HD_STATUS.STATE not like 'closed'

I changed the Update Query per your instructions.

There are two tickets in that New status and they are being found according to the Run Log but the Update Query is still not changing the ticket owner:
24:11> Starting: Tue, 27 Jul 2010 14:24:11 -0600
24:11> Executing Select Query...
24:11> selected 2 rows
24:11> Executing Update Query...
24:11> updated 0 rows
24:11> Ending: Tue, 27 Jul 2010 14:24:11 -0600

An update is only performed if a change is involved - meaning if those two tickets already have Deb Karlik listed as the Owner then no update will take place.

Also, the way the select query passes IDs to the update query is sort of flaky. Try changing * to ID in the select query like so:


SELECT ID FROM HD_TICKET
JOIN HD_STATUS ON (HD_TICKET.HD_STATUS_ID = HD_STATUS.ID)
WHERE HD_TICKET.TITLE like '%transcript%' AND HD_STATUS.STATE not like 'closed'

Deb Karlik is not the current Owner of those two tickets.

Changing * to ID produced this:

50:51> mysql error: [1052: Column 'ID' in field list is ambiguous] in EXECUTE("SELECT ID FROM HD_TICKET
JOIN HD_STATUS ON (HD_TICKET.HD_STATUS_ID = HD_STATUS.ID)
WHERE HD_TICKET.TITLE like '%transcript%' AND HD_STATUS.STATE not like 'closed'")

Sorry, I forgot about the join. You have to specify the table when explicitly choosing a field.


SELECT HD_TICKET.ID FROM HD_TICKET
JOIN HD_STATUS ON (HD_TICKET.HD_STATUS_ID = HD_STATUS.ID)
WHERE HD_TICKET.TITLE like '%transcript%' AND HD_STATUS.STATE not like 'closed'

Also, I think the column may have to be called 'ID', in which case you'll have to do this:


SELECT HD_TICKET.ID AS 'ID' FROM HD_TICKET
JOIN HD_STATUS ON (HD_TICKET.HD_STATUS_ID = HD_STATUS.ID)
WHERE HD_TICKET.TITLE like '%transcript%' AND HD_STATUS.STATE not like 'closed'


The custom rule interface could be a bit more intuitive, but I can deal with the caveats. :)

Yeah! It's working! Thanks a bunch. It worked with and without the "AS'ID'" part. I see that updating a ticket Owner through this method may not have the normal ticket change notification sent though so the new owner won't be notified of the new ticket. We can work around that though. I appreciate the help.

If you add a "description" comment during the Update Query, then it will add the little comment to the ticket with a bullet next to it stating that the owner was changed. This would trigger email notification. That, of course, is the hard way. :)

Try checking the box in the rule that says, "Results are tickets, add a comment to each one". This will add a comment, which will trigger the email notification. Just add anything you want as the comment, such as "Owner automatically changed to Deb Karlik by Custom SQL Rule."

That's almost perfect now. The ticket owner is changed based on the word in the title and a comment is added so the owner is being notified.

It seems every 15 minutes the comment will continue to be added to the ticket though. The comment only needs to be added once when the owner change is made so I assume another JOIN statement will be needed?

You can add to the WHERE clause in the select query. Then, the select query will only return results if the owner isn't already Deb Karlik.

SELECT HD_TICKET.ID FROM HD_TICKET
JOIN HD_STATUS ON (HD_TICKET.HD_STATUS_ID = HD_STATUS.ID)
JOIN USER ON (HD_TICKET.OWNER_ID = USER.ID)
WHERE HD_TICKET.TITLE like '%transcript%' AND HD_STATUS.STATE not like 'closed' AND USER.FULL_NAME not like 'Deb Karlik'

You da man Andy! That did it. Thanks again.