/build/static/layout/Breadcrumb_cap_w.png

Help with a custom rule?

Hi!  I was tasked with creating a custom rule but I really don't know SQL.  I've gotten my rule mostly working but after it sends me a notification through email it will periodically send me false notifications with no information in them.  Any help or guidance would be appreciated.

What I'm trying to is create a rule so that when a specific field is flipped to "Yes" and the ticket is not closed it will notify a specific email address.  It sends the correct notification when the field is set to "Yes" but at random intervals it will send me false notifications with no information in them.  

Here is my SQL:  (I apologize if the formatting is bad)
SELECT
     -- ticket fields
     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
     -- change fields
     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 your ticket at https://kace.company.com/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
     OWNER.FULL_NAME AS 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
     -- 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
     'notification@company.com' AS NEWTICKETEMAIL -- $newticketemail
   FROM HD_TICKET
    /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID AND C.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
       HD_TICKET.CUSTOM_FIELD_VALUE17 = 'Yes' AND S.NAME <> 'Closed' AND HD_TICKET.HD_QUEUE_ID = 1

0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 7 years ago
Red Belt
3

Top Answer

Right now this rule will match any ticket with your custom field = Yes and the status isn't closed, so any time a ticket is saved with those criteria the rule will run. To only trigger the rule when the custom field is changed you need to be more specific.

First you need to add a join statement to the HD_TICKET_CHANGE_FIELD table that will note changes in CUSTOM_FIELD_VALUE17:
JOIN HD__TICKET_CHANGE_FIELD CF on CF.HD_TICKET_CHANGE_ID = <CHANGE_ID> and FIELD_CHANGED = 'CUSTOM_FIELD_VALUE17'
Note that the CHANGE_ID inside the angle brackets is a variable that the K1000 will replace with the ID of the change when the ticket was saved. Add this line to the select statement where the other join statements are located (after FROM HD_TICKET and before WHERE).

Once you have that join in place you can select tickets that have just had the value changed. Remove this:
HD_TICKET.CUSTOM_FIELD_VALUE17 = 'Yes'
and replace it with this:
CF.AFTER_VALUE = 'Yes'
The rule should now only match tickets when the value of your custom field has changed to Yes. Be sure to have the rule set to run on ticket save and you should be all set.



Comments:
  • Thank you for your help. I've updated the sql and continue to receive incorrect notifications. Here is the new SQL.

    SELECT
    -- ticket fields
    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
    -- change fields
    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 your ticket at https://kace.company.com/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
    OWNER.FULL_NAME AS 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
    -- 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
    'notification@company.com' AS NEWTICKETEMAIL -- $newticketemail
    FROM HD_TICKET
    /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
    /* changed field ***/ JOIN HD_TICKET_CHANGE_FIELD CF ON CF.HD_TICKET_CHANGE_ID = <CHANGE_ID> AND FIELD_CHANGED = 'CUSTOM_FIELD_VALUE17'
    /* 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
    HD_TICKET.CUSTOM_FIELD_VALUE17 = 'Yes' AND S.NAME <> 'Closed' AND HD_TICKET.HD_QUEUE_ID = 1 - mad_cow_disease 7 years ago
    • This is almost the same query as before. You didn't make any changes to the where clause. - chucksteel 7 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ