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

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
Aside from the queries, how are the ticket rules setup? What are the rest of the settings? Screenshots would help diagnose the issue.
Answered 09/15/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
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
Answered 09/17/2010 by: kaneda0149
Orange Senior Belt

Please log in to comment
0
I meant the settings for the ticket rule itself, not the queue.
Answered 09/17/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
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>))
Answered 09/17/2010 by: kaneda0149
Orange Senior Belt

Please log in to comment
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>))
Answered 09/17/2010 by: kaneda0149
Orange Senior Belt

Please log in to comment
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!
Answered 07/26/2011 by: rodsmith01
Senior Yellow Belt

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

Share