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.

Flattr this
DNS, JSON, XML, SQL, pcap, and CSV :: 23 May 2011 :: e-mail

Comments

blog comments powered by Disqus