devices_needing_macsuck.sql

SELECT ip
FROM device
LEFT OUTER JOIN admin ON (device.ip = admin.device
                          AND admin.status = 'queued'
                          AND admin.action = 'macsuck')
FULL OUTER JOIN device_skip ON (device_skip.device = device.ip
                                AND (device_skip.actionset @> string_to_array('macsuck', '')
                                     OR (device_skip.deferrals >= '10'
                                         AND device_skip.last_defer > (LOCALTIMESTAMP - '7 days' ::interval))))
WHERE admin.device IS NULL
  AND device.ip IS NOT NULL
GROUP BY device.ip
HAVING count(device_skip.backend) < (SELECT count(distinct(backend)) FROM device_skip)
ORDER BY device.ip ASC;