/bundles/itninjaweb/img/Breadcrumb_cap_w.png

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

0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Community Chosen Answer


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

Answered 01/23/2013 by: GillySpy
Seventh Degree Black Belt

  • 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.
Please log in to comment
Answer this question or Comment on this question for clarity

Answers

0

I added

C.COMMENT, -- $comment

between:

-- other fields and

-- -- example of static distribution list

And presto

Answered 01/23/2013 by: glummeee
Senior White Belt

Please log in to comment