Show HN: Pcapsql – SQL interface for PCAP analysis(github.com)

  -- RTT Analysis using TCP timestamp echo
  -- Measures actual network RTT by tracking when our ts_val gets echoed back
  -- For client-side captures: dst_port in (80,443) = outbound, src_port in (80,443) = inbound


  WITH outbound AS (
      -- Packets TO server (dst_port is well-known)
      SELECT
          t.frame_number,
          f.timestamp as send_ts,
          t.dst_port as server_port,
          t.ts_val,
          i.src_ip as local_ip,
          t.src_port as local_port,
          i.dst_ip as remote_ip
      FROM tcp t
      JOIN ipv4 i ON t.frame_number = i.frame_number
      JOIN frames f ON t.frame_number = f.frame_number
      WHERE t.ts_val IS NOT NULL
         AND t.dst_port IN (80, 443, 8080, 8443)
  ),
  inbound AS (
      -- Packets FROM server (src_port is well-known)
      SELECT
          t.frame_number,
          f.timestamp as recv_ts,
          t.src_port as server_port,
          t.ts_ecr,
          i.dst_ip as local_ip,
          t.dst_port as local_port,
          i.src_ip as remote_ip
      FROM tcp t
      JOIN ipv4 i ON t.frame_number = i.frame_number
      JOIN frames f ON t.frame_number = f.frame_number
      WHERE t.ts_ecr IS NOT NULL
        AND t.ts_ecr > 0
        AND t.src_port IN (80, 443, 8080, 8443)
  ),
  -- Match: find when our ts_val was echoed back by the server
  rtt_samples AS (
      SELECT
          o.frame_number as send_frame,
          MIN(i.frame_number) as recv_frame,
          o.server_port,
          o.send_ts,
          MIN(i.recv_ts) as recv_ts
      FROM outbound o
      JOIN inbound i
          ON o.local_ip = i.local_ip
          AND o.local_port = i.local_port
          AND o.remote_ip = i.remote_ip
          AND o.server_port = i.server_port
          AND i.ts_ecr = o.ts_val
          AND i.frame_number > o.frame_number
      GROUP BY o.frame_number, o.server_port, o.send_ts
  ),
  rtt_values AS (
      SELECT
          server_port,
          EXTRACT(EPOCH FROM (recv_ts - send_ts)) * 1000.0 as rtt_ms
      FROM rtt_samples
      WHERE recv_ts > send_ts
  )
  SELECT
      server_port,
      hdr_count(hdr_histogram(rtt_ms)) as samples,
      ROUND(hdr_min(hdr_histogram(rtt_ms)), 2) as min_ms,
      ROUND(hdr_percentile(hdr_histogram(rtt_ms), 0.50), 2) as p50_ms,
      ROUND(hdr_percentile(hdr_histogram(rtt_ms), 0.75), 2) as p75_ms,
      ROUND(hdr_percentile(hdr_histogram(rtt_ms), 0.95), 2) as p95_ms,
      ROUND(hdr_percentile(hdr_histogram(rtt_ms), 0.99), 2) as p99_ms,
      ROUND(hdr_max(hdr_histogram(rtt_ms)), 2) as max_ms,
      ROUND(hdr_mean(hdr_histogram(rtt_ms)), 2) as mean_ms
  FROM rtt_values
  WHERE rtt_ms > 0 AND rtt_ms < 30000
  GROUP BY server_port
  ORDER BY samples DESC;

  +-------------+---------+--------+--------+--------+--------+--------+--------+---------+
     | server_port | samples | min_ms | p50_ms | p75_ms | p95_ms | p99_ms | max_ms | mean_ms |
     +=======================================================================================+
     | 443         | 315     | 1.0    | 75.0   | 263.0  | 349.0  | 3007.0 | 3007.0 | 177.27  |
     |-------------+---------+--------+--------+--------+--------+--------+--------+---------|
     | 80          | 6       | 70.0   | 71.0   | 72.0   | 72.0   | 72.0   | 72.0   | 71.17   |
     +-------------+---------+--------+--------+--------+--------+--------+--------+---------+