-
-
Notifications
You must be signed in to change notification settings - Fork 57
Home
This is the official homepage for PacketQ, a simple tool to make SQL-queries agains PCAP-files, making packet-analysis and building statistics simple and quick. PacketQ was previously know as DNS2db but was renamed in 2011 when it was rebuilt and could handle other protocols than DNS among other things.
If you have any questions please post them on our mailing-list http://lists.iis.se/mailman/listinfo/packetq
Look how easy it's to count DNS-packets in a PCAP-file.
# packetq -s "select count(*) as count_dns from dns" packets.pcap
[
{
"table_name": "result",
"head": [
{ "name": "count_dns","type": "int" }
],
"data": [
[95501]
]
}
]
Read more in our FAQ and Examples section below.
- Super-fast native decoding of PCAP-files (even gzipped) and dirt-quick in-memory sorting algorithms.
- A extensible protocol decoding design with build in support for ICMP and DNS from start.
- Support for grouping, sorting, counting and most other important SQL-functions.
- Only dependent on zlib, no other hard-to-find libs needed. Compiles on everything.
- Build-in web-server, JSON API and a simple JQuery-based GUI concept application with graphs.
- Can be designed to cache queries by pre-processing PCAPs into static JSON-files that can be used to make queries again.
- Built in DNS-resolver function (used by GUI).
- Support for sampling. Helps when making queries to large, uniform PCAP-files.
- Can convert flags in packet-headers to text on the fly.
- Can make multiple queries towards the same data in memory.
See Functions
You can get XML, CSV, JSON or TABLE (CSV with spaces).
The short answer is no. Packet implements an SQL like select function with some notable omissions like:
- No 3 value logic i.e. theres no special NULL value
- No supports for joins or subqueries
- No support for the distinct keyword
- No support for like in where statements
Refer to the file grammar for supported statements.
Use this SQL-statement.
# packetq -s "select * from dns limit 3" packets.pcap
# packetq -s "select qname,count(*) as count from dns group by qname order by count desc limit 1 " packets.pcap
[
{ "table_name": "result",
"head": [
{ "name": "qname","type": "text" },
{ "name": "count","type": "int" }
],
"data": [ ["se.",2747] ]
}
]
# packetq -s "select src_addr,count(*) as count from dns group by src_addr order by count desc limit 1" packets.pcap
[
{
"table_name": "result",
"head": [
{ "name": "src_addr","type": "text" },
{ "name": "count","type": "int" }
],
"data": [
["::127.0.0.1",1849]
]
}
]
By using the sample-keyword before the actual query, it selects only every Xth packet and speeds up the query substantially. The amount of time saved by this command will be different on different systems, at some point the time it takes to read the file from disk will be longer than the time spent on processing. In other words, the full file will have to be read from disk regardless. This command also saved RAM.
# ./packetq -s "sample 1000; select count(*) from dns" ~/pcap/*
[ {
"table_name": "result",
"head": [ { "name": "count(1)","type": "int" }],
"data": [ [90000]] }]
Using the rsplit-function you can split values based on chars (like the dot). This query extract the two lowest levels (the domain for the .se-zone) and makes a top-10 list.
# packetq -s "select count(*) as Count, lower(rsplit(qname,2)||'.'||rsplit(qname,1)) as Domain from dns group by domain order by count desc limit 10" peak/07/G.ns.se-20110408-074500-em0.gz
[
{
"table_name": "result",
"head": [
{ "name": "Count","type": "int" },
{ "name": "Domain","type": "text" }
],
"data": [
[5130,"pp.se"],
[1686,"netnod.se"],
[1448,".se"],
[1318,"domainnetwork.se"],
[936,"ballou.se"],
[867,"loopia.se"],
[784,"ns.se"],
[736,"sonera.se"],
[694,"digidns.se"],
[639,"prq.se"]
]
}
]
PacketQ supports looking at packets to and from the nameserver/resolver. But since the client-IP changes from src_addr to dst_addr if the packet is an reply, we have to use the if-function to extract the client-IP.
# packetq -s "select count(*) as Count, if(qr=1,dst_addr,src_addr) as Source from dns group by source order by count desc limit 15" peak/07/G.ns.se-20110408-070000-em0.gz
[
{
"table_name": "result",
"head": [
{ "name": "Count","type": "int" },
{ "name": "Source","type": "text" }
],
"data": [
[1021,"202.67.195.41"],
[929,"91.191.143.34"],
[638,"195.84.206.2"],
[582,"202.101.173.146"],
[490,"2a02:aa0:0:10:214:4fff:fef2:61a0"],
[438,"80.251.201.177"],
[436,"192.89.123.231"],
[426,"81.228.8.143"],
[409,"81.228.8.69"],
[402,"65.54.237.136"],
[368,"81.228.8.144"],
[352,"213.50.29.170"],
[342,"217.28.204.2"],
[318,"2001:6f0:0:1::2:3"],
[297,"81.228.9.132"]
]
}
]
Yes, it does. See the above result.
If by broken you mean resolvers that are repeating the same query over and over again, then yes.
# packetq -s "select count(*) as Count,qname,if(qr=1,dst_addr,src_addr) as Source,qtype from dns group by qname,source,qtype order by count desc limit 10" peak/07/G.ns.se-20110408-070000-em0.gz
[
{
"table_name": "result",
"head": [
{ "name": "Count","type": "int" },
{ "name": "qname","type": "text" },
{ "name": "Source","type": "text" },
{ "name": "qtype","type": "int" }
],
"data": [
[607,"se.","195.84.206.2",48],
[375,"ns.digidns.se.","202.67.195.41",1],
[330,"sas.sas.se.","202.67.195.41",1],
[316,"NS.DIGIDNS.SE.","202.67.195.41",1],
[147,"se.","62.80.200.144",48],
[42,"se.","94.232.104.58",48],
[40,"_ldap._tcp.pdc._msdcs.telemgmt.se.","213.115.146.180",33],
[40,"se.","83.12.96.122",48],
[34,"www.membran.se.","217.73.15.38",1],
[28,"fc.edu.upplandsvasby.se.","193.12.6.247",1]
]
}
]
Yes, and PacketQ can even translate the headers into text for you by using the NAME-function.
# packetq -s "SELECT NAME( 'qtype' , qtype ) AS qt, COUNT(*) AS antal FROM dns GROUP BY qtype ORDER BY Antal DESC" peak/07/G.ns.se-20110408-070000-em0.gz
[
{
"table_name": "result",
"head": [
{ "name": "qt","type": "text" },
{ "name": "antal","type": "int" }
],
"data": [
["A",59908],
["AAAA",13076],
["MX",10527],
["DS",7545],
["NS",1666],
["DNSKEY",1077],
["TXT",969],
["A6",563],
["*",556],
["SRV",197],
["SPF",145],
["SOA",126],
["PTR",60],
["CNAME",47],
["NAPTR",3]
]
}
]
Absolutely! This query uses the TRUNCATE-keyword to split the stats into different timeperiods. In this case 10-second periods. This is great for line-graphs that you can make using Excel or similar tools. We then get the stats by using conditions like ((rcode=0) and (an_count=0)) to identify packets that resulted in referrals and (rcode=3) to see which packets got an nxdomain back and so on. By using the SUM-function they are all counted and since we want q/sec as our output, divided by 10. Try to import the result below into Excel!
# packetq --table -s "select s as timestamp, count(*)/10 as total, sum((rcode=0) and (an_count=0))/10 as referral, sum(rcode=3)/10 as nxdomain, sum((rcode=0) and (an_count>0))/10 as success, sum(rd)/10 as recursion, sum(ether_type=34525)/10 as IPV6, sum(protocol=6)/10 as TCP from dns group by truncate(s/10) " peak/07/G.ns.se-20110408-071000-em0.gz
"timestamp","total" ,"referral","nxdomain" ,"success","recursion","IPV6" ,"TCP"
1302246609 ,822.500000 ,304.300000,515.500000 ,2.000000 ,527.000000 ,15.300000,0.000000
1302246619 ,1135.900000,330.800000,797.800000 ,6.000000 ,817.200000 ,16.700000,0.000000
1302246629 ,1475.200000,317.800000,1152.100000,4.400000 ,1170.000000,17.900000,0.000000
1302246639 ,1774.700000,344.900000,1424.800000,3.800000 ,1461.400000,14.200000,0.000000
1302246649 ,2014.400000,350.600000,1658.500000,4.400000 ,1712.400000,15.900000,0.000000
1302246659 ,2136.400000,346.700000,1786.200000,2.500000 ,1834.900000,15.400000,0.000000
1302246669 ,2212.100000,336.000000,1872.000000,3.000000 ,1918.300000,16.100000,0.100000
1302246679 ,2292.400000,336.700000,1952.600000,2.100000 ,1999.700000,16.400000,0.000000
1302246689 ,2337.000000,324.200000,2004.600000,6.800000 ,2052.600000,13.800000,0.000000
1302246699 ,2410.800000,315.900000,2084.700000,9.100000 ,2121.700000,12.300000,0.000000
1302246709 ,2437.500000,311.900000,2113.000000,12.000000,2151.300000,14.200000,0.000000
1302246719 ,2488.100000,302.800000,2178.500000,5.700000 ,2214.800000,13.800000,0.000000
1302246729 ,2496.000000,327.400000,2163.600000,3.800000 ,2202.500000,15.000000,0.000000
1302246739 ,2546.700000,333.300000,2200.200000,11.600000,2234.900000,16.700000,0.000000
1302246749 ,2473.300000,314.100000,2150.400000,7.900000 ,2180.800000,15.600000,0.000000
1302246759 ,2476.200000,313.400000,2146.800000,14.500000,2175.300000,12.000000,0.000000
1302246769 ,2472.000000,324.900000,2133.200000,12.800000,2159.400000,14.300000,0.000000
1302246779 ,2464.800000,337.600000,2120.400000,5.700000 ,2156.700000,14.600000,0.000000
1302246789 ,2443.900000,332.400000,2107.800000,2.900000 ,2141.500000,14.400000,0.000000
1302246799 ,2447.600000,323.600000,2119.800000,2.800000 ,2161.900000,14.900000,0.000000
1302246809 ,2477.900000,324.000000,2150.700000,1.700000 ,2184.200000,15.100000,0.000000
1302246819 ,2440.200000,321.900000,2114.100000,3.100000 ,2148.000000,14.900000,0.100000
1302246829 ,2478.000000,312.600000,2162.400000,2.400000 ,2199.500000,15.000000,0.000000
1302246839 ,2537.300000,319.100000,2214.600000,2.300000 ,2260.300000,16.300000,0.000000
1302246849 ,2654.500000,304.200000,2348.100000,1.300000 ,2382.500000,15.600000,0.000000
1302246859 ,2669.800000,318.600000,2347.800000,2.900000 ,2381.800000,15.700000,0.000000
1302246869 ,2707.600000,328.200000,2375.800000,2.800000 ,2414.900000,15.100000,0.000000
1302246879 ,2792.700000,309.300000,2480.500000,1.800000 ,2501.300000,16.700000,0.000000
1302246889 ,2841.300000,299.700000,2532.700000,7.800000 ,2559.400000,16.300000,0.000000
1302246899 ,2857.900000,312.800000,2531.700000,12.500000,2564.400000,16.400000,0.000000