/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


Unassigned notification after 2 hours, but only during work hours/days.

10/06/2017 680 views
I'm trying to make a cutom rule (I am not a sql guru) that notifies our department if a ticket has sat for more than two hours. I've got that piece working, but i only want it to notify us during work hours and days.

Anyone have some insight on why the business hours piece isn't working?

Here is my rule;

Select T.ID as 'Ticket 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 2 HOUR)
AND (HOUR(NOW()) > 06 OR HOUR(NOW()) < 18) AND HD_QUEUE_ID = 5
AND DAYOFWEEK(NOW()) not in (1,7)
AND T.OWNER_ID = 0
AND HD_STATUS_ID = 33
ORDER BY T.ID ASC
1 Comment   [ + ] Show comment

Comments

  • Try using
    (HOUR(NOW()) BETWEEN 05 and 19) instead of
    (HOUR(NOW()) > 06 OR HOUR(NOW()) < 18)

    If you use the view ticket search results link do you get any results?

    What is your schedule for running the rule?
    • Thanks for the comment.

      This is scheduled to run every hour.

      I get the following result when I run the rule with the above changes.

      10/09/2017 15:20:58> Starting: 10/09/2017 15:20:58 10/09/2017 15:20:58> Executing Select Query... 10/09/2017 15:20:58> 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 '(HOUR(NOW()) BETWEEN 05 and 19) AND HD_QUEUE_ID = 5 AND DAYOFWEEK(NOW()) not in ' at line 4] in EXECUTE("Select T.ID as 'Ticket 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 2 HOUR) (HOUR(NOW()) BETWEEN 05 and 19) AND HD_QUEUE_ID = 5 AND DAYOFWEEK(NOW()) not in (1,7) AND T.OWNER_ID = 0 AND HD_STATUS_ID = 33 ORDER BY T.ID ASC")
      • You dropped an AND.
        SUBDATE(NOW(), INTERVAL 2 HOUR) (HOUR(NOW()...

        Should be
        SUBDATE(NOW(), INTERVAL 2 HOUR) AND (HOUR(NOW()...
  • This content is currently hidden from public view.
    Reason: Removed by member request For more information, visit our FAQ's.

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