/build/static/layout/Breadcrumb_cap_w.png

Assign Owner based on Subject

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

Answers (15)

Posted by: airwolf 13 years ago
Red Belt
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>)
Posted by: RichB 13 years ago
Second Degree Brown Belt
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%
Posted by: airwolf 13 years ago
Red Belt
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%'
Posted by: RichB 13 years ago
Second Degree Brown Belt
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.
Posted by: airwolf 13 years ago
Red Belt
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>)
Posted by: RichB 13 years ago
Second Degree Brown Belt
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
Posted by: airwolf 13 years ago
Red Belt
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'
Posted by: RichB 13 years ago
Second Degree Brown Belt
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'")
Posted by: airwolf 13 years ago
Red Belt
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'
Posted by: airwolf 13 years ago
Red Belt
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. :)
Posted by: RichB 13 years ago
Second Degree Brown Belt
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.
Posted by: airwolf 13 years ago
Red Belt
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."
Posted by: RichB 13 years ago
Second Degree Brown Belt
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?
Posted by: airwolf 13 years ago
Red Belt
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'
Posted by: RichB 13 years ago
Second Degree Brown Belt
0
You da man Andy! That did it. Thanks again.
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ