Query: guardduty_no_high_severity_findings

with detectors as (
  select
    d.detector_id,
    d.arn,
    d.title,
    d.region,
    d.account_id,
    d.tags,
    d.status
  from
    aws_guardduty_detector d
    join aws_region r on d.account_id = r.account_id and d.region = r.name
  where
    r.steampipe_available = true
),
finding_count as (
  select
    f.detector_id,
    count(*) as count
  from
    aws_guardduty_finding as f
  group by
    f.detector_id
)
select
  d.arn as resource,
  case
    when d.status <> 'ENABLED' then 'skip'
    when fc.count = 0 or fc.count is null then 'ok'
    else 'alarm'
  end as status,
  case
    when d.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;