import pcap files into database

Question

Would really appreciate if somebody could help me with this. I have some Wireshark / pcap files - about 30 files. I need to analyze them for malicious activity. Whether the network was compromised in any way. What is the best way to search through the files? I was thinking of loading them into a database. I know that the pcap files will need to be converted to csv format before they can be imported into a database.

What is a better database to use between Microsoft SQL or mySQL? Basically which database is easier for importing CSV files into?

jonschipp jonschipp · Accepted Answer · 2014-04-02T02:54:05

I'm unable to answer your database specific questions but I can give advice on how to analyze the capture files and how to get them into a database using existing popular solutions.

If you would just like signature based alerts I would recommend reading your PCAP files into a IDS such as Snort or Suricata with Barnyard to output to a database backend. Web front ends exist like Snorby and Squert to search and classify the alerts. It's easy to read in PCAP files with many popular IDS engines e.g.

$ snort -r traffic.pcap

If you just want flow like data but with some application layer decoding I recommend using Bro to output CSV or it's default TSV (tab-separated) format which can be easily stored in a database and provides a lot of information.

Bro writes its decoded traffic to individual log files of protocol name e.g. dns.log, http.log.

$ bro -r traffic.pcap
$ head http.log
1216691479.339424   kfuZwhwI5c6 192.168.1.64    41607   65.175.87.70    80  1   GET e.drugstore.com /a/hBIhP7YAbeh5-B7SEoEBNJqOT.AcGxgqbm/spacer.gif    -   Mozilla/5.0 (Macintosh; U; PPC Mac OS X 10_5_4; en-us) AppleWebKit/525.18 (KHTML, like Gecko)   0   43  200 OK  -   (empty) -   -   -   image/gif   -   -
$ head dns.log
1216691468.360749   MCshRYLiesf 192.168.1.64    20128   192.168.1.254   53  udp 3217    ssl.google-analytics.com    1   C_INTERNET  NOERROR F   F   T   T   0   ssl-google-analytics.l.google.com,209.85.171.97 26636.000000,65.000000
$ head ssl.log
1216691467.672054   NdRPIIlKZaa 192.168.1.64    34050   74.125.19.103   443 TLSv10  TLS_RSA_WITH_RC4_128_SHA    www.google.com  9fea36dc5f2dc0d7bbfac02cec7595cf130f638a69a671801be670353be0c687    -   -   -   -   -
$ head files.log
1396403999.886276   FIvzWp1ZUUnNJD9i6   192.168.1.64    65.175.87.70    CtuART1AUxrAifTqd4  HTTP    0   SHA1,MD5    image/jpeg  -   9.956452    F   F   728731  728731  0   0   F   -   8cbf8f2e2713629fcd3ade0965e5e1f9    6ebfa114d86191eecb725c14f98b7c2a24a0cfa0    -

To write the output in CSV you can set Bro's field separator like this:

$ bro -r day1.pcap 'LogAscii::separator = ",";'
$ head http.log

1216691479.339424,SVZC7821ith,192.168.1.64,41607,65.175.87.70,80,1,GET,e.drugstore.com,/a/hBIhP7YAbeh5-B7SEoEBNJqOT.AcGxgqbm/spacer.gif,-,Mozilla/5.0 (Macintosh; U; PPC Mac OS X 10_5_4; en-us) AppleWebKit/525.18 (KHTML\x2c like Gecko),0,43,200,OK,-,-,-,(empty),-,-,-,image/gif,-,-

The ultimate easy solution in my opinion is to install SecurityOnion in a Virtual Machine, go through the setup wizard, and then replay the set of PCAP's on the network interface.

$ tcpreplay -i eth0 *.pcap

This will provide you with everything listed above but more out of the box:

IDS analysis with Snort or Suricata, stored in a MySQL database, and searchable via web frontends: Squert, Snorby, and ELSA
Bro logs written to /nsm/bro/logs and searchable in the ELSA front end
Decoded sessions (where applicable) and asset information stored in a MySQL database, searchable through Sguil, and pivotable into Wireshark or Network Minor for closer examination.
Ability to have Bro extract files from the network streams for forensic analysis

And lastly, there's Moloch, an open source IPv4 full PCAP capturing, indexing and database system. I have not used Moloch but it looks like it will solve your problem.

import pcap files into database

2 Answers