Ticket Rule
I have 3 sets of rule that; 1st creates an email to group of individuals when a new ticket is created. 2nd when owner has set status to 'Need More Info' an email goes out to the submitter. 3rd when submitter replies to the email, it updated the ticket and notified the owner. For some reason it's no longer working and I can't seem to be able to figure it out. Instead, it's creating a brand new ticket which it did not before. Below are my SQL statements. All the help would be great!
1ST RULE
2nd RULE
3rd RULE
1ST RULE
select distinct HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE as ACCTNAME,
HD_TICKET.CUSTOM_FIELD_VALUE0 as ACCTNUM,
HD_TICKET.CUSTOM_FIELD_VALUE9 as AMOUNT,
U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
'recipient@email' as NEWTICKETEMAIL,
HD_TICKET_CHANGE.COMMENT as COMMENT,
HD_PRIORITY.NAME as PRIORITY,
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP)
from ( HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID=<CHANGE_ID>
left join USER U1 on U1.ID = HD_TICKET.OWNER_ID
left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
left join USER U3 on U3.ID = HD_TICKET_CHANGE.USER_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID and
HD_STATUS.ID = HD_STATUS_ID and
HD_IMPACT.ID = HD_IMPACT_ID and
HD_CATEGORY.ID = HD_CATEGORY_ID and
HD_TICKET_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%'
2nd RULE
select distinct HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE as ACCTNAME,
CASE WHEN HD_TICKET.CUSTOM_FIELD_VALUE11 = 'Yes' THEN (CONCAT('[TICK: ',HD_TICKET.ID,'] ', 'File Rejected Need To Update: ',HD_TICKET.TITLE))
ELSE (CONCAT('[TICK: ',HD_TICKET.ID,'] ', 'Need More Info On: ',HD_TICKET.TITLE)) END AS Subject,
U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
HD_PRIORITY.NAME as PRIORITY,
HD_TICKET_CHANGE.COMMENT as COMMENT
from ( HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID=<CHANGE_ID>
left join USER U1 on U1.ID = HD_TICKET.OWNER_ID
left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
left join USER U3 on U3.ID = HD_TICKET.APPROVER_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID and
HD_STATUS.ID = HD_STATUS_ID and
HD_IMPACT.ID = HD_IMPACT_ID and
HD_CATEGORY.ID = HD_CATEGORY_ID and ((( HD_STATUS.NAME = 'Need More Info') AND HD_TICKET.RESOLUTION != 'Email Sent') and HD_TICKET.HD_QUEUE_ID = 8 )
3rd RULE
select distinct HD_TICKET.ID,
HD_TICKET.OWNER_ID as OWNER_ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE as ACCTNAME,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.STATE as STATE,
OWNER.USER_NAME as OWNER_NAME,
OWNER.FULL_NAME as OWNER_FULLNAME,
OWNER.EMAIL as OWNER_EMAIL,
UPDATER.USER_NAME as UPDATERNAME,
UPDATER.EMAIL as UPDATEREMAIL
from (HD_TICKET, HD_STATUS)
left join HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID = <CHANGE_ID>
left join HD_TICKET_CHANGE_FIELD on HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID = HD_TICKET_CHANGE.ID
left join USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID
left join USER UPDATER on UPDATER.ID = HD_TICKET_CHANGE.USER_ID
where HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
and HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED !='SATISFACTION_RATING'
and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED !='SATISFACTION_COMMENT'
and HD_STATUS.NAME = 'Need More Info'
and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL)
and UPDATER.ID > 0
0 Comments
[ + ] Show comments
Answers (7)
Please log in to answer
Posted by:
GillySpy
14 years ago
So, you're using rules to update the ticket so it creates a new entry. Smart. I like it. I usually use the update but you could do some more complex updates your way with a simpler methodology. Drawback is that your update is going to fire all the rules again but on most helpdesks that is not an issue.
Posted by:
imranqamar34
12 years ago
Hello Kaneda0149/Gilly Spy,
can you please tell me what are settings for these rules?
Thanks,
Posted by:
GillySpy
14 years ago
are you saying the 3rd rule is not working? If so, what isn't working the select, the email or the update? You only include the select and it looks good. The only thing glaring is that one of your conditions is that there is an owner on the ticket AND the person updating the ticket is not also the owner. (
Otherwise, do you have the output of the run log fromt he rule immediately after it is fired?
The wrench in this is that you said a brand new ticket is being created. What is the content of this ticket (screenshot?) ? Was the ticket that was being updated also get updated?
(UPDATER.ID <> OWNER.ID or OWNER.ID is NULL)Otherwise, do you have the output of the run log fromt he rule immediately after it is fired?
The wrench in this is that you said a brand new ticket is being created. What is the content of this ticket (screenshot?) ? Was the ticket that was being updated also get updated?
Posted by:
kaneda0149
14 years ago
Hi GillSpy,
Thanks for responding so quickly. Yes, the 3rd rule is not working. It's not updating the original ticket with the contents of the reply email. Instead it's creating a brand new ticket with the reply email content. Even when I disabled the 3rd rule and only used 1st and 2nd, if the user replies it is still creating a new ticket. The logic I used was copied from the System Ticket Rule called "CustomerResponded". Thank you so much!
When looking at the run log as you suggested, on the 1st rule when creating a new request the run log did not appear to do anything (as shown below).
Looking at the 2nd rule which sends an email to the submitter with my questions, it worked as designed (as shown below).
Looking at the 3rd rule when the submitter responds to the email, nothing (as shown below).
Here's the kicker, when looking back at the 1st rule because I got an email stating a new ticket was created now the run log launched (as shown below).
Thanks for responding so quickly. Yes, the 3rd rule is not working. It's not updating the original ticket with the contents of the reply email. Instead it's creating a brand new ticket with the reply email content. Even when I disabled the 3rd rule and only used 1st and 2nd, if the user replies it is still creating a new ticket. The logic I used was copied from the System Ticket Rule called "CustomerResponded". Thank you so much!
When looking at the run log as you suggested, on the 1st rule when creating a new request the run log did not appear to do anything (as shown below).
34:58> Starting: Tue, 07 Dec 2010 08:34:58 -0500
34:58> Executing Select Query...
34:58> selected 0 rows
Looking at the 2nd rule which sends an email to the submitter with my questions, it worked as designed (as shown below).
41:05> Starting: Tue, 07 Dec 2010 08:41:05 -0500
41:05> Executing Select Query...
41:05> selected 1 rows
41:05> Sending ticket notifications...
41:05> sent mail to 1 of 1
41:05> Executing Update Query...
41:05> updated 1 rows
41:05> Ending: Tue, 07 Dec 2010 08:41:05 -0500Looking at the 3rd rule when the submitter responds to the email, nothing (as shown below).
34:58> Starting: Tue, 07 Dec 2010 08:34:58 -0500
34:58> Executing Select Query...
34:58> selected 0 rows
Here's the kicker, when looking back at the 1st rule because I got an email stating a new ticket was created now the run log launched (as shown below).
34:58> Starting: Tue, 07 Dec 2010 08:34:58 -0500
34:58> Executing Select Query...
34:58> selected 1 rows
34:58> Sending ticket notifications...
34:58> sent mail to 1 of 1
34:58> Executing Update Query...
34:58> updated 1 rows
34:58> Ending: Tue, 07 Dec 2010 08:34:58 -0500
Posted by:
GillySpy
14 years ago
The new ticket must be created by the second rule or by something else. Is rule #2 sending an email to the helpdesk? If you cannot tell then please include the details of the 2nd rule.
The 3rd rule is not doing anything as the select returns 0 rows so the criteria for it is not being met. Here is the criteria for rule #3, which one(s) is not true?:
The 3rd rule is not doing anything as the select returns 0 rows so the criteria for it is not being met. Here is the criteria for rule #3, which one(s) is not true?:
- not interested if only change was either to 'SATISFACTION_RATING' or 'SATISFACTION_COMMENT'
- Status after save is 'Need More Info'
- Person saving ticket is not the owner
- User record of person saving the ticket is tied to the ticket comment (i.e. would see something like this in the ticket comments
Kaneda0149 on Dec 07 2010 08:34:58 AM
Posted by:
kaneda0149
14 years ago
Hi GillSpy,
I needed a 2nd eye and found the root of the issue. On my 2nd rule statement, there was a space between TICK: and all I had to do was remove it in both places and I was good to go. All worked perfectly! Thank you so much.
I needed a 2nd eye and found the root of the issue. On my 2nd rule statement, there was a space between TICK: and all I had to do was remove it in both places and I was good to go. All worked perfectly! Thank you so much.
CASE WHEN HD_TICKET.CUSTOM_FIELD_VALUE11 = 'Yes' THEN (CONCAT('[TICK:',HD_TICKET.ID,'] ', 'File Rejected Need To Update: ',HD_TICKET.TITLE))
ELSE (CONCAT('[TICK:',HD_TICKET.ID,'] ', 'Need More Info On: ',HD_TICKET.TITLE)) END AS Subject
Posted by:
kaneda0149
14 years ago
Yeah, I just found that out today from our IT department. My bad. Once our database gets larger, it will degrade. I will have to re-visit this. Thanks for all your help!
Comments:
-
Hi kaneda0149,
I want to implement the same rules as you did in our environment.Can you please send me whole settings of all your three rules?
Thanks,
Imran - imranqamar34 12 years ago
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.