/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Custom Ticket Rule

04/11/2015 3850 views
I'm looking to create a custom ticket rule targeting tickets that are overdue (past the due date). How would I target that criteria? I want to add a comment to those tickets so the submitter is notified.
Answer Summary:
2 Comments   [ + ] Show comments

Comments

  • Do you want the notification to happen just once or multiple times?
  • I would like it to run daily...

Answer Chosen by the Author

1
The rules wizard doesn't handle dates well so you'll need to change the SQL query a little bit. Use the wizard to create a rule where Due < today (actually enter the word today to make it easy to find). You can leave the defaults on the second page of the wizard.

On the Ticket Rule Detail page look for the following line in your rule:
HD_TICKET.DUE_DATE < 'today'

You need to change this to read:
DATE(HD_TICKET.DUE_DATE) < DATE(NOW())

This will match tickets where the due date is less than today. In order to match only open tickets you need to add this line also:
AND HD_STATUS.state != "closed"

Uncheck the box to run an update query.

Check the box to add a comment to the ticket. If you are using another rule to notify the user on comments make sure that this rule will run first by value for "Order" to be less than your other rule. You could also use the function to email the user directly with this rule by checking the "Email each recipient..." box. The submitter's email will be in the SUBMITTER_EMAIL column.


Answered 04/14/2015 by: chucksteel
Red Belt

  • Sorry - I'm having trouble getting this to work. The search run with no errors but returns no results when it should. How should I change this line?

    and (( HD_TICKET.DUE_DATE < 'today') and HD_TICKET.HD_QUEUE_ID = 3 )
    • It should read:
      and (DATE(HD_TICKET.DUE_DATE) < DATE(NOW()) and HD_TICKET.HD_QUEUE_ID = 3)
      • Thanks. Think I got it...
      • By deafult when new tickets are created - the due date is entered with all zeros as 0000-00-00 and these are being marked as overdue. I need to filter those out. Can you assist with the code to add? I need a line like HD_TICKET.DUE_DATE not like '0000-00-00%' but my sql is bad so I can't get the syntax right...thanks again.
      • What version of KACE are you running? My appliance is still 6.2 and the due date in the database for all of my tickets is null. Do you have MySQL Workbench setup to look at the HD_TICKETS table and confirm the value in your database?
      • I'm running 6.3.113397. I believe I've got a good select statement now:

        and ((DATE(HD_TICKET.DUE_DATE) < DATE(NOW()) and (HD_TICKET.DUE_DATE not like '0000-00-00%') and HD_TICKET.HD_QUEUE_ID = 3))

        I've been meaning to set up access to the database off the appliance but never got around to doing so. Now that I'm knee deep in service desk and custom rules I need to do so...

Don't be a Stranger!

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

Sign up! or login

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