SANS ISC: Select Star from PCAP - Treating Packet Captures as Databases - SANS Internet Storm Center

• SANS Site Network
  • Current Site
  • Internet Storm Center
  • Other SANS Sites Help
  • Graduate Degree Programs
  • Security Training
  • Security Certification
  • Security Awareness Training
  • Penetration Testing
  • Industrial Control Systems
  • Cyber Defense Foundations
  • DFIR
  • Software Security
  • Government OnSite Training

SANS ISC InfoSec Forums

Have you ever had to work with a large packet capture, and after getting past the initial stage of being overwhelmed by a few million packets, find that are still a bit overwhelmed?

I quite often work with large PCAPs - either for long-running issues, or intermittent issues, or because the capture filter that was defined might not have been all that it should be  :-)
After 4 or 5 passes through Wireshark or TCPDump (maybe with grep thrown in), where you have to wait 2 or 3 or 10 minutes each time for results, you start to say - "gee, this might be a bit easier if I could just use SQL instead of BPF or grep to query this data".  Well, it turns out that you can do that, if that's your preference.

PCAP2XML will dump standard PCAP files to XML or SQLite format.

Let's start with a sample PCAP file, I did a simple 1 minute capture of my "home / family" wireless network (not my home / work network), and got a 96 packet capture file.  Using a few examples in PCAP2XML's website, let's see what we find.

First, export the file.  Note that the offset is required (it should be zero for most cases), or the export will error out.  In this example I'm exporting to sql (use -x for xml)

C:\tmp> Pcap2XML.exe sample1.pcap -s sample.sql --offset 0
.______     ______     ___      .______    ___   ___   ___ .___  ___.  __
|   _  \   /      |   /   \     |   _  \  |__ \  \  \ /  / |   \/   | |  |
|  |_)  | |  ,----'  /  ^  \    |  |_)  |    ) |  \  V  /  |  \  /  | |  |
|   ___/  |  |      /  /_\  \   |   ___/    / /    >   <   |  |\/|  | |  |
|  |      |  `----./  _____  \  |  |       / /_   /  .  \  |  |  |  | |  `----.
| _|       \______/__/     \__\ | _|      |____| /__/ \__\ |__|  |__| |_______|

                          ver 1.0 by Pentester Academy
                   Info: http://PentesterAcademy.com/pcap2xml
          A tool to convert 802.11 trace files to XML and SQLite DB format
                    Ver. 1.0 only supports WLAN MAC Header

[+] Opening file: sample1.pcap (9.0 kB)
[+] Processing packet 96... (100.00 %)
[+] Parsing completed
[+] Dumping into XML and/or SQLite
[+] Processing done!
[+] Run statistics:

Filename:                       sample1.pcap
Number of packets:              96
Number of packets parsed:       96
Data packets parsed:            45
Control packets parsed:         41
Management packets parsed:      10
SQLite out file:                sample.sql
Total time taken:               0.039 sec
[-] No update available. This is the latest version

Now, let's open the file in sqlite browser

OH - as my cat would say - this is as good as catnip!!

Let's list the unique sending and receiving mac addresses on the network

Adding counts for each, sorted in descending order by packet count - OK, maybe this is BETTER than catnip !

Average packet length?  No problem!

OK, for a 96 packet PCAP, thsi might not be your first go-to tool for analysis.  But if the file was 96,000 or 960,000 packets?  Maybe now using a database approach makes a bit more sense now, at least to start narrowing things down?  

You can download PCAP2XML and read more about it here:

https://github.com/securitytube/pcap2xml

http://hackoftheday.securitytube.net/2015/03/pcap2xmlsqlite-convert-80211-packets-to.html

 

===============
Rob VandenBrink
Metafore

Wow! Thanks! I usually do this with a pipeline of grep, awk, sort, uniq, et al, even breaking out in fluent perl script one-liners when the need arises, but you still gotta slough thru the whole stinkin file every time. Nice tool!

Nice finding, added to my bookmarks!
Often, I'm also using bro to parse the pcap file. The "conn.log" gives you useful information to start debugging. Ok, grep is still used but you can get quickly interesting stats.
Ex: top-10 of source IP addresses from the PCAP file:

$ cat conn.log | awk '{ print $3 }' | sort -urn | head -10

/x

Bro absolutely rules!

Great post!