Analytics & Reporting

-- Find resources with high-risk violations in the last 30 days
SELECT
  r.resource_id,
  r.resource_type,
  r.account_id,
  p.policy_name,
  v.severity,
  v.violation_date
FROM resources r
JOIN violations v ON r.resource_id = v.resource_id
JOIN policies p ON v.policy_id = p.policy_id
WHERE
  v.severity IN ('HIGH', 'CRITICAL')
  AND v.violation_date >= NOW() - INTERVAL 30 DAY
  AND v.status = 'OPEN'
ORDER BY v.violation_date DESC
LIMIT 100;