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, tagsfrom oci_core_subnet;
select display_name, id, lifecycle_state, time_created, tagsfrom 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_idfrom oci_core_subnet;
select display_name, id, json_each.value as security_list_idfrom 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_countfrom oci_core_subnetgroup by vcn_id;
select vcn_id, count(id) as subnet_countfrom oci_core_subnetgroup 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_sizefrom oci_core_subnet;
Error: SQLite does not support CIDR operations.
Query examples
- compute_instances_for_vcn_vcn
- identity_availability_domains_for_vcn_subnet
- identity_availability_domains_for_vcn_vcn
- oci_vcn_no_subnet_count
- oci_vcn_subnet_by_compartment
- oci_vcn_subnet_by_region
- oci_vcn_subnet_by_tenancy
- oci_vcn_subnet_by_vcn
- oci_vcn_subnet_count
- regional_identity_availability_domains_for_vcn_subnet
- regional_identity_availability_domains_for_vcn_vcn
- vcn_attached_subnet_count
- vcn_dhcp_options_for_vcn_subnet
- vcn_gateway_sankey
- vcn_load_balancers_for_vcn_vcn
- vcn_network_load_balancers_for_vcn_vcn
- vcn_nsl_egress_rule_sankey
- vcn_nsl_ingress_rule_sankey
- vcn_route_tables_for_vcn_subnet
- vcn_security_lists_for_vcn_subnet
- vcn_subnet
- vcn_subnet_cidr_block
- vcn_subnet_input
- vcn_subnet_overview
- vcn_subnet_tag
- vcn_subnets_for_vcn_security_list
- vcn_subnets_for_vcn_vcn
- vcn_vcns_for_compute_instance
- vcn_vcns_for_database_autonomous_database
- vcn_vcns_for_mysql_db_system
- vcn_vcns_for_vcn_subnet
Control examples
Schema for oci_core_subnet
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form. | |
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. | |
sp_connection_name | text | =, !=, ~~, ~~*, !~~, !~~* | Steampipe connection name. |
sp_ctx | jsonb | Steampipe context in JSON form. | |
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