steampipe plugin install oci

Table: oci_identity_compartment - Query OCI Identity Compartments using SQL

Oracle Cloud Infrastructure Identity and Access Management (IAM) service lets you control who has access to your cloud resources. You can control what type of access a group of users have and to which specific resources. This is achieved through the use of compartments, which are a fundamental component of Oracle Cloud Infrastructure for organizing and isolating your cloud resources.

Table Usage Guide

The oci_identity_compartment table provides insights into compartments within Oracle Cloud Infrastructure Identity and Access Management (IAM). As a cloud architect or administrator, you can explore compartment-specific details through this table, including compartment names, descriptions, and states. Utilize it to manage and understand your OCI resource organization, such as identifying compartments with specific resources, understanding your compartment hierarchy, and ensuring appropriate resource isolation.

Examples

Basic info

Discover the segments that are in different lifecycle states within your OCI Identity Compartments. This could be useful in assessing the elements within your compartments and understanding their statuses for better resource management.

select
name,
lifecycle_state,
id,
compartment_id as parent_compartment
from
oci.oci_identity_compartment
select
name,
lifecycle_state,
id,
compartment_id as parent_compartment
from
oci_identity_compartment

Details joined with parent compartment

Analyze the settings to understand the relationship between different compartments within an OCI identity compartment hierarchy. This is useful for understanding the structure and dependencies within your OCI environment.

select
child.name as name,
coalesce(parent.name, 'root') as parent_compartment,
child.id as id,
coalesce(parent.id, child.tenant_id) as parent_compartment_id
from
oci_identity_compartment child
left join oci_identity_compartment parent on (child.compartment_id = parent.id)
order by
parent.name;
select
child.name as name,
coalesce(parent.name, 'root') as parent_compartment,
child.id as id,
coalesce(parent.id, child.tenant_id) as parent_compartment_id
from
oci_identity_compartment as child
left join oci_identity_compartment as parent on child.compartment_id = parent.id
order by
parent.name;

Full path of the compartments

This query is useful in tracking the full path of active compartments within a system. It aids in system organization and management by allowing users to understand the hierarchical structure of compartments, thereby facilitating easier navigation and data retrieval.

with recursive compartments as (
select
name,
id,
compartment_id,
tenant_id,
name as path,
name as last_name,
id as last_id
from
oci_identity_compartment
where
lifecycle_state = 'ACTIVE'
union all
select
oci_identity_compartment.name,
oci_identity_compartment.id,
oci_identity_compartment.compartment_id,
oci_identity_compartment.tenant_id,
oci_identity_compartment.name || '/' || compartments.path,
compartments.last_name,
compartments.last_id
from
oci_identity_compartment
join compartments on oci_identity_compartment.id = compartments.compartment_id
)
select
last_id as compartment_id,
last_name as name,
path
from
compartments
where
compartment_id = tenant_id
order by
path;
with recursive compartments as (
select
name,
id,
compartment_id,
tenant_id,
name as path,
name as last_name,
id as last_id
from
oci_identity_compartment
where
lifecycle_state = 'ACTIVE'
union all
select
oci_identity_compartment.name,
oci_identity_compartment.id,
oci_identity_compartment.compartment_id,
oci_identity_compartment.tenant_id,
oci_identity_compartment.name || '/' || compartments.path,
compartments.last_name,
compartments.last_id
from
oci_identity_compartment
join compartments on oci_identity_compartment.id = compartments.compartment_id
)
select
last_id as compartment_id,
last_name as name,
path
from
compartments
where
compartment_id = tenant_id
order by
path;

Query examples

Control examples

Schema for oci_identity_compartment

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
compartment_idtextThe OCID of the compartment in Tenant in which the resource is located.
defined_tagsjsonbDefined tags for resource. Defined tags are set up in your tenancy by an administrator. Only users granted permission to work with the defined tags can apply them to resources.
descriptiontextThe description you assign to the compartment.
freeform_tagsjsonbFree-form tags for resource. This tags can be applied by any user with permissions on the resource.
idtext=The OCID of the compartment.
inactive_statusbigintThe detailed status of INACTIVE lifecycleState
is_accessiblebooleanIndicates whether or not the compartment is accessible for the user making the request.
lifecycle_statetext=The compartment's current state.
nametext=The name assigned to the compartment during creation
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
tagsjsonbA map of tags for the resource.
tenant_idtext=, !=, ~~, ~~*, !~~, !~~*The OCID of the Tenant in which the resource is located.
tenant_nametextThe name of the Tenant in which the resource is located.
time_createdtimestamp with time zoneDate and time the user was created.
titletextTitle of the resource.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- oci

You can pass the configuration to the command with the --config argument:

steampipe_export_oci --config '<your_config>' oci_identity_compartment