Query: identity_groups_without_policies_count

with group_with_policy as (
  select
    distinct g.name as group_name,
    g.id as group_id
  from
    oci_identity_policy as p,
    jsonb_array_elements_text(statements) as s,
    oci_identity_group as g
  where
    s ilike '%' || g.name || '%'
)
select
  count(*) as value,
  'Without policies' as label,
  case when count(*) = 0 then 'ok' else 'alert' end as type
from
  oci_identity_group
where
  id not in (select distinct group_id from group_with_policy);