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!

3 Comments   [ + ] Show Comments

Comments

  • Try to remove the 0 attached to order. That was the only thing that jumped out without trying the code myself.
  • The code above had a ton of errors, try this:


    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
  • Thanks for the help but an getting an errors with the code.

    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
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity