steampipe plugin install aws

Table: aws_vpc_subnet - Query AWS VPC Subnets using SQL

An AWS VPC (Virtual Private Cloud) Subnet is a range of IP addresses in your VPC. It allows you to launch AWS resources into a specified subnet, providing logical separation of resources based on security and operational needs. Subnets can be public, private, or VPN-only, providing flexible networking architecture.

Table Usage Guide

The aws_vpc_subnet table in Steampipe provides you with information about subnets within Amazon Virtual Private Cloud (VPC). This table allows you, as a DevOps engineer, to query subnet-specific details, including its configuration, associated VPC, availability zone, and CIDR block. You can utilize this table to gather insights on subnets, such as subnet size, associated route tables, network ACLs, and more. The schema outlines the various attributes of the subnet, including the subnet ID, VPC ID, state, CIDR block, and associated tags for you.

Examples

Basic VPC subnet IP address info

Determine the areas in which IP addresses are assigned within your AWS VPC subnets. This allows you to understand how IP address assignment and mapping is configured, which is crucial for managing network accessibility and security.

select
vpc_id,
subnet_id,
cidr_block,
assign_ipv6_address_on_creation,
map_customer_owned_ip_on_launch,
map_public_ip_on_launch,
ipv6_cidr_block_association_set
from
aws_vpc_subnet;
select
vpc_id,
subnet_id,
cidr_block,
assign_ipv6_address_on_creation,
map_customer_owned_ip_on_launch,
map_public_ip_on_launch,
ipv6_cidr_block_association_set
from
aws_vpc_subnet;

Availability zone info for each subnet in a VPC

Explore which subnets are located in each availability zone within a Virtual Private Cloud (VPC). This is useful for understanding your network layout and ensuring a balanced distribution across availability zones for resilience and high availability.

select
vpc_id,
subnet_id,
availability_zone,
availability_zone_id
from
aws_vpc_subnet
order by
vpc_id,
availability_zone;
select
vpc_id,
subnet_id,
availability_zone,
availability_zone_id
from
aws_vpc_subnet
order by
vpc_id,
availability_zone;

Find the number of available IP address in each subnet

Determine the areas in which there are available IP addresses within each subnet. This is useful for understanding where there is capacity for new devices or services.

select
subnet_id,
cidr_block,
available_ip_address_count,
power(2, 32 - masklen(cidr_block :: cidr)) -1 as raw_size
from
aws_vpc_subnet;
Error: SQLite does not support CIDR operations.

Route table associated with each subnet

Explore which route tables are linked to each subnet in your AWS VPC. This can help you understand the routing of network traffic within your virtual private cloud.

select
associations_detail ->> 'SubnetId' as subnet_id,
route_table_id
from
aws_vpc_route_table as rt
cross join jsonb_array_elements(associations) as associations_detail
join aws_vpc_subnet as sub on sub.subnet_id = associations_detail ->> 'SubnetId';
select
json_extract(associations_detail.value, '$.SubnetId') as subnet_id,
route_table_id
from
aws_vpc_route_table as rt
join json_each(rt.associations) as associations_detail
join aws_vpc_subnet as sub on sub.subnet_id = json_extract(associations_detail.value, '$.SubnetId');

Subnet count by VPC ID

Assess the distribution of subnets across various VPCs to understand the network segmentation in your AWS environment.

select
vpc_id,
count(subnet_id) as subnet_count
from
aws_vpc_subnet
group by
vpc_id;
select
vpc_id,
count(subnet_id) as subnet_count
from
aws_vpc_subnet
group by
vpc_id;

Schema for aws_vpc_subnet

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The AWS Account ID in which the resource is located.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
assign_ipv6_address_on_creationbooleanIndicates whether a network interface created in this subnet (including a network interface created by RunInstances) receives an IPv6 address.
availability_zonetext=The Availability Zone of the subnet.
availability_zone_idtext=The AZ ID of the subnet.
available_ip_address_countbigint=The number of unused private IPv4 addresses in the subnet. The IPv4 addresses for any stopped instances are considered unavailable.
cidr_blockcidr=Contains the IPv4 CIDR block assigned to the subnet.
customer_owned_ipv4_pooltextThe customer-owned IPv4 address pool associated with the subnet.
default_for_azboolean=Indicates whether this is the default subnet for the Availability Zone.
ipv6_cidr_block_association_setjsonbA list of IPv6 CIDR blocks associated with the subnet.
map_customer_owned_ip_on_launchbooleanIndicates whether a network interface created in this subnet (including a network interface created by RunInstances) receives a customer-owned IPv4 address.
map_public_ip_on_launchbooleanIndicates whether instances launched in this subnet receive a public IPv4 address.
outpost_arntext=The Amazon Resource Name (ARN) of the Outpost. Available only if subnet is on an outpost.
owner_idtext=Contains the AWS account that own the subnet.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
regiontextThe AWS Region in which the resource is located.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statetext=Current state of the subnet.
subnet_arntext=Contains the Amazon Resource Name (ARN) of the subnet.
subnet_idtext=Contains the unique ID to specify a subnet.
tagsjsonbA map of tags for the resource.
tags_srcjsonbA list of tags that are attached to the subnet.
titletextTitle of the resource.
vpc_idtext=ID of the VPC, the subnet is in.

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)" -- aws

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

steampipe_export_aws --config '<your_config>' aws_vpc_subnet