Skip to content

Instantly share code, notes, and snippets.

@sdstrowes

sdstrowes/find_10_8_tr.sql

Last active September 15, 2021 16:10
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 sdstrowes/e9d4a3c7c03dd1aafa3198333cc39ffa to your computer and use it in GitHub Desktop.
Save sdstrowes/e9d4a3c7c03dd1aafa3198333cc39ffa to your computer and use it in GitHub Desktop.
Download ZIP
Raw
find_10_8_tr.sql
with matching_traceroutes as (
select start_time, prb_id, msm_id, hops
from ripencc-atlas.yesterday.traceroute, unnest(hops) h
where net.ip_trunc(h.hop_addr_bytes, 8) = net.ip_from_string("10.0.0.0") and af = 4 and hop > 4
)
-- ANY_VALUE() and TO_JSON_STRING() are magic to get back to the
-- original record, since I unpacked the hops in the statement above
select ANY_VALUE(matching_traceroutes).*
from matching_traceroutes
group by TO_JSON_STRING(matching_traceroutes)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment