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   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

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

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
Answer this question or Comment on this question for clarity

Share