Including "Comments" when alerting HelpDesk of new ticket
Hellow,
I've got a ticket rule which will email the helpdesk distribution list whenever a new ticket is created. I'm stuck because I can't include the "Comment" when the email goes out. I know its because I don't a Comment column defined in my query but I'm not sure how to pull one out from HD_TICKET_CHANGE table. Below is my current query:
SELECT
-- ticket fields
HD_TICKET.ID, -- $id
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_TICKET.TITLE, -- $title
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
-- about the submitter
SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
-- about priority
P.NAME AS PRIORITY, -- $priority
-- about status
S.NAME AS STATUS, -- $status
-- about impact
I.NAME AS IMPACT, -- $impact
-- about category
CAT.NAME AS CATEGORY, -- $category
-- other fields
-- -- example of static distribution list
'my@email.com' AS NEWTICKETEMAIL -- $newticketemail
FROM HD_TICKET
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
AND C.ID=<CHANGE_ID>
/* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
/* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
/* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
/* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
/* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
/* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
/* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
/* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
WHERE
C.DESCRIPTION LIKE 'TICKET CREATED%'
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1
Answers (2)
The comment is HD_TICKET_CHANGE.COMMENT or HD_TICKET_CHANGE.COMMENT_LOC is the localized version (5.3 L10n sp1 and up) but should be the same.
so in the query above you could change:
/* first part of query */ from HD_TICKET /* rest of the query */
to
/* first part of query */ , C.COMMENT /* do not forget the comma */ from HD_TICKET /* rest of the query */
Then in your "send an email for each result row" email you could use $comment to represent what was in the comment field for the change that triggered this rule
Comments:
-
Thanks!!!
I'm actually running into another issue with this. It seems to be firing off emails fine but once I change the email to a distribution list the emails stop firing. It looks like its leaving the appliance but I never get them in my Outlook. I'm thinking of just hardcoding the email addresses of who need to be notified. - glummeee 11 years ago
I added
C.COMMENT, -- $comment
between:
-- other fields and
-- -- example of static distribution list
And presto