Creating a rule for unassigned ticket reminder
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.
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
Answers (1)
Please log in to answer
Posted by:
chucksteel
6 years ago
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.
