Query: iam_security_audit_role

with security_audit_role_count as(
  select
    'arn:' || a.partition || ':::' || a.account_id as resource,
    count(policy_arn),
    a.account_id,
    a._ctx
  from
    aws_account as a
    left join aws_iam_role as r on r.account_id = a.account_id
    left join jsonb_array_elements_text(attached_policy_arns) as policy_arn on true
  where
    policy_arn = 'arn:aws:iam::aws:policy/SecurityAudit'
  group by
    a.account_id,
    a.partition,
    a._ctx
)
select
  resource,
  case
    when count > 0 then 'ok'
    else 'alarm'
  end as status,
  case
    when count = 1 then 'SecurityAudit policy attached to 1 role.'
    when count > 1 then 'SecurityAudit policy attached to ' || count || ' roles.'
    else 'SecurityAudit policy not attached to any role.'
  end as reason
  , account_id
from
  security_audit_role_count;