/build/static/layout/Breadcrumb_cap_w.png

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

Asked 11 years ago 4251 views
0 Comments   [ + ] Show comments

Answers (5)

Posted by: jverbosk 11 years ago
Red Belt
3

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


Posted by: chucksteel 11 years ago
Red Belt
2
I would recommend adding the following join statement:
 
JOIN HD_TICKET_CHANGE_FIELD ON HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID=&lt;CHANGE_ID%gt; and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED='STATUS_NAME'
 
(this goes above the where clause, along with the other JOIN lines)
 
and then changing this:
 
and (((  HD_STATUS.NAME = 'Need More Info') AND HD_TICKET.RESOLUTION != 'Email Sent') and HD_TICKET.HD_QUEUE_ID = 8 )
 
to the following:
 
and HD_TICKET_CHANGE_FIELD.AFTER_VALUE = 'Need More Info'
and HD_TICKET.HD_QUEUE_ID = 8
 
Then have the rule set to run on save. The advantage is that the email is sent as soon as the ticket is saved and you don't have to set a value in the resolution to keep track of whether the email has been sent. The rule only matches if the status is changed to Need More Info, so the email should only be sent once. You may also want to check the box to add a comment to the ticket indicating that the email was sent. This will give another indication to the technician that the rule fired and contacted the user. 
Posted by: jverbosk 11 years ago
Red Belt
2

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

Posted by: imranqamar34 11 years ago
Senior Yellow Belt
1

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.

Posted by: imranqamar34 11 years ago
Senior Yellow Belt
1

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 11 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 11 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 11 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 11 years ago
 
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