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.


5rfpaQ.png



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!

 

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
I think that your select statement may be overly complicated but you may have reasons for that. I believe your problem is that you have the last line commented out. That line is was matches tickets where the owner has been changed so without it the rule is probably matching on any changes to the ticket.

Answered 09/18/2015 by: chucksteel
Red Belt

  • The last line that is commented does do what you say. However, our setup has it so that our IT people are in 'All Ticket Owners' while all the other users are not. When the last line is enabled, when we send tickets to other IT people they will not be notified. If the last line is disabled, then IT and others will receive the update. Its strange.
Please log in to comment
Answer this question or Comment on this question for clarity