I am trying to filter out locations and devices which are currently under scheduled maintenance from showing up in the alert list. I have managed to cook a query which returns a list of currently active schedules, given a device_id.
(Currently disregarding schedules mapped to a Group and recurring schedules)
The macro documentation indicates that SQL in macros is possible, but does not provide a complete example for this. Anyone care to lend a hand?
SELECT alert_schedule.schedule_id FROM alert_schedule,alert_schedulables WHERE alert_schedule.schedule_id=alert_schedulables.schedule_id AND now() BETWEEN alert_schedule.start AND alert_schedule.end AND ( alert_schedulables.alert_schedulable_type LIKE 'device' AND alert_schedulable_id = ? OR alert_schedulables.alert_schedulable_type LIKE 'location' AND alert_schedulable_id=(SELECT location_id FROM devices WHERE devices.device_id = ?) );