Skip to content

Instantly share code, notes, and snippets.

@tylertreat
Created June 17, 2021 23:12
Show Gist options
  • Save tylertreat/8b3aa562a3fc7659e4762d157aa3b5e6 to your computer and use it in GitHub Desktop.
Save tylertreat/8b3aa562a3fc7659e4762d157aa3b5e6 to your computer and use it in GitHub Desktop.
BigQuery query for GCP flow logs graph visualization
SELECT
jsonPayload.CONNECTION.src_ip,
jsonPayload.CONNECTION.dest_ip,
jsonPayload.src_instance.region AS src_region,
jsonPayload.src_instance.zone AS src_zone,
jsonPayload.src_instance.vm_name AS src_vm,
jsonPayload.dest_instance.region AS dest_region,
jsonPayload.dest_instance.zone AS dest_zone,
jsonPayload.dest_instance.vm_name AS dest_vm,
jsonPayload.src_vpc.vpc_name AS src_vpc,
jsonPayload.src_vpc.subnetwork_name AS src_subnet,
jsonPayload.dest_vpc.vpc_name AS dest_vpc,
jsonPayload.dest_vpc.subnetwork_name AS dest_subnet,
SUM(CAST(jsonPayload.bytes_sent AS int64)) AS bytes_sent
FROM
`rk-playground.vpc_flow_logs.compute_googleapis_com_vpc_flows`
GROUP BY
jsonPayload.CONNECTION.src_ip,
jsonPayload.CONNECTION.dest_ip,
jsonPayload.src_instance.region,
jsonPayload.src_instance.zone,
jsonPayload.src_instance.vm_name,
jsonPayload.dest_instance.region,
jsonPayload.dest_instance.zone,
jsonPayload.dest_instance.vm_name,
jsonPayload.src_vpc.vpc_name,
jsonPayload.src_vpc.subnetwork_name,
jsonPayload.dest_vpc.vpc_name,
jsonPayload.dest_vpc.subnetwork_name
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment