KACE Product Support

Hello fellow KACE enthusiasts!
My name is Maxwell and I am a KACE administrator where I work (how lucky, I know) and I had question about a custom ticket rule that isn't working correctly. I will explain how it is setup, how it currently works, and how we want it to work. Then, maybe then, we can fix this issue!

The ticket is intended to email the new assignee if there is a new assignee for a given ticket. The ticket rule is setup to run a select query, email each recipient in the query results, and set to fire on Ticket Save. See picture below for visual representation.

To verify: There are no Email on Events (built-in KACE notifications) enabled.

What is happening is every time someone comments on a given ticket the New Assigned rule will fire. We also have another custom ticket rule to email on comment, which works fine. Meaning that if someone comments on a ticket two emails will be send. Thankfully the formatting between each rule is slightly different so I was easily able to identify that the New Assigned rule was to blame.

Now that is out of the way here is the SQL code for the rule in question:

Select Query:

SELECT HD_TICKET.ID AS TICKET_NUM,
       HD_TICKET.TITLE AS TICKET_TITLE,
       HD_CATEGORY.NAME AS TICKET_CAT,
       HD_PRIORITY.NAME AS TICKET_PRIORITY,
       FC.COMMENT as FIRST_COMMENT,
       LC.COMMENT as LAST_COMMENT,
case when CHANGE_USER = HD_TICKET.OWNER_ID then "helpdesktest@cianbro.com"
        else CONCAT(OWN_USER.EMAIL,",helpdesktest@cianbro.com") end AS OWNER_EMAIL,
      --  "mmala@cianbro.com" as OWNER_EMAIL,
       OWN_USER.FULL_NAME AS OWNER_NAME,
     --  HD_TICKET.OWNER_ID AS OWNER_NUM,
       SUB_USER.FULL_NAME AS SUB_NAME,
       SUB_USER.EMAIL AS SUB_EMAIL,
       SUB_USER.LOCATION AS SUB_LOC,
       SUB_USER.WORK_PHONE AS SUB_WORKPHONE,
       SUB_USER.MOBILE_PHONE AS SUB_MOBILE,
       SUB_USER.CUSTOM_2 AS SUB_TMID
  FROM    
  
                                            
                                              ORG1.HD_TICKET HD_TICKET
                                     
                                       INNER JOIN
                                          ORG1.USER SUB_USER
                                       ON (SUB_USER.ID =
                                              HD_TICKET.SUBMITTER_ID)
                                   INNER JOIN
                                      ORG1.USER OWN_USER
                                   ON (OWN_USER.ID = HD_TICKET.OWNER_ID)
                               INNER JOIN
                                  ORG1.HD_CATEGORY HD_CATEGORY
                               ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
                    
       INNER JOIN
          ORG1.HD_PRIORITY HD_PRIORITY
       ON (HD_TICKET.HD_PRIORITY_ID = HD_PRIORITY.ID)
     inner join  
       (  select TICKET_NUM, HTC_1.COMMENT from
                        ( SELECT min(HTC.ID) AS CHANGE_ID_NUM,
                                 
                                  HTC.HD_TICKET_ID as TICKET_NUM
                                 
                                 FROM ORG1.HD_TICKET_CHANGE HTC                                
                       
                               GROUP BY HTC.HD_TICKET_ID
                           
                                ) minid
                            inner join ORG1.HD_TICKET_CHANGE HTC_1
                                  ON HTC_1.ID= minid.CHANGE_ID_NUM 
                                  ) FC
                                  
             ON FC.TICKET_NUM = HD_TICKET.ID
             
            
             
    inner join (     select TICKET_NUM, HTC_1.COMMENT, HTC_1.OWNERS_ONLY_DESCRIPTION from
                        ( SELECT max(HTC.ID) AS CHANGE_ID_NUM,
                                 
                                  HTC.HD_TICKET_ID as TICKET_NUM
                                 
                                 FROM ORG1.HD_TICKET_CHANGE HTC                                
                       
                                  inner join ORG1.HD_TICKET_CHANGE_FIELD HTCF on HTC.ID = HTCF.HD_TICKET_CHANGE_ID and HTCF.FIELD_CHANGED= "COMMENT"
                               
                               GROUP BY HTC.HD_TICKET_ID
                           
                                ) maxid
                            inner join ORG1.HD_TICKET_CHANGE HTC_1
                                  ON HTC_1.ID= maxid.CHANGE_ID_NUM 
                                  where  COMMENT NOT LIKE '%Machine % was deleted%'
                                  ) LC
       ON LC.TICKET_NUM = HD_TICKET.ID
       
       
       inner join (select TICKET_NUM, HTC_1.OWNERS_ONLY_DESCRIPTION, HTC_1.USER_ID as CHANGE_USER from
                        ( SELECT max(HTC.ID) AS CHANGE_ID_NUM,
                                 
                                  HTC.HD_TICKET_ID as TICKET_NUM
                                 
                                 FROM ORG1.HD_TICKET_CHANGE HTC                                
                       
                                                           
                               GROUP BY HTC.HD_TICKET_ID
                           
                                ) maxid1
                            inner join ORG1.HD_TICKET_CHANGE HTC_1
                                  ON HTC_1.ID= maxid1.CHANGE_ID_NUM
                                ) OC
                                ON OC.TICKET_NUM = HD_TICKET.ID
     and OC.CHANGE_USER <> HD_TICKET.OWNER_ID
    --   and OC.OWNERS_ONLY_DESCRIPTION like "%Changed ticket Owner%"

Email Each in Query:

Ticket $ticket_num has been assigned to $owner_name
Ticket Information:
Title: $ticket_title
Priority: $ticket_priority
Catagory: $ticket_cat
Submitters Information
Name: $sub_name
Email: $sub_email
Location: $sub_loc
Work Number: $sub_workphone
Cell Number: $sub_mobile
Team Member ID: $sub_tmid
Additional Notes:
$last_comment
Incident:
$first_comment

If you think you may need to look at our SQL code for the comment rule let me know and I will post that too. Thank you everyone for all the help!