Custom ticket rules for unassigned tickets
Greetings...
Rather than opening a thread that has been inactive for awhile I thought that it would be best to open a new one. I am trying to create a custom ticket rule to notify management of any tickets that have been submitted but are unassigned. Basically, looking for a notification that a new ticket has been entered in the queue. Searching the threads on this site there are examples of how to do this so basically I followed the directions to this link: http://kace.uservoice.com/forums/82699-k1000/suggestions/2868303-email-group-on-unassigned-ticket-creation
Here is the code that I used:
Select Query:Select 'xxxxx@xxxxxx.net' as SUPPORT, T.ID as ID, T.TITLE as Issue,S.FULL_NAME as Submitter, T.CREATED as Created FROM HD_TICKET TJOIN USER S ON (S.ID = T.SUBMITTER_ID) WHERE T.CREATED < SUBDATE(NOW(), INTERVAL 1 HOUR)AND T.OWNER_ID = 0ORDER BY T.ID ASC
or this link: http://www.kace.com/support/resources/kb/solutiondetail?sol=SOL111222&action=artikel&cat=8&id=613&artlang=en
When I test the test the results via view ticket search results I see SQL Statement errors:View Ticket Search Results
mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USER S ON (S.ID = T.SUBMITTER_ID) WHERE T.CREATED < SUBDATE(NOW(), INTERVAL 1 HO' at line 1] in EXECUTE("SELECT COUNT(*) FROM HD_TICKET TJOIN USER S ON (S.ID = T.SUBMITTER_ID) WHERE T.CREATED < SUBDATE(NOW(), INTERVAL 1 HOUR)AND T.OWNER_ID = 0ORDER BY T.ID ASC and HD_TICKET.HD_QUEUE_ID = '4' LIMIT 1")
Can anyone help me out?
Thanks!
Answers (0)
Be the first to answer this question
SELECT 'xxxxx@xxxxxx.net' 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 TIMESTAMPDIFF( HOUR , T.CREATED, NOW( ) ) <1
ORDER BY T.ID ASC - Jbr32 10 years ago
mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and HD_TICKET.HD_QUEUE_ID = '4' ORDER BY SORT_OVERDUE_STATUS, STATUS_ORDINAL, PR' at line 5] in EXECUTE("SELECT 'xxxx@xxxx.net' 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 TIMESTAMPDIFF( HOUR , T.CREATED, NOW( ) ) <1 ORDER BY T.ID ASC and HD_TICKET.HD_QUEUE_ID = '4' ORDER BY SORT_OVERDUE_STATUS, STATUS_ORDINAL, PRIORITY_NUMBER, SORT_OWNER_NAME, TITLE LIMIT 0,10")
The k1000 server version is 5.5.90547 - SVenesky 10 years ago