Skip to content

Instantly share code, notes, and snippets.

@ollyg
Last active March 10, 2023 14:00
Show Gist options
  • Save ollyg/3f241fbe0b29c636a3c83515f6867f64 to your computer and use it in GitHub Desktop.
Save ollyg/3f241fbe0b29c636a3c83515f6867f64 to your computer and use it in GitHub Desktop.
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment