steampipe plugin install ocisteampipe plugin install oci

Table: oci_core_subnet - Query OCI Core Subnets using SQL

A Subnet in OCI Core is a subdivision within a VCN (Virtual Cloud Network). It consists of a contiguous range of IP addresses that do not overlap with other subnets in the VCN. You can launch instances, databases, and other resources in a subnet.

Table Usage Guide

The oci_core_subnet table provides insights into subnets within Oracle Cloud Infrastructure's Core Services. As a network administrator, explore subnet-specific details through this table, including associated security lists, route tables, and the availability domain in which the subnet is located. Utilize it to uncover information about subnets, such as those with public IP addresses, the CIDR blocks they cover, and the route tables they use.

Examples

Basic info

Explore which subnets are currently active in your Oracle Cloud Infrastructure, along with their creation times and any associated tags. This can help manage resources and track their usage over time.

select
  display_name,
  id,
  lifecycle_state,
  time_created,
  tags
from
  oci_core_subnet;

select
  display_name,
  id,
  lifecycle_state,
  time_created,
  tags
from
  oci_core_subnet;

Get the OCIDs of the security list for each subnet

Determine the unique identifiers of security lists associated with each network subnet. This can be useful to assess the security configuration and understand how it is applied across different network segments.

select
  display_name,
  id,
  jsonb_array_elements_text(security_list_ids) as security_list_id
from
  oci_core_subnet;

select
  display_name,
  id,
  json_each.value as security_list_id
from
  oci_core_subnet,
  json_each(security_list_ids);

Count of subnets by VCN ID

Analyze the settings to understand the distribution of subnets across different Virtual Cloud Networks (VCNs). This is useful for managing resources and load balancing across multiple VCNs.

select
  vcn_id,
  count(id) as subnet_count
from
  oci_core_subnet
group by
  vcn_id;

select
  vcn_id,
  count(id) as subnet_count
from
  oci_core_subnet
group by
  vcn_id;

Get the number of available IP address in each subnet

Explore which subnets have the most available IP addresses to optimize network resource allocation and ensure efficient use of your network space. This is particularly useful in planning network expansion or monitoring network usage.

select
  id,
  cidr_block,
  power(2, 32 - masklen(cidr_block :: cidr)) -1 as raw_size
from
  oci_core_subnet;

Error: SQLite does not support CIDR operations.

Query examples

Control examples

Schema for oci_core_subnet

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
availability_domain	text		The subnet's availability domain.
cidr_block	cidr		The subnet's CIDR block.
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.
dhcp_options_id	text		The OCID of the set of DHCP options that the subnet uses.
display_name	text	=	A user-friendly name. Does not have to be unique, and it's changeable.
dns_label	text		A DNS label for the subnet, used in conjunction with the VNIC's hostname and VCN's DNS label to form a fully qualified domain name (FQDN) for each VNIC within this subnet.
freeform_tags	jsonb		Free-form tags for resource. This tags can be applied by any user with permissions on the resource.
id	text	=	The subnet's Oracle ID (OCID).
ipv6_cidr_block	cidr		For an IPv6-enabled subnet, this is the IPv6 CIDR block for the subnet's private IP address space.
ipv6_public_cidr_block	cidr		For an IPv6-enabled subnet, this is the IPv6 CIDR block for the subnet's public IP address space.
ipv6_virtual_router_ip	inet		For an IPv6-enabled subnet, this is the IPv6 address of the virtual router.
lifecycle_state	text	=	The subnet's current state.
prohibit_public_ip_on_vnic	boolean		Indicates whether VNICs within this subnet can have public IP addresses.
region	text		The OCI region in which the resource is located.
route_table_id	text		The OCID of the route table that the subnet uses.
security_list_ids	jsonb		The OCIDs of the security list or lists that the subnet uses.
subnet_domain_name	text		The subnet's domain name, which consists of the subnet's DNS label, the VCN's DNS label, and the `oraclevcn.com` domain.
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		The date and time the subnet was created.
title	text		Title of the resource.
vcn_id	text	=	The OCID of the VCN the subnet is in.
virtual_router_ip	inet		The IP address of the virtual router.
virtual_router_mac	text		The MAC address of the virtual router.

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_core_subnet