Systems Management

I am trying to write a custom inventory rule (CTR) that notifies our IT Team of any change on a ticket when for our Change Management Queue.  However, we have a custom field value labeled Team Notify that is checked to send an email and unchecked to prevent an email from going out.  This is desired so that small changes can be made on the Request for Change (RFC) without spamming our whole team.

My issue is that I am not getting the results I expect when running the rule.  Should be getting several different changes (see below):

But instead, I only get a few and they are not the latest change:

Here is my code:

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

 SELECT

      -- ticket fields

      HD_TICKET.ID, -- $id

      HD_TICKET.ID AS TICKNUM, -- $ticknum

      HD_TICKET.TITLE, -- $title

      HD_TICKET.CUSTOM_FIELD_VALUE0 AS AFFECTED_SYSTEMS, -- $affected_systems

      DATE_FORMAT(HD_TICKET.CUSTOM_FIELD_VALUE1,'%b %d %Y') AS EFFECTIVE_DATE, -- $effective_date

      DATE_FORMAT(HD_TICKET.CUSTOM_FIELD_VALUE2,'%b %d %Y') AS FOLLOW_UP, -- $follow_up

      HD_TICKET.CUSTOM_FIELD_VALUE3 AS CHANGE_REASON, -- $change_reason

      IF(HD_TICKET.CUSTOM_FIELD_VALUE6=1, "Yes", "No") AS TEAM_NOTIFY, -- $team_notify

      HD_TICKET.HTML_SUMMARY, -- $html_summary

      HD_TICKET.CC_LIST AS CC_LIST, -- $cc_list

      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

      -- change fields

      C.COMMENT, -- $comment

      C.DESCRIPTION, -- $description

      C.TIMESTAMP, -- $timestamp

      GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',

       H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://usckace1000/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')

       ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history

      -- about the updater

      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

      -- about the owner

      OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname

      IFNULL(OWNER.FULL_NAME, 'Unassigned') OWNER_FNAME, -- $owner_fname

      OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email

      IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user

      -- 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

      SUBMITTER.MOBILE_PHONE AS SUBMITTER_MOBILE, -- $submitter_mobile

      SUBMITTER.WORK_PHONE AS SUBMITTER_WORK, -- $submitter_work

      -- 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

      'IT-ChangeAlerts@Domain.com' AS NEWTICKETEMAIL -- $newticketemail

    FROM HD_TICKET

     /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.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 NOT LIKE '%TICKET CREATED%'

     AND S.NAME != 'Completed'

     AND S.NAME != 'Cancelled'

     AND S.NAME != 'Needs Action'

     AND HD_TICKET.CUSTOM_FIELD_VALUE6=1

      /* this is necessary when using group by functions */

    GROUP BY HD_TICKET.ID

    HAVING 1=1

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

Any ideas?

Thanks - Cloud