KACE Service desk: Email to customer when ticket Closed (custome field)
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,
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
Answers (1)
Please log in to answer
Posted by:
chucksteel
9 years ago
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.
Comments:
-
I'm not much of a SQL guy, how do you enter this info for our Kace appliance - buffetbruddahs 9 years ago
-
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. - chucksteel 9 years ago
-
Thanks, but question was how to send email to customer e-mail who are writen on custome field. - Janis 9 years ago
-
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. - chucksteel 9 years ago
-
Hi, I use Custome 10 field - Janis 9 years ago
-
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. - chucksteel 9 years ago -
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 - Janis 9 years ago -
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. - chucksteel 9 years ago
-
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" - Janis 9 years ago
