turbot/oci_insights

Query: identity_groups_with_policy

Usage

powerpipe query oci_insights.query.identity_groups_with_policy

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 || '%'
),group_without_policy as (
select
id,
case
when id not in (select distinct group_id from group_with_policy) then 'no policies'
else 'with policies'
end as has_policies
from
oci_identity_group
)
select
has_policies,
count(*)
from
group_without_policy
group by
has_policies;

Dashboards

The query is used in the dashboards: