/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Creating a rule for unassigned ticket reminder

11/07/2017 827 views
We are currently running KACE K1000 v7.1.149 and I am trying to create a rule for a reminder for unassigned tickets that have been sitting for more than an hour.  I've found some SQL queries from this site, but I think it was for an older version of firmware.  Because when I try to run the following it returns with a syntax error.  I'm a SQL novice, so any help would be appreciated.  Thank you.

Select 'it@company.com' as SUPPORT, T.ID as ID, T.TITLE as Issue,
S.FULL_NAME as Submitter, T.CREATED as Created 
FROM HD_TICKET T
JOIN USER S ON (S.ID = T.SUBMITTER_ID) 
WHERE T.CREATED < SUBDATE(NOW(), INTERVAL 1 HOUR)
AND T.OWNER_ID = 0
ORDER BY T.ID ASC



And the error I get:

There were syntax errors in your query.

mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'and HD_TICKET.HD_QUEUE_ID = '8'' at line 7] in EXECUTE("Select 'it@company.com' as SUPPORT, T.ID as ID, T.TITLE as Issue, S.FULL_NAME as Submitter, T.CREATED as Created FROM HD_TICKET T JOIN USER S ON (S.ID = T.SUBMITTER_ID) WHERE T.CREATED < SUBDATE(NOW(), INTERVAL 1 HOUR) AND T.OWNER_ID = 0 ORDER BY T.ID ASC and HD_TICKET.HD_QUEUE_ID = '8' ")

0 Comments   [ + ] Show comments

Comments


All Answers

0
When the rule runs the appliance adds a line to limit the results to a specific queue (the queue where the rule is saved). It appends "and HD_TICKET.HD_QUEUE_ID = 'queue id' to the end of your query. Normally, queries generated by the wizard do not include an order by statements, so this isn't a problem, but in your case your statement has one, and when the appliance tries to add something after that, things go bad.

There isn't really any reason to order the results from the ticket rule, since I'm assuming an email will be sent for each ticket, so you can remove the order by clause from your query. If, however, you do want the order by statement included, you can add the "and HD_TICKET.HD_QUEUE_ID = '8'" after the "AND T.OWNER_ID = 0 " and you should be fine.

Answered 11/08/2017 by: chucksteel
Red Belt

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