/build/static/layout/Breadcrumb_cap_w.png

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!


3 Comments   [ + ] Show comments
  • Try to remove the 0 attached to order. That was the only thing that jumped out without trying the code myself. - Jbr32 10 years ago
  • 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 - Jbr32 10 years ago
  • 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 - SVenesky 10 years ago

Answers (0)

Be the first to answer this question

 
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