Query: wafv2_web_acl_rule_attached

with rule_group_count as (
  select
    arn,
    count(*) as rule_group_count
  from
    aws_wafv2_web_acl,
    jsonb_array_elements(rules) as r
  where
    r -> 'Statement' -> 'RuleGroupReferenceStatement' ->> 'ARN' is not null
  group by
    arn
)
select
  a.arn as resource,
  case
    when rules is null or jsonb_array_length(rules) = 0 then 'alarm'
    else 'ok'
  end as status,
  case
    when rules is null or jsonb_array_length(rules) = 0 then title || ' has no attached rules.'
    else title || ' has ' || c.rule_group_count || ' rule group(s) and ' || (jsonb_array_length(rules) - c.rule_group_count) || ' rule(s) attached.'
  end as reason
  
  , region, account_id
from
  aws_wafv2_web_acl as a
  left join rule_group_count as c on c.arn = a.arn;