<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/reading-pcap-files-with-apache-drill-and-the-sergeant-r-package/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://rud.is/b/2017/07/27/reading-pcap-files-with-apache-drill-and-the-sergeant-r-package/"> R – rud.is</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>It’s no secret that I’m a fan of <a rel="nofollow" href="https://drill.apache.org/" target="_blank">Apache Drill</a>. One big strength of the platform is that it normalizes the access to diverse data sources down to ANSI SQL calls, which means that I can pull data from parquet, Hie, HBase, Kudu, CSV, JSON, MongoDB and MariaDB with the same SQL syntax. This also means that I get access to all those platforms in R centrally through the <a rel="nofollow" href="https://github.com/hrbrmstr/sergeant" target="_blank"><code>sergeant</code></a> package that rests atop <code>d[b]plyr</code>. However, it <em>further</em> means that when support for a new file type is added, I get that same functionality without any extra effort.</p>
<p>Why am I calling this out?</p>
<p>Well, the intrepid Drill developers are in the process of finalizing the <a rel="nofollow" href="https://home.apache.org/~arina/drill/releases/1.11.0" target="_blank">release candidate for version 1.11.0</a> and one feature they’ve added is the ability to query individual and entire directories full of <a rel="nofollow" href="https://en.wikipedia.org/wiki/Pcap" target="_blank">PCAP files</a> from within Drill. While I provided a link to the Wikipedia article on PCAP files, the TL;DR on them is that it’s an optimized binary file format for recording network activity. If you’re on macOS or a linux-ish system go do something like this:</p>
<p><code>sudo tcpdump -ni en0 -s0 -w capture01.pcap</code></p>
<p>And, wait a bit.</p>
<p>NOTE: Some of you may have to change the <code>en0</code> to your main network interface name (a quick google for that for your platform should get you to the right one to use).</p>
<p>That command will passively record all network activity on your system until you <code>ctrl-c</code> it. The longer it goes the larger it gets.</p>
<p>When you’ve recorded a minute or two of packets, <code>ctrl-c</code> the program and then try to look at the PCAP file. It’s a binary mess. You can re-read it with <code>tcpdump</code> or <a rel="nofollow" href="https://www.wireshark.org/" target="_blank">Wireshark</a> and there are many C[++] libraries and other utilities that can read them. You can even convert them to CSV or XML, but the PCAP itself requires custom tools to work with them effectively. I had started creating <a rel="nofollow" href="https://github.com/hrbrmstr/crafter" target="_blank"><code>crafter</code></a> to work with these files but my use case/project dried up and haven’t gone back to it.</p>
<p>Adding the capability into Drill means I don’t really have to work any further on that specialized package as I can do this:</p>
<pre><code>library(sergeant)
library(iptools)
library(tidyverse)
library(cymruservices)

db &lt;- src_drill(&quot;localhost&quot;)

my_pcaps &lt;- tbl(db, &quot;dfs.caps.`/capture02.pcap`&quot;)

glimpse(my_pcaps)
## Observations: 25
## Variables: 12
## $ src_ip          </code></pre>