Custom Ticket Rule
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.
2 Comments
[ + ] Show comments
-
Do you want the notification to happen just once or multiple times? - chucksteel 9 years ago
-
I would like it to run daily... - jegolf 9 years ago
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
9 years ago
Top Answer
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.
Comments:
-
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 ) - jegolf 9 years ago-
It should read:
and (DATE(HD_TICKET.DUE_DATE) < DATE(NOW()) and HD_TICKET.HD_QUEUE_ID = 3) - chucksteel 9 years ago-
Thanks. Think I got it... - jegolf 9 years ago
-
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. - jegolf 9 years ago
-
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? - chucksteel 9 years ago
-
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... - jegolf 9 years ago
