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:
2nd rule:
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)
Please log in to answer
Posted by:
airwolf
14 years ago
Posted by:
kaneda0149
14 years ago
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
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:
kaneda0149
14 years ago
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
14 years ago
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
13 years ago
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
so that the conversation will remain readable.