/build/static/layout/Breadcrumb_cap_w.png

Sending all comments on ticket close email

I am trying to come up with a way of sending all comments to customers on a ticket close email. I was using the $ticket_history so that I would not need a custom rule but this was showing the agents email address. We wanted to eliminate the ability for customers to contact technicians directly.

I modified a rule i seen on kace.com which is:

select
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,
COMMENT
from HD_TICKET_CHANGE
JOIN HD_TICKET ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET.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_CHANGE.USER_ID
and HD_TICKET_CHANGE.DESCRIPTION LIKE '%to "Closed"%'


Using sql workbench this will pull the type of information I would like to put into an email. I have to change the "and HD_TICKET.ID=<CHANGE_ID>" to a actual ticket number because it does not like the <CHANGE_ID> syntax in workbench.

I do not think the task is selecting any data to send out. It is also not sending out an email at all. I have it set to send an email for each result row.

The Subject:SUBJECT:[TICK:$ticknum] NEW TICKET: $title
Email Column:SUBMITTER_EMAIL
Email Body:
------------------------------------------------------------
Test
Ticket Number: $ticknum

Summary: $title

Comment:

$comment
----------------------------------------------------------


Any thoughts or help would be greatly appreciated.

0 Comments   [ + ] Show comments

Answers (3)

Posted by: GillySpy 12 years ago
7th Degree Black Belt
0
Since you would have to do a group by this is a tricky thing to pull off in an "on ticket save" rule, while also remaining an efficient query. This is because a rule that runs every save appends "and HD_TICKET.ID=xyz" to whatever select query you have.

This will work in version 5.x and up.
select HD_TICKET.ID, GROUP_CONCAT(
CONCAT('----- Change by ', IFNULL(UPDATER.EMAIL,'<user deleted>'),' at ',cast(TIMESTAMP as char),' (',IF(VIA_EMAIL<>','via email','),') -----\n',
DESCRIPTION,'\n',COMMENT,'\n\nPlease see your ticket at http://kbox/userui/ticket.php?ID=',cast(C.HD_TICKET_ID as char),'\n') SEPARATOR '\n') HISTORY

FROM HD_TICKET
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID
JOIN (select HD_TICKET_ID from HD_TICKET_CHANGE WHERE ID=<CHANGE_ID>) THISCHANGE
ON THISCHANGE.HD_TICKET_ID=HD_TICKET.ID
JOIN USER UPDATER ON UPDATER.ID=C.USER_ID
WHERE 1=1
GROUP BY HD_TICKET.ID
HAVING 1=1 /* and HD_TICKET.ID = xyz will be added at runtime but is redundant */


Now in your email you can reference the comments as $history. Make sure you add back in your stuff -- this was just a basic example.
Posted by: young020 12 years ago
Black Belt
0
Thank you for the quick reply. This is a bit more difficult than I had though. I am not familiar with the [/link] command in the code. The kbox does not seem to like this syntax.
Posted by: GillySpy 12 years ago
7th Degree Black Belt
0
That was the wysiwyg editor doing that on my copy/paste. I removed it
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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