{"id":22211,"date":"2024-08-26T07:37:28","date_gmt":"2024-08-26T12:37:28","guid":{"rendered":"https:\/\/rud.is\/b\/?p=22211"},"modified":"2024-08-30T03:03:25","modified_gmt":"2024-08-30T08:03:25","slug":"reading-pcap-files-directly-with-duckdb","status":"publish","type":"post","link":"https:\/\/rud.is\/b\/2024\/08\/26\/reading-pcap-files-directly-with-duckdb\/","title":{"rendered":"Reading PCAP Files (Directly) With DuckDB"},"content":{"rendered":"
\"\"<\/a>

Photo by Vincent van Zalinge<\/a> on Unsplash<\/a><\/p><\/div>\n

2024-08-30 UPDATE:
\nBinary versions of this extension are available for amd64 Linux (linux_amd64<\/code> & linux_amd64_gcc4<\/code>) and Apple Silicon. (osx_arm64<\/code>).<\/p>\n

$ duckdb -unsigned\nv1.0.0 1f98600c2c\nEnter \".help\" for usage hints.\nConnected to a transient in-memory database.\nUse \".open FILENAME\" to reopen on a persistent database.\nD SET custom_extension_repository='https:\/\/w3c2.c20.e2-5.dev\/ppcap\/latest';\nD INSTALL ppcap;\nD LOAD ppcap;\n<\/code><\/pre>\n
2024-08-29 UPDATE: The Apple Silicon macOS and Linux AMD64 versions of the plugin now work with PCAP files that are “Raw IP” vs. just “Ethernet<\/p>\n
We generate a ton<\/em> of PCAP files at $DAYJOB<\/code>. Since I do not always have to work directly with them, I regularly mix up or forget the various tshark<\/code>, tcpdump<\/code>, etc., filters and CLI parameters. While this is less of an issue in the age of LLM\/GPTs (just ask local ollama to gen the CLI incantation, and it usually does a good job), each failed command makes me miss Apache Drill just a tad, since it had\/has a decent, albeit basic, PCAP reading capability.<\/p>\n
For the past few months, I’ve had an “I should build a DuckDB extension to read PCAP files” idea floating in the back of my mind. Thanks to lingering issues from long covid, I’m back in the “let’s wake him up at 0-dark-30 and not let him get back to sleep” routine, so I decided to try to scratch this itch (I was actually hoping super focused work would engender slumber, but that, too, was a big fail).<\/p>\n
The DuckDB folks have a spiffy extension template<\/a> that you can use\/fork to get started. It’s been a minute since I’ve had to work in C++ land, and I’m also used to working with system-level, or vendored libraries when doing said work. So, first I had to figure out vcpkg<\/a> \u2014 a C\/C++ dependency manager from (ugh) Microsoft \u2014 as the DuckDB folks strongly encourage using it (and they use it). You likely do not have to get in the weeds, since there are three lines in the extension template<\/a> that are (pretty much) all you really need to know\/do.<\/p>\n
Once that was done, I added libpcap<\/code> to the DuckDB vcpkg deps<\/a>. Then, a review of the structure of the example extension and the JSON, CSV, and Parquet reader extensions was in order to get a feel for how to add new functions, and return rectangular data from an entirely new file type.<\/p>\n
To get started, I focused on some easy fields: source\/destination IPs, timestamp, and payload length and had some oddly great success. So, of course, I had to start a Mastodon thread<\/a>.<\/p>\n
The brilliant minds at DuckDB truly made it pretty straightforward to work with list\/array columns, and write new utility functions, so I just kept adding fields and functionality until time ran out (adulting is hard).<\/p>\n
At present, the extension exposes the following fields from a PCAP file:<\/p>\n