Query: iam_access_analyzer_enabled_without_findings

with accessanalyzer_findings as (
  select
    a.status as status,
    f.access_analyzer_arn as arn,
    a.region,
    a.account_id,
    a.tags,
    a.name,
    count(*)
  from
    aws_accessanalyzer_analyzer as a
    left join aws_accessanalyzer_finding as f on f.access_analyzer_arn = a.arn
  group by
    f.access_analyzer_arn,
    a.status,
    a.region,
    a.account_id,
    a.tags,
    a.name
)
select
  'arn:' || r.partition || '::' || r.region || ':' || r.account_id as resource,
  case
    -- Skip any regions that are disabled in the account.
    when r.opt_in_status = 'not-opted-in' then 'skip'
    when f.status = 'ACTIVE' and f.arn is null then 'ok'
    when f.status = 'ACTIVE' and f.arn is not null then 'alarm'
    when f.status = 'NOT_AVAILABLE' then 'alarm'
    else 'alarm'
  end as status,
  case
    when r.opt_in_status = 'not-opted-in' then r.region || ' region is disabled.'
    when f.status = 'ACTIVE' and f.arn is null then f.name || ' does not have active findings in region ' || r.region || '.'
    when f.status = 'ACTIVE' and f.arn is not null then f.name || ' has active findings in region ' || r.region || '.'
    when f.status = 'NOT_AVAILABLE' then f.name || ' is not enabled in region ' || r.region || '.'
    else 'IAM Access Analyzer is not active in region ' || r.region || '.'
  end as reason
  
  , r.region, r.account_id
from
  aws_region as r
  left join accessanalyzer_findings as f on f.region = r.region and f.account_id = r.account_id;