turbot/oci_compliance

Query: identity_tenancy_with_one_active_compartment

Usage

powerpipe query oci_compliance.query.identity_tenancy_with_one_active_compartment

SQL

with compartment_count as (
select
count (compartment_id),
tenant_id,
tenant_name,
_ctx
from
oci_identity_compartment
where
lifecycle_state = 'ACTIVE' and name <> 'ManagedCompartmentForPaaS'
group by
tenant_id,
_ctx,
tenant_name
)
select
a.tenant_id as resource,
case
when a.count > 1 then 'ok'
else 'alarm'
end as status,
case
when a.count > 1 then a.count || ' compartments exist in tenancy.'
else 'No additional compartments exist in tenancy.'
end as reason
, a.tenant_name as tenant
from
compartment_count as a
left join oci_identity_tenancy as b on b.tenant_id = a.tenant_id;

Controls

The query is being used by the following controls: