We are currently using the following Script to notify our IT department of all new Tickets created.

Select SQL:

SELECT
      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
      C.COMMENT, -- $comment
      C.DESCRIPTION, -- $description
      GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
       H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see the ticket at http://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
       ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
      UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
      UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
      UPDATER.EMAIL AS UPDATER_EMAIL,     -- $updater_email
      IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
      OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
      OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
      OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email
      IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
      SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
      SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
      SUBMITTER.EMAIL AS SUBMITTER_EMAIL,     -- $submitter_email
      P.NAME AS PRIORITY, -- $priority
      S.NAME AS STATUS,   -- $status
      I.NAME AS IMPACT,   -- $impact
      CAT.NAME AS CATEGORY, -- $category
      'Helpdesk email is here' AS NEWTICKETEMAIL -- $newticketemail
    FROM HD_TICKET
    JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
                            AND C.ID=<CHANGE_ID>
      JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
      JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
      JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
      JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
      JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
      LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
      LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
      LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
    WHERE
     C.DESCRIPTION LIKE 'TICKET CREATED%'
          GROUP BY HD_TICKET.ID
    HAVING 1=1

With an email being sent out to each recipient in query results of the following:

Subject:

[TICK:$ticknum] NEW TICKET: $title

Column Containing Email Address:

NEWTICKETEMAIL

Message:

$submitter_fname has opened a ticket.  
The submission was:
Ticket: $ticknum
From: $submitter_fname ($submitter_email)
Category: $category
Priority: $priority
Status: $status
Severity: $impact
Opening Comment: $comment


I'm not very good with scripting, but I was wondering if anyone could shed some light on how to modify this script so that when someone submits a ticket in a certain category, we can send out an email to that specific category email group. We would want this script to work when a new ticket is opened, as well as when a ticket is reassigned to that specific category.


Right now we have a 'Lending' category, where we would like an email notification sent out to the lending email group on ticket creation, as well as on category reassignment.


Thank you

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

1
If your queue configuration includes the group email address in the category configuration under Category CC then you can get that email address by including CAT.CC_LIST in the select portion of your statement. The column containing email address will then be CC_LIST instead of NEWTICKETEMAIL.

To have the email sent for category changes you will need a separate rule that looks for a description like '%Changed ticket Category from%to"Lending"%'

Answered 02/04/2016 by: chucksteel
Red Belt

  • So that would essentially be the following select script:


    SELECT
    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
    C.COMMENT, -- $comment
    C.DESCRIPTION, -- $description
    GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
    H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see the ticket at http://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
    ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
    UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
    UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
    UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
    IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
    OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
    OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
    OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
    IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
    SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
    SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
    SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
    P.NAME AS PRIORITY, -- $priority
    S.NAME AS STATUS, -- $status
    I.NAME AS IMPACT, -- $impact
    CAT.NAME AS CATEGORY, -- $category
    CAT.CC_LIST
    FROM HD_TICKET
    JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
    AND C.ID=<CHANGE_ID>
    JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
    JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
    JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
    JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
    JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
    LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
    LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
    LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
    WHERE
    C.DESCRIPTION LIKE 'TICKET CREATED%'
    GROUP BY HD_TICKET.ID
    HAVING 1=1

    And then change "Column Containing Email Address:" to CC_LIST, correct?
    • It worked. Thank you.
  • In order to get the Category change email, where do I put '%Changed ticket Category from%to"Lending"%'?

    I'm not very experienced with SQL, so I'm at a bit of a loss.
    • This line:
      C.DESCRIPTION LIKE 'TICKET CREATED%'
      becomes this:
      C.DESCRIPTION LIKE '%Changed ticket Category from%to"Lending"%'
      • SELECT
        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
        C.COMMENT, -- $comment
        C.DESCRIPTION, -- $description
        GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
        H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see the ticket at http://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
        ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
        UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
        UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
        UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
        IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
        OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
        OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
        OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
        IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
        SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
        SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
        SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
        P.NAME AS PRIORITY, -- $priority
        S.NAME AS STATUS, -- $status
        I.NAME AS IMPACT, -- $impact
        CAT.NAME AS CATEGORY, -- $category
        CAT.CC_LIST
        FROM HD_TICKET
        JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
        AND C.ID=<CHANGE_ID>
        JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
        JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
        JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
        JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
        JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
        LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
        LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
        LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
        WHERE
        C.DESCRIPTION LIKE '%Changed ticket Category from%to"Lending"%'
        GROUP BY HD_TICKET.ID
        HAVING 1=1

        I duplicated the above Select script and made the description change and still kept the Email each recipient in query results as "CC_LIST", but it's not actually sending out the email on ticket change. In an actual test Ticket that I created, the verbiage of the category change is:

        Changed ticket Category from "Other" to "Lending".
      • It might be a copy/paste or formatting issue but it looks like there isn't a space in to "Lending". That might be my fault from when I pasted it above.
Please log in to comment

Answers

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share