I was introduced to PacketQ today, the artist formerly known as DNS2db. PacketQ takes a pcap file containing DNS or ICMP traffic and provdes an interface to query that pcap file with an SQL dialect. Let me quickly show you what I’ve learned. I’ll first take a sampling of DNS traffic with tcpdump, and I’ll store that in a file:
tcpdump -n -s 1500 -w file.pcap -i en1 port 53
After installing PacketQ, I can run some queries on the packet capture file, using one of the provided examples:
$ packetq -c \
-s "SELECT NAME( 'qtype' , qtype ) AS qt, COUNT(*) AS count \
FROM dns GROUP BY qtype ORDER BY count DESC" pcap/file.pcap
"qt","count"
"A",1165
"PTR",394
"AAAA",381
"SRV",89
"TXT",73
"SOA",30
"MX",28
"DNSKEY",10
"SSHFP",4
The -c switch gives me CSV (XML is also available, and the default
is JSON). So, removing -c (or changing it to -j) gives me the
corresponding JSON output:
[
{
"table_name": "result",
"head": [
{ "name": "qt","type": "text" },
{ "name": "count","type": "int" }
],
"data": [
["A",1165],
["PTR",394],
["AAAA",381],
["SRV",89],
["TXT",73],
["SOA",30],
["MX",28],
["DNSKEY",10],
["SSHFP",4]
]
}
]
If I launch packetq and give it a port, a HTML directory and a path
to a bunch of pcap files, it behaves as a server, and will answer HTTP
requests on the given port. I’ve also populated the directories with a few
files:
$ packetq -p 8090 -w html/ -r pcap/
$ find html -type f
html/dnstypes.html
html/jquery.flot.js
html/jquery.js
$ find pcap -type f
pcap/file.pcap
Now let me point a Web browser at localhost:8090/dnstypes.html. This
file is served up by PacketQ. Therein, I’m using a bit of jQuery and
a bit of the wonderful Flot to produce the following graph directly from
the JSON made by PacketQ: 
Wow. If I were more than just a copy/paste
Web 2.0 developer I would have probably completed it in just a few minutes,
instead of hacking at it for an hour. Here is my dnstypes.html file, which
is mostly a copy of one of Flot’s examples, though I had to massage PacketQ’s
JSON output to make Flot happy:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>packetq: DNS qtypes</title>
<script language="javascript" type="text/javascript" src="jquery.js"></script>
<script language="javascript" type="text/javascript" src="jquery.flot.js"></script>
</head>
<body>
<div id="placeholder" style="width:600px;height:300px;"></div>
<p>
<input class="dataUpdate" type="button" value="Refresh">
</p>
<script type="text/javascript">
$(function () {
$("input.dataUpdate").click(function () {
function fetchData() {
function onDataReceived(series) {
var data = [];
var i = 0;
$.each(series.data, function(key, val) {
var elem = {
label: val[0], // A PTR SRV ...
data: [[i++, val[1]]]
};
data.push(elem);
});
$.plot($("#placeholder"), data, {
// lines: { show: true },
bars: { show: true },
points: { show: false },
yaxis: { min: 0, show: true },
xaxis: { tickDecimals: 0, show: false},
grid: { hoverable: true, clickable: true },
});
}
var url = "http://127.0.0.1:8090/query?file=file.pcap&sql=SELECT NAME( 'qtype' , qtype ) AS qt, COUNT(*) AS count FROM dns GROUP BY qtype ORDER BY count DESC;";
$.ajax({
url: encodeURI(url),
method: 'GET',
dataType: 'json',
success: onDataReceived
});
}
fetchData();
});
});
</script>
</body>
</html>
PacketQ is definitely something for I’m keeping in my DNS toolbox.