Query: iam_subscription_owner_max_3

with owner_roles as (
  select
    d.role_name,
    d.role_type,
    d.name,
    d.title,
    d._ctx,
    d.subscription_id
  from
    azure_role_definition as d
    join azure_role_assignment as a on d.id = a.role_definition_id
  where
    d.role_name = 'Owner'
)
select
  owner.subscription_id as resource,
  case
    when count(*) <= 3 then 'ok'
    else 'alarm'
  end as status,
  count(*) || ' owner(s) associated.' as reason
  
  , sub.display_name as subscription
from
  owner_roles as owner,
  azure_subscription as sub
where
  sub.subscription_id =owner.subscription_id
group by
  owner.subscription_id,
  owner._ctx,
  sub.display_name;