We use a ticket rule to send hourly emails listing all new helpdesk tickets. This week we added a second queue to our K1000 for another department, however the ticket rule is now being triggered by tickets in both queues. We've tried adding 'AND HD_TICKET.HD_QUEUE_ID = 1' but it caused an error. Any suggestions would be warmly welcomed :)

CURRENT RULE:

SELECT
T.ID AS TICKNUM,
T.TITLE AS TITLE,
S.NAME AS STATUS,
U.EMAIL AS SUBMITTER_EMAIL,
U.FULL_NAME AS SUBMITTER_FULLNAME,
CONCAT ('http://kbox1.OurCompany.org/adminui/ticket.php?ID=' , T.ID) AS TICKET_LINK

FROM HD_TICKET T JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID) JOIN USER U ON (T.SUBMITTER_ID = U.ID) WHERE HOUR(NOW()) > 6 AND HOUR(NOW()) < 19 AND NOW() > DATE_ADD(T.CREATED, INTERVAL 15 MINUTE) AND S.NAME = 'New'

/******* begin only during business hours ***********/
     /**/and HOUR(NOW())>=7  /* 7am or later kbox time*/
     /**/and CURTIME()<='18:00:00'  /* before 6pm Pacific */
     /**/and DAYNAME(NOW()) NOT IN ('Saturday','Sunday')  /* not the weekend */
     /**/and DAYOFYEAR(NOW()) NOT IN (1,360)   /* not xmas or new year's */
     /**/and NOT(   /*not U.S. thxgiving 2 days */
     /**/   MONTHNAME(CURDATE())='November' and (
     /**/      (DAYNAME(CURDATE())='Thursday' and DAYOFMONTH(CURDATE()) IN (22,23,24,25,26,27,28)) OR
     /**/     (DAYNAME(CURDATE())='Friday' and DAYOFMONTH(CURDATE()) IN (23,24,25,26,27,28,29))
     /**/)  )  and NOT(      /* not U.S. Labor Day */
     /**/   MONTHNAME(CURDATE())='September' and DAYNAME(CURDATE())='Monday' and DAYOFMONTH(CURDATE()) IN (1,2,3,4,5,6,7) )
     /******* end only during business hours ***********/

Answer Summary:
Cancel
2 Comments   [ + ] Show Comments

Comments

  • Have you tried using 'AND T.HD_QUEUE_ID = 1' since you aliased HD_TICKET as T?
  • Worked like a charm. Thank you so much for the assist!
Please log in to comment

Answers

1

Grayematter's comment above answered this question. Thank you!

Have you tried using 'AND T.HD_QUEUE_ID = 1' since you aliased  HD_TICKET as T? - See more at: http://www.itninja.com/question/how-would-we-limit-a-k1000-helpdesk-ticket-email-rule-to-a-single-queue#sthash.p4B77XXH.dpuf
Answered 03/21/2014 by: jvincent
Orange Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share