/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
Answer this question or Comment on this question for clarity

Answers

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.
Answered 10/28/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
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.
Answered 10/28/2011 by: young020
Black Belt

Please log in to comment
0
That was the wysiwyg editor doing that on my copy/paste. I removed it
Answered 10/28/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment