Documentation Index

Fetch the complete documentation index at: https://docs.fmad.io/llms.txt

Use this file to discover all available pages before exploring further.

Examples

  • Updated on Jul 24, 2026
  • Published on Jul 12, 2026
  • 14 minute(s) read
  • aA
 Prev Next

Environment

Duckdb has a power macro expansion function that allows expanding a table functionality significantly.

FMADIO Macro file is located here

pcap2parq.sql

Best to copy into

mkdir /opt/fmadio/etc/duckdb
cp pcap2parq.sql /opt/fmadio/etc/duckdb

Or use curl 

curl -O https://firmware.fmad.io/download/pcap2parq/pcap2parq.sql

Running these example requires the following

Step1 ) creating a file

For example the Top50 flows

nano top50.sql

Paste the contents + save

Step 2) Make executable

Make the binary executable

chmod uga+rwx ./top50.sql

Step 3) Run with a .parq file

Execute with a PARQ file

./top50  interop17_split_20170609_223953.717.953.280.parq

Example output

fmadio@fmadio200v4-636:/mnt/store1/pcap2parq$ ./top50  interop17_split_20170609_223953.717.953.280.parq  | head
-- Loading resources from /opt/fmadio/etc/duckdb/pcap2parq.sql
┌───────────────────────────────┬──────────────────────────────────┬───────────────┬───────────────────────────────────────────────────────┬───────────┐
│  make_timestamp_ns(ts_begin)  │          hash_inner_str          │ entropy_inner │                      ipv4_0_flow                      │ byte_wire │
│         timestamp_ns          │             varchar              │    uint32     │                        varchar                        │   int64   │
├───────────────────────────────┼──────────────────────────────────┼───────────────┼───────────────────────────────────────────────────────┼───────────┤
│ 2017-06-09 13:39:53.783211495 │ 18fc705e25327303910b1db101430228 │           246 │ [TCP ]  10.  1. 28.114:50441 -> 211.  7.166. 14:22002 │       750 │
│ 2017-06-09 13:39:53.7833821   │ 18fc705e25327303910b1db101430228 │           246 │ [TCP ] 130.128.255. 28:50441 -> 211.  7.166. 14:22002 │       746 │
│ 2017-06-09 13:39:53.900363607 │ 18fc705e25327303910b1db101430228 │           246 │ [TCP ]  10.  1. 28.114:50441 -> 211.  7.166. 14:22002 │       750 │
│ 2017-06-09 13:39:53.900606794 │ 18fc705e25327303910b1db101430228 │           246 │ [TCP ] 130.128.255. 28:50441 -> 211.  7.166. 14:22002 │       746 │
│ 2017-06-09 13:39:53.98851696  │ 18fc705e25327303910b1db101430228 │           246 │ [TCP ]  10.  1. 28.114:50441 -> 211.  7.166. 14:22002 │       750 │
│ 2017-06-09 13:39:53.989042839 │ 18fc705e25327303910b1db101430228 │           246 │ [TCP ] 130.128.255. 28:50441 -> 211.  7.166. 14:22002 │       746 │
fmadio@fmadio200v4-636:/mnt/store1/pcap2parq$

Top 50

Various Top 50 stats

Top 50 Flows (IPv4 level 0)

Generating a top 50 flows on the 1st level of IPv4 is shown below

#/usr/local/bin/bash
duckdb -init /opt/fmadio/etc/duckdb/pcap2parq.sql <<EOF
SELECT
    make_timestamp_ns(min(ts_begin)),
    ipv4_0_flow,
    sum(byte_wire) as byte,
    (max(ts_end) - min(ts_begin))/1e9 as sec
FROM read_pcap2parq('$1')
GROUP BY ipv4_0_flow
ORDER BY byte DESC
LIMIT 50
;
EOF

Example output

fmadio@fmadio200v4-636:/mnt/store1/pcap2parq$ ./top50 interop17_split_20170609_223953.717.953.280.parq
-- Loading resources from /opt/fmadio/etc/duckdb/pcap2parq.sql
┌──────────────────────────────────┬───────────────────────────────────────────────────────┬────────────┬──────────────┐
│ make_timestamp_ns(min(ts_begin)) │                      ipv4_0_flow                      │    byte    │     sec      │
├──────────────────────────────────┼───────────────────────────────────────────────────────┼────────────┼──────────────┤
│ 2017-06-09 13:39:53.717953341    │ [UDP ] 150.100. 29. 14:10662 -> 130.128. 19. 30: 5004 │ 3576306282 │ 15.332070851 │
│ 2017-06-09 13:39:53.718775744    │ [   0]   0.  0.  0.  0:    0 ->   0.  0.  0.  0:    0 │ 116458587  │ 15.330399738 │
│ 2017-06-09 13:39:53.718539002    │ [UDP ] 223.133.125.178: 6000 -> 130.128. 48. 10: 6000 │ 74374738   │ 15.331130457 │
│ 2017-06-09 13:39:53.73341992     │ [TCP ]  45.  0.205.216:59574 ->  54.245. 16. 35:  443 │ 70371326   │ 15.31630104  │
│ 2017-06-09 13:39:53.747921565    │ [TCP ]  45.  0.205.216:45802 ->  54.218. 97.237:  443 │ 70159545   │ 15.292268285 │
│ 2017-06-09 13:39:53.741614922    │ [TCP ]  45.  0.205.216:48402 ->  54.244.158. 88:  443 │ 44397431   │ 15.301076477 │
│ 2017-06-09 13:39:53.718250141    │ [TCP ] 133.243.238. 82:11196 ->  45.  0.205. 22:50806 │ 38732680   │ 15.329830739 │
│ 2017-06-09 13:39:54.437289002    │ [TCP ]  54.192. 85. 51:   80 ->  10.  1.102.145:52058 │ 33487728   │ 14.606012451 │
│ 2017-06-09 13:39:54.43703138     │ [TCP ]  54.192. 85. 51:   80 -> 130.128.255.102:52058 │ 33399304   │ 14.606034115 │
│ 2017-06-09 13:39:53.921734087    │ [TCP ]  17.250.118.205:   80 -> 202. 17.220.148:49282 │ 24976577   │ 13.938437276 │
│ 2017-06-09 13:39:53.719672842    │ [TCP ] 218.110.175.130:50170 ->  10.  1. 39.105:50293 │ 19472929   │ 15.32807391  │
│ 2017-06-09 13:39:53.719138525    │ [TCP ] 218.110.175.130:50170 -> 130.128.255. 39:50293 │ 19412985   │ 15.330689235 │
│ 2017-06-09 13:39:53.718027063    │ [TCP ] 218.110.175.130:50173 ->  10.  1. 39.105:49203 │ 17313835   │ 15.328456627 │
│ 2017-06-09 13:39:53.718162272    │ [TCP ] 218.110.175.130:50173 -> 130.128.255. 39:49203 │ 17254469   │ 15.327836259 │
│ 2017-06-09 13:39:53.718415552    │ [TCP ]  45.  0.250.254:54369 -> 218.220.189. 37:   22 │ 16232266   │ 15.331038275 │
│ 2017-06-09 13:39:53.732118052    │ [UDP ] 119.243. 74. 94:44881 -> 130.128. 47.  5:11917 │ 14543278   │ 15.29778319  │
│ 2017-06-09 13:39:53.740662372    │ [TCP ] 150. 65.230.138: 8000 ->  10.  1.104.108:51379 │ 14477913   │ 15.285898169 │
│ 2017-06-09 13:39:53.740330605    │ [TCP ] 150. 65.230.138: 8000 -> 130.128.255.104:51379 │ 14438753   │ 15.285937974 │
│ 2017-06-09 13:39:53.731233156    │ [UDP ]  52.221. 20.123: 5004 -> 130.128. 55.  7:36005 │ 11824148   │ 15.308687247 │
│ 2017-06-09 13:39:53.727823271    │ [UDP ] 210.  4.206. 10:58378 -> 130.128. 55.  7:50032 │ 9811142    │ 15.298376051 │
│ 2017-06-09 13:39:53.72099425     │ [UDP ] 130.128. 47.  5:11917 -> 119.243. 74. 94:44881 │ 9599048    │ 15.3199112   │
│ 2017-06-09 13:39:53.741406388    │ [UDP ] 130.128. 55.  7:50022 ->  62.109.220. 18:59964 │ 9305952    │ 15.294465641 │
│ 2017-06-09 13:39:55.159027482    │ [TCP ]  52.113. 66. 77:  443 -> 130.128.112.  8:63396 │ 9025020    │ 13.748728928 │
│ 2017-06-09 13:39:53.896201658    │ [TCP ]  52.113. 66. 13:  443 -> 130.128.112.  8:63505 │ 8776934    │ 15.135948409 │
│ 2017-06-09 13:39:53.71970146     │ [UDP ]  10.  1.103.106:59531 -> 110.  0.  2.117: 4900 │ 8728740    │ 15.329604115 │
│ 2017-06-09 13:39:53.720165357    │ [UDP ] 130.128.255.103:59531 -> 110.  0.  2.117: 4900 │ 8703180    │ 15.329531789 │
│ 2017-06-09 13:39:53.725541703    │ [UDP ] 130.128. 55.  7:50032 -> 210.  4.206. 10:58378 │ 8639256    │ 15.323932284 │
│ 2017-06-09 13:39:53.743391652    │ [TCP ]  45.  0.191.245: 2956 -> 162.125. 33.  2:  443 │ 8054515    │ 14.896172348 │
│ 2017-06-09 13:39:53.724852983    │ [UDP ] 130.128. 55.  7:36005 ->  52.221. 20.123: 5004 │ 8033665    │ 15.325157753 │
│ 2017-06-09 13:39:53.9032577      │ [TCP ]  52.113. 66. 77:  443 -> 130.128.112.  8:63555 │ 8010958    │ 15.145109532 │
│ 2017-06-09 13:39:54.353181892    │ [TCP ]  45.  0.191.245: 2955 -> 162.125. 33.  2:  443 │ 7724290    │ 14.671508383 │
│ 2017-06-09 13:39:53.728368928    │ [UDP ] 130.128. 55.  8: 3238 -> 210.  4.206. 38:46254 │ 7586472    │ 15.319713837 │
│ 2017-06-09 13:39:53.724457885    │ [TCP ] 130.128.112.  8:63471 ->  52.113. 66. 13:  443 │ 7568071    │ 15.324409715 │
│ 2017-06-09 13:39:53.732346378    │ [UDP ]  52.221. 20.123: 5004 -> 130.128. 55.  8:62554 │ 7425746    │ 15.29751015  │
│ 2017-06-09 13:39:53.745396202    │ [UDP ] 130.128. 55.  7:50034 ->  52.113.  4. 37:52292 │ 7421676    │ 15.301543206 │
│ 2017-06-09 13:39:53.732719204    │ [UDP ] 210.  4.206. 38:46254 -> 130.128. 55.  8: 3238 │ 7216592    │ 15.29864543  │
│ 2017-06-09 13:39:53.745220743    │ [UDP ] 130.128.112.  8:62394 ->  52.113. 66.126:55617 │ 7110912    │ 15.294112428 │
│ 2017-06-09 13:39:53.718039332    │ [TCP ]  52.198.141.137:  443 -> 130.128. 47. 20:49375 │ 7067870    │ 15.320750611 │
│ 2017-06-09 13:39:53.721065204    │ [TCP ] 203.178.142.230:  443 -> 130.128. 10. 12:52859 │ 6946720    │ 15.21081783  │
│ 2017-06-09 13:39:53.739059159    │ [UDP ] 130.128. 55.  7:50022 ->  52.113.  4. 45: 3478 │ 6862972    │ 15.278793148 │
│ 2017-06-09 13:39:53.751796071    │ [TCP ]  45.  0.191.245: 2958 -> 162.125. 32.  2:  443 │ 6584583    │ 15.19539111  │
│ 2017-06-09 13:39:54.642158496    │ [TCP ]  45.  0.191.245: 2954 -> 162.125. 33.  2:  443 │ 6446988    │ 14.282831626 │
│ 2017-06-09 13:39:53.718578692    │ [TCP ]  45.  0.205. 22:50806 -> 133.243.238. 82:11196 │ 6415639    │ 15.329626822 │
│ 2017-06-09 13:39:53.72083888     │ [TCP ]  45.  0.197. 11:  443 ->  10.  1.173. 56:54964 │ 6406012    │ 6.401103632  │
│ 2017-06-09 13:39:53.720339111    │ [TCP ]  45.  0.197. 11:  443 -> 130.128.255.173:54964 │ 6372984    │ 6.401050688  │
│ 2017-06-09 13:39:53.734393376    │ [UDP ] 130.128. 55.  8:62554 ->  52.221. 20.123: 5004 │ 6126598    │ 15.306513152 │
│ 2017-06-09 13:39:54.365330634    │ [UDP ]  39.110.208.207: 4500 -> 130.128.255.  7: 4500 │ 5994166    │ 14.684014233 │
│ 2017-06-09 13:39:53.721866202    │ [UDP ]  62.109.220. 18:59964 -> 130.128. 55.  7:50022 │ 5608866    │ 15.303801673 │
│ 2017-06-09 13:39:53.717959047    │ [UDP ]  10.  1.170.111:65048 ->  54.178.176.175:35003 │ 5061030    │ 15.332032464 │
│ 2017-06-09 13:39:53.718089197    │ [UDP ]  54.178.176.175:35001 ->  10.  1.170.111:56467 │ 5055149    │ 15.327205846 │
└──────────────────────────────────┴───────────────────────────────────────────────────────┴────────────┴──────────────┘
fmadio@fmadio200v4-636:/mnt/store1/pcap2parq$

Top 50 Hosts

Generate a list of the most active Hosts

#/usr/local/bin/bash
duckdb -init /opt/fmadio/etc/duckdb/pcap2parq.sql <<EOF
SELECT
    u32_to_ipv4(raw_ip) AS host_ip,
    SUM(byte_wire) AS total_bytes
FROM (
    -- Step 1: Unnest the IPs into a single column first
    SELECT
        unnest([ipv4_0_src, ipv4_0_dst]) AS raw_ip,
        byte_wire
    FROM read_parquet('$1')
)
GROUP BY raw_ip
ORDER BY total_bytes DESC
LIMIT 50
;
EOF

Example output

+-----------------+-------------+
|     host_ip     | total_bytes |
+-----------------+-------------+
| 150.100.29.14   | 3576340916  |
| 130.128.19.30   | 3576340916  |
| 0.0.0.0         | 232929020   |
| 45.0.205.216    | 189475678   |
| 130.128.55.7    | 90538923    |
| 218.110.175.130 | 75287616    |
| 223.133.125.178 | 74382358    |
| 130.128.48.10   | 74382358    |
| 54.245.16.35    | 71956576    |
| 54.218.97.237   | 71754955    |
| 54.192.85.51    | 67181224    |
| 130.128.112.8   | 51113847    |
| 133.243.238.82  | 47074462    |
| 10.1.39.105     | 46819335    |
| 130.128.255.39  | 46686778    |
| 54.244.158.88   | 45416331    |
| 45.0.205.22     | 45148319    |
| 54.178.176.175  | 36999074    |
| 52.221.20.123   | 36532659    |
| 130.128.255.102 | 36167292    |
| 10.1.102.145    | 34143032    |
| 45.0.197.11     | 31298739    |
| 130.128.55.8    | 30499592    |
| 150.65.230.138  | 29317946    |
| 45.0.191.245    | 29180816    |
| 202.17.220.148  | 28869427    |
| 130.128.47.5    | 26790730    |
| 17.250.118.205  | 25416293    |
| 119.243.74.94   | 24142326    |
| 162.125.33.2    | 22461753    |
| 52.113.4.37     | 21841766    |
| 219.101.163.230 | 20740005    |
| 210.4.206.10    | 18805884    |
| 130.128.255.170 | 18588175    |
| 10.1.170.111    | 18553713    |
| 52.113.66.13    | 17668869    |
| 110.0.2.117     | 17431920    |
| 52.113.66.77    | 17305570    |
| 104.68.107.15   | 16975823    |
| 45.0.250.254    | 16450621    |
| 218.220.189.37  | 16356910    |
| 130.128.255.184 | 16183063    |
| 104.199.215.237 | 15738158    |
| 210.4.206.38    | 15366360    |
| 62.109.220.18   | 15265908    |
| 13.78.49.41     | 14862502    |
| 130.128.255.173 | 14783898    |
| 10.1.104.108    | 14699630    |
| 130.128.255.104 | 14652382    |
| 34.203.254.163  | 14187132    |
+-----------------+-------------+
Run Time (s): real 0.044 user 0.445385 sys 0.000000

Tracing

Various packet tracing

Matching Inner Packets

When searching for the same packet as it traverses a network, the following command will list such packets.

The following uses the https://docs.fmad.io/docs/features-metadata-reference#hashinner  column to correlated the inner payload

Replace: 18fc705e25327303910b1db101430228 with the actual hash_inner of the packet

SELECT
    make_timestamp_ns(ts_begin),
    hash_inner_str,
    entropy_inner,
    ipv4_0_flow,
    byte_wire
FROM read_pcap2parq('interop17_split_20170609_223953.717.953.280.parq')
WHERE hash_inner_str = '18fc705e25327303910b1db101430228'

Example output. Its likely an ACK of some kind of protocol.

┌───────────────────────────────┬──────────────────────────────────┬───────────────┬───────────────────────────────────────────────────────┬───────────┐
│  make_timestamp_ns(ts_begin)  │          hash_inner_str          │ entropy_inner │                      ipv4_0_flow                      │ byte_wire │
├───────────────────────────────┼──────────────────────────────────┼───────────────┼───────────────────────────────────────────────────────┼───────────┤
│ 2017-06-09 13:39:53.783211495 │ 18fc705e25327303910b1db101430228 │ 246           │ [TCP ]  10.  1. 28.114:50441 -> 211.  7.166. 14:22002 │ 750       │
│ 2017-06-09 13:39:53.7833821   │ 18fc705e25327303910b1db101430228 │ 246           │ [TCP ] 130.128.255. 28:50441 -> 211.  7.166. 14:22002 │ 746       │
│ 2017-06-09 13:39:53.900363607 │ 18fc705e25327303910b1db101430228 │ 246           │ [TCP ]  10.  1. 28.114:50441 -> 211.  7.166. 14:22002 │ 750       │
│ 2017-06-09 13:39:53.900606794 │ 18fc705e25327303910b1db101430228 │ 246           │ [TCP ] 130.128.255. 28:50441 -> 211.  7.166. 14:22002 │ 746       │
│ 2017-06-09 13:39:53.98851696  │ 18fc705e25327303910b1db101430228 │ 246           │ [TCP ]  10.  1. 28.114:50441 -> 211.  7.166. 14:22002 │ 750       │
│ 2017-06-09 13:39:53.989042839 │ 18fc705e25327303910b1db101430228 │ 246           │ [TCP ] 130.128.255. 28:50441 -> 211.  7.166. 14:22002 │ 746       │
│ 2017-06-09 13:39:54.100642477 │ 18fc705e25327303910b1db101430228 │ 246           │ [TCP ]  10.  1. 28.114:50441 -> 211.  7.166. 14:22002 │ 750       │
│ 2017-06-09 13:39:54.100898695 │ 18fc705e25327303910b1db101430228 │ 246           │ [TCP ] 130.128.255. 28:50441 -> 211.  7.166. 14:22002 │ 746       │
│ 2017-06-09 13:39:54.200051319 │ 18fc705e25327303910b1db101430228 │ 246           │ [TCP ]  10.  1. 28.114:50441 -> 211.  7.166. 14:22002 │ 750       │
│ 2017-06-09 13:39:54.200548125 │ 18fc705e25327303910b1db101430228 │ 246           │ [TCP ] 130.128.255. 28:50441 -> 211.  7.166. 14:22002 │ 746       │
└───────────────────────────────┴──────────────────────────────────┴───────────────┴───────────────────────────────────────────────────────┴───────────┘
Run Time (s): real 0.059 user 0.610440 sys 0.010733

Histogram of Packet Duplication

Generate a list of flows which have duplicate payload hashes. e.g. the inner payload is identical. 

duckdb  -c "select ipv4_0_flow,hex(hash_inner) as hash_inner_str,count(*) as dup from read_pcap2parq('test.parq') group by ipv4_0_flow,hash_inner order by dup desc limit 100"

PGM

Various PGM examples

PGM Flow Metrics

Generating PGM flow metrics per flow and PGM Type and PGM GSI

#/usr/local/bin/bash
duckdb -init /opt/fmadio/etc/duckdb/pcap2parq.sql <<EOF
SELECT
        ipv4_0_flow,
        pgm_0_type,
        pgm_0_gsi as pgm_0_gsi_str,
        sum(byte_wire) as byte,
        sum(pkt) as pkt
FROM read_pcap2parq('$1')
GROUP by ipv4_0_flow,pgm_0_type,pgm_0_gsi
ORDER by byte
;
EOF

Top 10 GSI

Generate list of top 10 GSI addresses

#/usr/local/bin/bash
duckdb -init /opt/fmadio/etc/duckdb/pcap2parq.sql <<EOF
SELECT
        pgm_0_gsi_str,
        ipv4_0_flow,
        sum(byte_wire) as byte,
        sum(pkt) as pkt
FROM read_pcap2parq('$1')
GROUP by pgm_0_gsi_str,ipv4_0_flow
ORDER by pkt DESC
LIMIT 10
;
EOF

List all Switch and Ports

Using LLDP and CDPv2 protocols, list all the switch and ports captured

#/usr/local/bin/bash
duckdb -init /opt/fmadio/etc/duckdb/pcap2parq.sql <<EOF
SELECT 
    printf('%s_%s', neighbor_0_switch_name, neighbor_0_port_name) as dev, 
    proto,count(*) 
FROM read_parquet('$1') 
WHERE neighbor_cnt > 0 
GROUP BY dev,proto 
ORDER BY dev
;
EOF

Example output

fmadio@fmadio200v45-828-2U:/mnt/store1/index/2026/07/24$ ./switchlist  ./**/*.pkt.parq
-- Loading resources from /opt/fmadio/etc/duckdbrc
Run Time (s): real 0.001 user 0.000748 sys 0.001122
┌────────────────────────────────────────────┬──────────────┬──────────────┐
│                    dev                     │    proto     │ count_star() │
├────────────────────────────────────────────┼──────────────┼──────────────┤
│ fmadsg0-tor220_0/42                        │ :ether:cdpv2 │ 1823         │
│ fmadsg0-tor220_0/43                        │ :ether:cdpv2 │ 1823         │
│ fmadsg0-tor220_0/44                        │ :ether:cdpv2 │ 1823         │
│ fmadsg0-tor220_0/45                        │ :ether:cdpv2 │ 1823         │
│ fmadsg0-tor220_0/46                        │ :ether:cdpv2 │ 1823         │
│ fmadsg0-tor220_0/47                        │ :ether:cdpv2 │ 1823         │
│ fmadsg0-tor220_0/48                        │ :ether:cdpv2 │ 1823         │
│ fmadsg0-tor220_0/49                        │ :ether:cdpv2 │ 3646         │
│ fmadsg0-tor420_0/45                        │ :ether:cdpv2 │ 1841         │
│ fmadsg0-tor420_0/47                        │ :ether:cdpv2 │ 1841         │
│ fmadsg0-tor420_0/51                        │ :ether:cdpv2 │ 1841         │
│ sg2-r0-sw-tor100(FDO29260G1Z)_Ethernet1/22 │ :ether:cdpv2 │ 923          │
│ sg2-r0-sw-tor100(FDO29260G1Z)_Ethernet1/28 │ :ether:cdpv2 │ 920          │
│ sg2-r0-sw-tor100(FDO29260G1Z)_mgmt0        │ :ether:cdpv2 │ 920          │
│ shu_Management1                            │ :ether:lldp  │ 1840         │
│ shu_Management2                            │ :ether:lldp  │ 1840         │
└────────────────────────────────────────────┴──────────────┴──────────────┘
Run Time (s): real 0.582 user 10.580317 sys 1.688420

FMADIO Packet Capture systems