Custom Ticket Rule to Send email to Submitter when Tcket Status is "need More Info"
I want to set Custom ticker rule like when
Rule 1: "owner has set status to 'Need More Info' an email goes out to the submitter and ask for required information"
Then an other rule like
Rule2: " when submitter replies to the email, it updated the ticket and notified the owner."
Below are SQL statements that I copied from this post http://www.itninja.com/question/ticket-rule#
Can some one please tell me what will be the settings of these rules ? Any help would be really appriciated.
SQL Statements:
RULE 1:
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 )
RULE 2:
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
Answers (5)
The first ticket rule appears to be a wizard-generated rule and typically I've found that it's only the last line or two that you need to focus on as this targets what you specify. The nice thing here is that once you understand this and how to use the MySQL Query Browser, you can edit these pretty easily to get as specific as you need. Here's the breakdown of the last section, and all of these must be true in order for a match to occur:
where... > specifies which criteria to match
HD_STATUS.NAME = 'Need More Info' > specifies the HD_STATUS.NAME value must equal 'Need More Info' (i.e. tickets with other statuses will not be acted upon by the ticket rule)
HD_TICKET.RESOLUTION != 'Email Sent' > specifies the HD_TICKET.RESOLUTION must not have 'Email Sent' as the value (i.e. if a ticket's resolution indicates that an email has been sent, then the ticket rule will not apply)
HD_TICKET.HD_QUEUE_ID = 8 > specifies that this rule will only apply to tickets in queue 8
In short, a ticket must be in queue 8, not have had any resolution email sent and must have "Need More Info" as the status in order for this ticket rule to apply (and do the specified action - i.e. query update, email, etc).
Reading the post you referenced, it sounds like you want an email to go to the submitter when the status is "Need More Info", in which case you'll just need to setup the ticket rule to use the "send an email for each result row" and use the ticket's submitter as the recipient. For some examples of sending emails via ticket rules, check out these posts I did:
http://www.itninja.com/blog/view/k1000-service-desk-time-tracking-queue-config-custom-ticket-rules-sql-reports
http://www.itninja.com/blog/view/k1000-service-desk-equipment-request-queue-config-custom-ticket-rules
Just be aware that for the Email Column field you'll probably want to use U2.EMAIL (i.e. SUBMITTER_EMAIL), assuming you'll reuse the code I posted and just tweak it.
If you need a basic intro to MySQL queries (including how to setup & use the MySQL Query Browser), see if this might help:
http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example
John
If you are new to the K1000, you probably don't have 8 queues already, so that would be the first thing I would consider getting rid of (the "and HD_TICKET.HD_QUEUE_ID = 8" statement).
Next point, HD_TICKET.CUSTOM_FIELD_VALUE11 refers to a custom field (Custom Field 12 in the queue's GUI config screen) in the person's queue which this rule was created to run.
What I would recommend in your situation (new to K1000 & MySQL) would be to use the ticket rule wizard to create a rule based on your criteria and tweak it from there. Go into Helpdesk > Configuration > Departments > click on your queue > scroll down to Custom Ticket Rules and click on Customize > Choose Action > Add Ticket Rule > create a dummy rule with whatever values you want.
From here, go to the last line of the Select Query and adjust the dummy value you specified above to HD_TICKET_CHANGE_FIELD.AFTER_VALUE = 'Need More Info' (as chucksteel suggested). You'll need to edit the code manually as this field isn't available using the wizard. Then clear out the update query and setup the email section as you did before.
I'd also recommend running this through the MySQL Query Browser first, to make sure the ticket rule is hitting the tickets you intend. See the MySQL primer post I referenced earlier for tips on this.
John
Thanks John for quick response.I am new to sql and k1000 so please be patient if I am asking silly question. I have uploaded the snapshot of my rule.Can you please have a look on my rule and make my corrections if I have done anything wrong.
I have enabled the rule and test it with some test user but its not sending any email to the user.
Comments:
-
There's a textbox at the bottom of the rule that shows the results of the last time it ran. What does it say about sending an email? Did the resolution get set like your update clause indicates? - chucksteel 12 years ago
-
it just stuck on executing select query.
34:34> Starting: Tue, 17 Jul 2012 22:34:34 +0930
34:34> Executing Select Query...
34:34>
I will try your above suggestions and update you about outcome. - imranqamar34 12 years ago
-
As I copied this code from some other post , I am not sure this part of the code
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,
Specially HD_TICKET.CUSTOM_FIELD_VALUE11 = 'Yes'
Can you please explain what this code is doing and what is custom_field_value11 is ? - imranqamar34 12 years ago -
Your select statement has things in it that shouldn't be there, so it is failing. Like John said, custom_field_value11 is the value of custom field number 12. I would remove that entire statement and also make sure that the where clause for the queue id is the appropriate queue id. Actually, you can probably just eliminate it, as I believe I read somewhere that KACE will automatically add an appropriate where clause when the rule actually runs. - chucksteel 12 years ago