turbot/gcp

steampipe plugin install gcpsteampipe plugin install gcp
gcp_audit_policygcp_bigquery_datasetgcp_bigquery_jobgcp_bigquery_tablegcp_bigtable_instancegcp_cloudfunctions_functiongcp_compute_addressgcp_compute_backend_bucketgcp_compute_backend_servicegcp_compute_diskgcp_compute_disk_metric_read_opsgcp_compute_disk_metric_read_ops_dailygcp_compute_disk_metric_read_ops_hourlygcp_compute_disk_metric_write_opsgcp_compute_disk_metric_write_ops_dailygcp_compute_disk_metric_write_ops_hourlygcp_compute_firewallgcp_compute_forwarding_rulegcp_compute_global_addressgcp_compute_global_forwarding_rulegcp_compute_imagegcp_compute_instancegcp_compute_instance_metric_cpu_utilizationgcp_compute_instance_metric_cpu_utilization_dailygcp_compute_instance_metric_cpu_utilization_hourlygcp_compute_instance_templategcp_compute_machine_typegcp_compute_networkgcp_compute_node_groupgcp_compute_node_templategcp_compute_project_metadatagcp_compute_regiongcp_compute_resource_policygcp_compute_routergcp_compute_snapshotgcp_compute_ssl_policygcp_compute_subnetworkgcp_compute_target_https_proxygcp_compute_target_poolgcp_compute_target_ssl_proxygcp_compute_target_vpn_gatewaygcp_compute_url_mapgcp_compute_vpn_tunnelgcp_compute_zonegcp_dns_managed_zonegcp_dns_policygcp_dns_record_setgcp_iam_policygcp_iam_rolegcp_kms_keygcp_kms_key_ringgcp_logging_bucketgcp_logging_exclusiongcp_logging_metricgcp_logging_sinkgcp_monitoring_alert_policygcp_monitoring_groupgcp_monitoring_notification_channelgcp_organizationgcp_projectgcp_project_organization_policygcp_project_servicegcp_pubsub_snapshotgcp_pubsub_subscriptiongcp_pubsub_topicgcp_service_accountgcp_service_account_keygcp_sql_backupgcp_sql_databasegcp_sql_database_instancegcp_sql_database_instance_metric_connectionsgcp_sql_database_instance_metric_connections_dailygcp_sql_database_instance_metric_connections_hourlygcp_sql_database_instance_metric_cpu_utilizationgcp_sql_database_instance_metric_cpu_utilization_dailygcp_sql_database_instance_metric_cpu_utilization_hourlygcp_storage_bucket

Table: gcp_compute_subnetwork

A subnetwork (also known as a subnet) is a logical partition of a Virtual Private Cloud network with one primary IP range and zero or more secondary IP ranges.

Examples

Subnetwork basic info

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

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';

Secondary IP info of each subnetwork

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;

Subnet count per network

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

List subnetworks having VPC flow logging set to false

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

IP Info subnets

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;

.inspect gcp_compute_subnetwork

GCP Compute Subnetwork

NameTypeDescription
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
creation_timestamptimestamp without time zoneThe creation timestamp of the resource.
descriptiontextA user-specified, human-readable description of the address.
enable_flow_logsbooleanSpecifies 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.
nametextName 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_accessbooleanSpecifies whether the VMs in this subnet can access Google services without assigned external IP addresses.
private_ipv6_google_accesstextThe private IPv6 google access type for the VMs in this subnet.
projecttextThe GCP Project in which the resource is located.
purposetextThe 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.
statetextSpecifies the current state of the subnetwork.
titletextTitle of the resource.