--- title: "Examples" slug: "features-metadata-examples" updated: 2026-07-24T15:31:18Z published: 2026-07-24T15:31:18Z canonical: "docs.fmad.io/features-metadata-examples" --- > ## 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 ## Environment Duckdb has a power macro expansion function that allows expanding a table functionality significantly. FMADIO Macro file is located here [pcap2parq.sql](https://firmware.fmad.io/download/pcap2parq/pcap2parq.sql) Best to copy into ```plaintext mkdir /opt/fmadio/etc/duckdb cp pcap2parq.sql /opt/fmadio/etc/duckdb ``` Or use curl ```plaintext 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 ```plaintext nano top50.sql ``` Paste the contents + save ### Step 2) Make executable Make the binary executable ```plaintext chmod uga+rwx ./top50.sql ``` ### Step 3) Run with a .parq file Execute with a PARQ file ```plaintext ./top50  interop17_split_20170609_223953.717.953.280.parq ``` Example output ```plaintext 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 ```sql #/usr/local/bin/bash duckdb -init /opt/fmadio/etc/duckdb/pcap2parq.sql < 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 ```sql #/usr/local/bin/bash duckdb -init /opt/fmadio/etc/duckdb/pcap2parq.sql < 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. ```plaintext 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 ```sql #/usr/local/bin/bash duckdb -init /opt/fmadio/etc/duckdb/pcap2parq.sql < 0 GROUP BY dev,proto ORDER BY dev ; EOF ``` Example output ```plaintext 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 ```