steampipe plugin install gcp

Table: gcp_compute_subnetwork - Query Google Cloud Compute Engine Subnetworks using SQL

Google Cloud Compute Engine Subnetworks are regional resources, each within a specific region, that contain IP address ranges. Subnetworks can be used to partition the IP space of a network into segments, improving network security and efficiency. They are associated with a network and region, and can have policies that control outbound internet access.

Table Usage Guide

The gcp_compute_subnetwork table provides insights into the subnetworks within Google Cloud Compute Engine. As a network administrator, explore subnetwork-specific details through this table, including IP ranges, associated network and region, and outbound internet access policies. Utilize it to uncover information about subnetworks, such as their configuration, status, and the partitioning of the IP space of a network.

Examples

Subnetwork basic info

Explore which subnetworks in your Google Cloud Platform have private IP Google access enabled. This can help determine areas where you may want to tighten security or reconfigure access permissions.

select
name,
gateway_address,
ip_cidr_range,
ipv6_cidr_range,
private_ip_google_access,
id,
network_name
from
gcp_compute_subnetwork;
select
name,
gateway_address,
ip_cidr_range,
ipv6_cidr_range,
private_ip_google_access,
id,
network_name
from
gcp_compute_subnetwork;

List of subnetworks where users have compute admin access assigned in a resource policy

Explore which subnetworks have users with compute admin access assigned, allowing you to understand the distribution of administrative privileges within your network resources. This query is useful for identifying potential security risks and ensuring appropriate access management.

select
name,
id,
jsonb_array_elements_text(p -> 'members') as members,
p ->> 'role' as role
from
gcp_compute_subnetwork,
jsonb_array_elements(iam_policy -> 'bindings') as p
where
p ->> 'role' = 'roles/compute.admin';
select
s.name,
s.id,
json_extract(p.value, '$.members') as members,
json_extract(p.value, '$.role') as role
from
gcp_compute_subnetwork as s,
json_each(iam_policy, '$.bindings') as p
where
json_extract(p.value, '$.role') = 'roles/compute.admin';

Secondary IP info of each subnetwork

Identify the secondary IP ranges within each subnetwork in your Google Cloud Platform. This can help you understand the distribution and usage of IP addresses within your network infrastructure.

select
name,
id,
p ->> 'rangeName' as range_name,
p ->> 'ipCidrRange' as ip_cidr_range
from
gcp_compute_subnetwork,
jsonb_array_elements(secondary_ip_ranges) as p;
select
s.name,
s.id,
json_extract(p.value, '$.rangeName') as range_name,
json_extract(p.value, '$.ipCidrRange') as ip_cidr_range
from
gcp_compute_subnetwork as s,
json_each(secondary_ip_ranges) as p;

Subnet count per network

Analyze your network configuration to understand how many subnetworks exist within each network. This can be useful for assessing the complexity and segmentation of your network infrastructure.

select
network,
count(*) as subnet_count
from
gcp_compute_subnetwork
group by
network;
select
network,
count(*) as subnet_count
from
gcp_compute_subnetwork
group by
network;

List subnetworks having VPC flow logging set to false

Determine the areas in which VPC flow logging is not enabled within your Google Cloud Platform subnetworks. This can help identify potential security vulnerabilities and improve network monitoring and troubleshooting.

select
name,
id,
enable_flow_logs
from
gcp_compute_subnetwork
where
not enable_flow_logs;
select
name,
id,
enable_flow_logs
from
gcp_compute_subnetwork
where
not enable_flow_logs;

IP Info subnets

Explore which subnetworks in your Google Cloud Platform (GCP) compute environment have specific characteristics. This query can help pinpoint the specific locations where the number of hosts per subnet may need to be adjusted for optimal network performance.

select
name,
id,
ip_cidr_range,
gateway_address,
broadcast(ip_cidr_range),
netmask(ip_cidr_range),
network(ip_cidr_range),
pow(2, 32 - masklen(ip_cidr_range)) -1 as hosts_per_subnet
from
gcp_compute_subnetwork;
Error: SQLite does not support CIDR operations.

Schema for gcp_compute_subnetwork

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
creation_timestamptimestamp with time zoneThe creation timestamp of the resource.
descriptiontextA user-specified, human-readable description of the address.
enable_flow_logsboolean!=, =Specifies whether to enable flow logging for this subnetwork, or not.
fingerprinttextAn unique system generated string, to reduce conflicts when multiple users change any property of the resource.
gateway_addressinetThe gateway address for default routes to reach destination addresses outside this subnetwork.
iam_policyjsonbAn Identity and Access Management (IAM) policy, which specifies access controls for Google Cloud resources. A `Policy` is a collection of `bindings`. A `binding` binds one or more `members` to a single `role`. Members can be user accounts, service accounts, Google groups, and domains (such as G Suite). A `role` is a named list of permissions; each `role` can be an IAM predefined role or a user-created custom role. For some types of Google Cloud resources, a `binding` can also specify a `condition`, which is a logical expression that allows access to a resource only if the expression evaluates to `true`.
idbigintThe unique identifier for the resource. This identifier is defined by the server.
ip_cidr_rangecidrThe range of internal addresses that are owned by this subnetwork.
ipv6_cidr_rangecidrThe range of internal IPv6 addresses that are owned by this subnetwork.
kindtextType of the resource. Always compute#subnetwork for Subnetwork resources.
locationtextThe GCP multi-region, region, or zone in which the resource is located.
log_config_aggregation_intervaltextCan only be specified if VPC flow logging for this subnetwork is enabled. Toggles the aggregation interval for collecting flow logs. Increasing the interval time will reduce the amount of generated flow logs for long lasting connections.
log_config_enablebooleanSpecifies whether to enable flow logging for this subnetwork, or not.
log_config_filter_exprtextCan only be specified if VPC flow logs for this subnetwork is enabled. Export filter used to define which VPC flow logs should be logged.
log_config_flow_samplingdouble precisionCan only be specified if VPC flow logging for this subnetwork is enabled. The value of the field must be in [0, 1]. Set the sampling rate of VPC flow logs within the subnetwork where 1.0 means all collected logs are reported and 0.0 means no logs are reported. Default is 0.5, which means half of all collected logs are reported.
log_config_metadatatextConfigures whether all, none or a subset of metadata fields should be added to the reported VPC flow logs.
log_config_metadata_fieldsjsonbCan only be specified if VPC flow logs for this subnetwork is enabled and 'metadata' was set to CUSTOM_METADATA.
nametext=Name of the resource. Provided by the client when the resource is created.
networktextThe URL of the network to which this subnetwork belongs.
network_nametextThe name of the network to which this subnetwork belongs.
private_ip_google_accessboolean!=, =Specifies whether the VMs in this subnet can access Google services without assigned external IP addresses.
private_ipv6_google_accesstext!=, =The private IPv6 google access type for the VMs in this subnet.
projecttext=, !=, ~~, ~~*, !~~, !~~*The GCP Project in which the resource is located.
purposetext!=, =The purpose of the resource.
regiontextThe URL of the region where the Subnetwork resides.
roletextSpecifies the role of the subnetwork.
secondary_ip_rangesjsonbAn array of configurations for secondary IP ranges for VM instances contained in this subnetwork.
self_linktextServer-defined URL for the resource.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statetext!=, =Specifies the current state of the subnetwork.
titletextTitle 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)" -- gcp

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

steampipe_export_gcp --config '<your_config>' gcp_compute_subnetwork