Table: oci_resource_search - Query OCI Resource Search using SQL

Oracle Cloud Infrastructure (OCI) Resource Search is a service that enables you to find, explore, and understand the entirety of your OCI resources. It provides a unified view of the resources across all compartments in a tenancy, allowing you to manage and govern your resources more effectively. With OCI Resource Search, you can search for resources based on their attributes, relationships, and configurations.

Table Usage Guide

The oci_resource_search table provides insights into the resources within Oracle Cloud Infrastructure. As a cloud administrator, explore resource-specific details through this table, including resource types, compartments, and associated metadata. Utilize it to uncover information about resources, such as their configurations, relationships, and the compartment in which they reside.

Important Notes

You must specify either the text or query in the where clause to query this table.

Examples

List resources/services/documentations with freetext as test

Explore which resources, services, or documentations were created at a specific time and are currently active, based on a free text search for the term 'test'. This can help in identifying relevant resources quickly and efficiently.

select
  identifier,
  display_name,
  time_created,
  lifecycle_state as state
from
  oci_resource_search
where
  text = 'test';

select
  identifier,
  display_name,
  time_created,
  lifecycle_state as state
from
  oci_resource_search
where
  text = 'test';

List running instances

Identify instances where specific resources are currently active. This allows for a better understanding of resource usage and management, particularly for optimizing operations and troubleshooting.

select
  identifier,
  display_name,
  time_created,
  lifecycle_state as state
from
  oci_resource_search
where
  query = 'query instance resources where lifeCycleState = "RUNNING"';

select
  identifier,
  display_name,
  time_created,
  lifecycle_state as state
from
  oci_resource_search
where
  query = 'query instance resources where lifeCycleState = "RUNNING"';

List resources created in the root compartment

Determine the areas in which resources were created in the root compartment. This can be beneficial for assessing the elements within your organization's infrastructure, to maintain an overview of resource allocation and usage.

select
  identifier,
  display_name,
  time_created,
  lifecycle_state as state
from
  oci_resource_search
where
  query = 'query all resources where compartmentId = "ocid1.tenancy.oc1..aaaaaaah5soecxzjetci3yjjnjqmfkr4po3"';

select
  identifier,
  display_name,
  time_created,
  lifecycle_state as state
from
  oci_resource_search
where
  query = 'query all resources where compartmentId = "ocid1.tenancy.oc1..aaaaaaah5soecxzjetci3yjjnjqmfkr4po3"';

Schema for oci_resource_search

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
availability_domain	text		The availability domain where this resource exists, if applicable.
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.
display_name	text		The display name (or name) of this resource, if one exists.
freeform_tags	jsonb		Free-form tags for resource. This tags can be applied by any user with permissions on the resource.
identifier	text		The unique identifier for this particular resource, usually an OCID.
identity_context	jsonb		Additional identifiers to use together in a Get request for a specified resource, only required for resource types that explicitly cannot be retrieved by using a single identifier, such as the resource's OCID.
lifecycle_state	text		The lifecycle state of this resource, if applicable.
query	text	=	The query based on which the search was done.
resource_type	text		The resource type name.
search_context	jsonb		SearchContext Contains search context, such as highlighting, for found resources.
search_region	text		The OCI region in which the resource is located.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
system_tags	jsonb		System tags associated with this resource.
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.
text	text	=	The freeText based on which the search was done.
time_created	timestamp with time zone		The time that this resource was created.
title	text		Title 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_resource_search