steampipe plugin install ocisteampipe plugin install oci

oci_adm_knowledge_base oci_adm_vulnerability_audit oci_ai_anomaly_detection_ai_private_endpoint oci_ai_anomaly_detection_data_asset oci_ai_anomaly_detection_model oci_ai_anomaly_detection_project oci_analytics_instance oci_apigateway_api oci_application_migration_migration oci_application_migration_source oci_artifacts_container_image oci_artifacts_container_image_signature oci_artifacts_container_repository oci_artifacts_generic_artifact oci_artifacts_repository oci_autoscaling_auto_scaling_configuration oci_autoscaling_auto_scaling_policy oci_bastion_bastion oci_bastion_session oci_bds_bds_instance oci_budget_alert_rule oci_budget_budget oci_certificates_authority_bundle oci_certificates_management_association oci_certificates_management_ca_bundle oci_certificates_management_certificate oci_certificates_management_certificate_authority oci_certificates_management_certificate_authority_version oci_certificates_management_certificate_version oci_cloud_guard_configuration oci_cloud_guard_detector_recipe oci_cloud_guard_managed_list oci_cloud_guard_responder_recipe oci_cloud_guard_target oci_container_instances_container oci_container_instances_container_instance oci_containerengine_cluster oci_core_block_volume_replica oci_core_boot_volume oci_core_boot_volume_attachment oci_core_boot_volume_backup oci_core_boot_volume_metric_read_ops oci_core_boot_volume_metric_read_ops_daily oci_core_boot_volume_metric_read_ops_hourly oci_core_boot_volume_metric_write_ops oci_core_boot_volume_metric_write_ops_daily oci_core_boot_volume_metric_write_ops_hourly oci_core_boot_volume_replica oci_core_cluster_network oci_core_dhcp_options oci_core_drg oci_core_image oci_core_image_custom oci_core_instance oci_core_instance_configuration oci_core_instance_metric_cpu_utilization oci_core_instance_metric_cpu_utilization_daily oci_core_instance_metric_cpu_utilization_hourly oci_core_internet_gateway oci_core_load_balancer oci_core_local_peering_gateway oci_core_nat_gateway oci_core_network_load_balancer oci_core_network_security_group oci_core_public_ip oci_core_public_ip_pool oci_core_route_table oci_core_security_list oci_core_service_gateway oci_core_subnet oci_core_vcn oci_core_vnic_attachment oci_core_volume oci_core_volume_attachment oci_core_volume_backup oci_core_volume_backup_policy oci_core_volume_default_backup_policy oci_core_volume_group oci_database_autonomous_database oci_database_autonomous_db_metric_cpu_utilization oci_database_autonomous_db_metric_cpu_utilization_daily oci_database_autonomous_db_metric_cpu_utilization_hourly oci_database_autonomous_db_metric_storage_utilization oci_database_autonomous_db_metric_storage_utilization_daily oci_database_autonomous_db_metric_storage_utilization_hourly oci_database_db oci_database_db_home oci_database_db_system oci_database_pluggable_database oci_database_software_image oci_devops_project oci_devops_repository oci_dns_rrset oci_dns_tsig_key oci_dns_zone oci_events_rule oci_file_storage_file_system oci_file_storage_mount_target oci_file_storage_snapshot oci_functions_application oci_functions_function oci_identity_api_key oci_identity_auth_token oci_identity_authentication_policy oci_identity_availability_domain oci_identity_compartment oci_identity_customer_secret_key oci_identity_dynamic_group oci_identity_group oci_identity_network_source oci_identity_policy oci_identity_tag_default oci_identity_tag_namespace oci_identity_tenancy oci_identity_user oci_kms_key oci_kms_key_version oci_kms_vault oci_logging_log oci_logging_log_group oci_logging_search oci_mysql_backup oci_mysql_channel oci_mysql_configuration oci_mysql_configuration_custom oci_mysql_db_system oci_mysql_db_system_metric_connections oci_mysql_db_system_metric_connections_daily oci_mysql_db_system_metric_connections_hourly oci_mysql_db_system_metric_cpu_utilization oci_mysql_db_system_metric_cpu_utilization_daily oci_mysql_db_system_metric_cpu_utilization_hourly oci_mysql_db_system_metric_memory_utilization oci_mysql_db_system_metric_memory_utilization_daily oci_mysql_heat_wave_cluster oci_network_firewall_firewall oci_network_firewall_policy oci_nosql_table oci_nosql_table_metric_read_throttle_count oci_nosql_table_metric_read_throttle_count_daily oci_nosql_table_metric_read_throttle_count_hourly oci_nosql_table_metric_storage_utilization oci_nosql_table_metric_storage_utilization_daily oci_nosql_table_metric_storage_utilization_hourly oci_nosql_table_metric_write_throttle_count oci_nosql_table_metric_write_throttle_count_daily oci_nosql_table_metric_write_throttle_count_hourly oci_objectstorage_bucket oci_objectstorage_object oci_ons_notification_topic oci_ons_subscription oci_queue_queue oci_region oci_resource_search oci_resourcemanager_stack oci_streaming_stream oci_vault_secret

Table: oci_identity_compartment

Compartments are the primary building blocks you use to organize your cloud resources. You use compartments to organize and isolate your resources to make it easier to manage and secure access to them. For more information about compartments policy, go to Understanding Compartments

Examples

Basic info

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

Details joined with parent compartment

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;

Full path of the compartments

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

.inspect oci_identity_compartment

OCI Identity Compartment

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
compartment_id	text	The OCID of the compartment in Tenant in which the resource is located.
defined_tags	jsonb	Defined 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.
description	text	The description you assign to the compartment.
freeform_tags	jsonb	Free-form tags for resource. This tags can be applied by any user with permissions on the resource.
id	text	The OCID of the compartment.
inactive_status	bigint	The detailed status of INACTIVE lifecycleState
is_accessible	boolean	Indicates whether or not the compartment is accessible for the user making the request.
lifecycle_state	text	The compartment's current state.
name	text	The name assigned to the compartment during creation
tags	jsonb	A map of tags for the resource.
tenant_id	text	The OCID of the Tenant in which the resource is located.
tenant_name	text	The name of the Tenant in which the resource is located.
time_created	timestamp with time zone	Date and time the user was created.
title	text	Title of the resource.