turbot/oci_insights

Query: identity_groups_without_policies_count

Usage

powerpipe query oci_insights.query.identity_groups_without_policies_count

SQL

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);

Dashboards

The query is used in the dashboards: