Query: guardduty_no_high_severity_findings

with detectors as (
  select
    detector_id,
    arn,
    title region,
    account_id,
    status
  from
    aws_guardduty_detector
),
finding_count as (
  select
    f.detector_id,
    count(*) as count
  from
    aws_guardduty_finding as f
  group by
    f.detector_id
)
select
  arn as resource,
  case
    when status <> 'ENABLED' then 'skip'
    when fc.count = 0
    or fc.count is NULL then 'ok'
    else 'alarm'
  end as status,
  case
    when status <> 'ENABLED' then d.detector_id || ' is disabled.'
    when fc.count = 0
    or fc.count is NULL then d.detector_id || ' is enabled and does not have high severity findings.'
    else d.detector_id || ' is enabled and has ' || fc.count || ' high severity findings.'
  end as reason --
  --, d.region, d.account_id
from
  detectors as d
  left join finding_count as fc on fc.detector_id = d.detector_id;