Hi,
I would like to send customize email when ticket is closed.
Ticket closed send email to customer (Custome field). I cant use already predefinited events because the customer each time will be diferent on will not be in my user list.
I think it simmple but I'am not SQL guy.
Thanks,
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1
Here is the select statement for our "Send email to submitter when ticket closed" rule:

select HD_TICKET.*, 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,
DATE_FORMAT(HD_TICKET.TIME_CLOSED,'%b %d %Y %I:%i:%s %p') as CLOSED,
                        HD_STATUS.NAME AS STATUS_NAME,
                        HD_STATUS.ORDINAL as STATUS_ORDINAL,
                        HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
                        HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
                        HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
                        STATE, 
                        if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
                        if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED, 
                        if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED, 
                        if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED, 
                        if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
                        if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED, 
                        if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
                        if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
                        case upper(STATE) 
                        when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED) 
                        when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED) 
                        else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
                        if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
                        U1.FULL_NAME as OWNER_FULLNAME,
                        U1.EMAIL as OWNER_EMAIL,
                        if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
                        if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
                        U2.FULL_NAME as SUBMITTER_FULLNAME,
                        U2.EMAIL as SUBMITTER_EMAIL,
                        if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
                        if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
                        if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
                        Q.NAME as QUEUE_NAME,
INITIAL_CHANGE.COMMENT as INITIAL_COMMENT                        
                        from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
JOIN HD_TICKET_CHANGE_FIELD ON HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID=<CHANGE_ID>
 and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED='STATUS_NAME'
JOIN HD_TICKET_CHANGE INITIAL_CHANGE ON INITIAL_CHANGE.HD_TICKET_ID = HD_TICKET.ID
 and INITIAL_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.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
                        LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
                        LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_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_TICKET_CHANGE_FIELD.AFTER_VALUE = 'Closed') and HD_TICKET.HD_QUEUE_ID = 2 and U2.EMAIL != 'helpdesk@company.com')

You will have to change the HD_TICKET.HD_QUEUE_ID = 2 value to match the queue ID for your ticket queue. This rule also excludes any tickets where the submitter is helpdesk@company.com to prevent it from emailing the helpdesk when tickets are closed with the helpdesk as the user.

Any of the columns selected in the select portion of the statement are available as variables in the email being sent to the user. For instance, the title of the ticket is $title, the ID is $id, etc. The submitter's email address will be returned as SUBMITTER_EMAIL and that is what you should use in the "Column containing email address field" value for the rule.



Answered 08/13/2014 by: chucksteel
Red Belt

  • I'm not much of a SQL guy, how do you enter this info for our Kace appliance
    • The best way to use a custom rule is to use the rules wizard to create a rule. Once the rule is created edit the rule and enter the custom SQL query in the select statement portion of the rule.
  • Thanks, but question was how to send email to customer e-mail who are writen on custome field.
    • Ah, sorry I missed that. In order to modify the rule to include a custom field I need to know which custom field you're using to store the email address.
      • Hi, I use Custome 10 field
      • Because of the way the database is setup custom field 10 in the web interface is stored as CUSTOM_FIELD_VALUE9 (the database columns start at 0, instead of 1). Since the select statement I posted earlier includes all of the fields from the HD_TICKET table (the HD_TICKET.* does that) this field is already being pulled by the statement and can be used by other parts of the rule.

        To have the email send to the address in custom field 10, enter CUSTOM_FIELD_VALUE9 in the Column containing email addresses field.
      • Are it possible that you change and send me changed code, i will be very thankful to you.
        Sorry but don't know SQL. but seems i need to start to learn but for now i don't have enough time
      • I'm saying that you don't need to change the query that I posted originally, it will work for the custom field you are using.
      • I have error on this:
        "mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax"
Please log in to comment

Answers

This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

Answer this question or Comment on this question for clarity