Skip to content

Instantly share code, notes, and snippets.

@tylertreat

tylertreat/query.txt

Created June 17, 2021 23:12
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save tylertreat/8b3aa562a3fc7659e4762d157aa3b5e6 to your computer and use it in GitHub Desktop.
Save tylertreat/8b3aa562a3fc7659e4762d157aa3b5e6 to your computer and use it in GitHub Desktop.
Download ZIP
BigQuery query for GCP flow logs graph visualization
Raw
query.txt
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