/build/static/layout/Breadcrumb_cap_w.png

Rule On Stalled State Sending 2nd Email

I have 2 rules; the 1st rule sends the submitter an email when the owner has changed the status to a stalled state (aka 'User Feedback Requested') and added a comment to get more information from the submitter. Within the body of email, it states to simply reply to the email with his/her response. My 2nd rule takes that replied email and updates the request to Open and inserts their comment and sends the owner an email stating it's been updated. This works perfectly except the initial email to the submitter goes out again stating we need a response. Below is the 1st query that sends out the email to the submitter as well as the 2nd query that updates the status state from 'stalled' to 'opened'. The help would be awesome!

1st rule:

select distinct HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
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 like 'User Feedback Requested') and HD_TICKET.HD_QUEUE_ID = 3 )


2nd rule:

select distinct HD_TICKET.ID,
HD_TICKET.OWNER_ID as OWNER_ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
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.STATE = 'stalled'
and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL)
and UPDATER.ID > 0

0 Comments   [ + ] Show comments

Answers (6)

Posted by: airwolf 13 years ago
Red Belt
0
Aside from the queries, how are the ticket rules setup? What are the rest of the settings? Screenshots would help diagnose the issue.
Posted by: kaneda0149 13 years ago
Orange Senior Belt
0
Hi airwolf,

I can't seem to be able to add an attachment. My settings on my Email on Events are as follows:

Any Change [Owner checked off]
Owner Change [Owner checked off]
Status Change [Nothing checked]
Comment [Nothing checked]
Approval Change [Owner and Submitter checked off]
Resolution Change [Nothing checked]
Escalation [Owner checked off]
Support Request Closed [Nothing checked]
New Support Reqeust Via Email [Submitter checked]

System Ticket Rules:
WaitingOverdue = enabled
OverdueClose = disabled
EmailOnClose = disabled
CustomerResponded = enabled
Reopen Ticket = disabled
Posted by: airwolf 13 years ago
Red Belt
0
I meant the settings for the ticket rule itself, not the queue.
Posted by: kaneda0149 13 years ago
Orange Senior Belt
0
Sorry, here are the settings for the 1st rule:




Record Created:
Sep 17 2010, 03:56 PM

Record Last Modified:
Sep 17 2010 03:48:53 PM

Title:


Order:


Queue:
CPR

Notes:
Rule sends submitter an email when Owner updates status to User Feedback Requested.



Frequency:
15 Minutes Hourly Daily Weekly Monthly on Ticket Save


Next Run:
January February March April May June July August September October November December 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 at 1 2 3 4 5 6 7 8 9 10 11 12 : 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 AM PM

Current Server Time:
Sep 17 2010 03:56:27 PM



Enabled:


Select Query:
select distinct HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
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 = 'User Feedback Requested') AND HD_TICKET.RESOLUTION not like '%Email sent%') and HD_TICKET.HD_QUEUE_ID = 3 )


[/align]



Send query results to someone


Turning this on will send a table of results of the Select Query to the email address(s) specified. If you are specifying more than one email, then separate them with commas. All the columns returned by the Select Query will be included in the email.[/align]


Email:





Results are tickets, add a comment to each one


This will allow you to add a comment to each ticket from the Select Query. This is useful because the Update Query specified later may update a Ticket without logging that information. Here you could add a message like 'Ticket Rule: Increase Priority to High triggered.' This would give you an indication of what tickets have been changed. [/align]


Comment:


Owners Only:





Send an email for each result row


[/align]


Subject:


Email Column:


Email Body:
$owner_fullname has a question concerning your support request.

══════════════════════════════════════════════════════════════════════
COMMENT:
$comment

══════════════════════════════════════════════════════════════════════

To respond to the support request, please reply to this email message and the request will be updated automatically.


Thank you,

CPR Team





Run an update query, using the results from the one above


[/align]


Update Query:
update HD_TICKET as T
set T.RESOLUTION = 'Email sent'
where
(T.ID in (<TICKET_IDS>))
Posted by: kaneda0149 13 years ago
Orange Senior Belt
0
Settings for the 2nd rule:

Record Created: Sep 17 2010, 04:03 PM
Record Last Modified: Sep 17 2010 03:48:53 PM
Title:
Order:
Queue: CPR
Notes: Moves ticket into a special status when any user other than the owner updates it, and notifies the owner by email.

Frequency: 15 Minutes Hourly Daily Weekly Monthly on Ticket Save
Next Run: January February March April May June July August September October November December 1, etc... AM PM Current Server Time: Sep 17 2010 04:03:48 PM

Enabled:
Select Query: select distinct HD_TICKET.ID,
HD_TICKET.OWNER_ID as OWNER_ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
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.STATE = 'stalled'
and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL)
and UPDATER.ID > 0 View Ticket Search Results


Send query results to someone

Results are tickets, add a comment to each one

Send an email for each result row
Subject:
Email Column:
Email Body: Ticket $ticknum owned by "$owner_name" has been updated by the submitter.

You may review the comment here:
http://support/adminui/ticket?ID=$ticknum
The status was "$status_name" and is now marked as "Opened".


Run an update query, using the results from the one above

Update Query: update HD_TICKET as T, HD_STATUS as T5
set T.HD_STATUS_ID = T5.ID,
T.TIME_OPENED = IF(T5.STATE = 'opened', NOW(), T.TIME_OPENED),
T.TIME_CLOSED = IF(T5.STATE = 'closed', NOW(), T.TIME_CLOSED),
T.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), T.TIME_STALLED),
T.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_RATING),
T.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_COMMENT),
T.RESOLUTION = '
where T5.NAME = 'Opened' and
T.HD_QUEUE_ID = T5.HD_QUEUE_ID and
(T.ID in (<TICKET_IDS>))
Posted by: rodsmith01 12 years ago
Senior Yellow Belt
0
Kaneda0149,

left join HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID = <CHANGE_ID>

i am getting a syntax error from the line above, any thoughts?

thanks!
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