SQL による特権アクションの監査

SELECT
  action,
  COUNT(*) AS actions,
  SUM(CASE WHEN outcome <> 'success' THEN 1 ELSE 0 END) AS failed_actions,
  SUM(CASE WHEN requires_review THEN 1 ELSE 0 END) AS review_required,
  100.0 * SUM(CASE WHEN outcome <> 'success' THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS failure_rate_pct
FROM security_audit_events
WHERE timestamp_utc >= now() - INTERVAL '30 days'
  AND environment = 'production'
GROUP BY action
ORDER BY failed_actions DESC, failure_rate_pct DESC, action;