We would like to have tickets that are created from an email with the word "transcript" in the Subject (Ticket Title) be assigned to a particular ticket owner. Normally all new tickets are assigned to the default person who then re-assigns them so that is what would be automated. This is a special help desk and they understand all tickets with that word will be assigned to that person. As I understand the logic it goes something like {help needed}:

SELECT HD_TICKET.TITLE
WHEN HD_TICKET.TITLE='%transcript%' {and ticket is being created for the first time}
FROM HD_TICKET
{Change Ticket Owner to Deb Karlik}
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
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>)
Answered 07/26/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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%
Answered 07/26/2010 by: RichB
Third Degree Green Belt

Please log in to comment
0
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%'
Answered 07/26/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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.
Answered 07/26/2010 by: RichB
Third Degree Green Belt

Please log in to comment
0
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>)
Answered 07/27/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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
Answered 07/27/2010 by: RichB
Third Degree Green Belt

Please log in to comment
0
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'
Answered 07/27/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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'")
Answered 07/27/2010 by: RichB
Third Degree Green Belt

Please log in to comment
0
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'
Answered 07/27/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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. :)
Answered 07/27/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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.
Answered 07/27/2010 by: RichB
Third Degree Green Belt

Please log in to comment
0
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."
Answered 07/27/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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?
Answered 07/27/2010 by: RichB
Third Degree Green Belt

Please log in to comment
0
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'
Answered 07/27/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
You da man Andy! That did it. Thanks again.
Answered 07/27/2010 by: RichB
Third Degree Green Belt

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

Share