Scripting

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