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:
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.
Any thoughts or help would be greatly appreciated.
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)
Please log in to answer
Posted by:
GillySpy
13 years ago
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.
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.
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
13 years ago
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
13 years ago
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.