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

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

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
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. (
(UPDATER.ID <> OWNER.ID or OWNER.ID is NULL)
) I like these conditions, but when testing it is tempting to use the same account for both so perhaps that's masking the issue.

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?
Answered 12/06/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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).

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


Looking 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
Answered 12/07/2010 by: kaneda0149
Orange Senior Belt

Please log in to comment
0
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?:
  • 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
Answered 12/07/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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.


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
Answered 12/07/2010 by: kaneda0149
Orange Senior Belt

Please log in to comment
1
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.
Answered 12/07/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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!
Answered 12/07/2010 by: kaneda0149
Orange Senior Belt

  • 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
Please log in to comment
0

Hello Kaneda0149/Gilly Spy,

can you please tell me what are settings for these rules?

Thanks,

Answered 07/13/2012 by: imranqamar34
Senior Yellow Belt

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