Example SQL

Summary

For use with MDGap summary parq files list

High level Total Packets and Gaps

duckdb -c "select make_timestamp_ns(min(ts_pcap_min)) as TSStart, (max(ts_pcap_max) - min(ts_pcap_min))/(24*60*60e9) as Days, proto,mcgroup,port,count(DISTINCT source) as PCAPFiles,sum(gap_cnt) as TotalGap,sum(pkt_byte)/1e9 as GB from read_parquet('./**/*.parq') group by proto,mcgroup,port order by port"

Example output

List all files with Gaps

Generates a list of all PCAP files which have gaps

duckdb  -c "select source,proto,endpoint,mcgroup,port,gap_cnt from read_parquet('./**/*.parq') where (gap_cnt > 0)"

Example output

List all files with Gaps on a specific Multicast Group

Filter for gaps/drops on a specific multicast group

duckdb  -c "select source,proto,endpoint,mcgroup,port,gap_cnt from read_parquet('./**/*.parq') \
  where (gap_cnt > 0) and \
    (MCGroup like '224.  0.206.  5%')"

Example output

List all files on a specific Port with a Timerange

List all files on multicast port

• 4502

• between 24th June 2026  8:55 and 09:05 AM EST

duckdb -c "select  source, proto,endpoint,mcgroup,port,pkt_cnt, gap_cnt from read_parquet('./**/*.parq') \
  where (port == 45002) and \
    (ts_pcap_min > epoch_ns('2026-06-24 08:55:00'::TIMESTAMPTZ AT TIME ZONE 'EST')) and \
    (ts_pcap_max < epoch_ns('2026-06-24 09:05:00'::TIMESTAMPTZ AT TIME ZONE 'EST'))  \
  order by ts_pcap_min"

Example output

Gap Logs

For use on MDGapLog .parq files, these files log each and every gap detected.

List the largest top 10 Gaps found

duckdb -c "select make_timestamp_ns(ts_pcap),source,proto,endpoint, mcgroup,port,seq_delta from read_parquet('./**/*.parq') order by seq_delta desc limit 10"

Example output

Summary per Multicast group of all Gaps

duckdb -c "select make_timestamp_ns(min(ts_pcap)),endpoint,mcgroup,port,sum(seq_delta) as TotalGap,count(distinct source) as TotalPCAP from read_parquet('./**/*.parq') group by endpoint,mcgroup,port order by port"

Example Ouput