Home > Uncategorized > Effectively parsing PCAPs with python’s querycsv

Effectively parsing PCAPs with python’s querycsv

Here is a quick method I used today, before implementing an argus probe in a sustainable way, to create and parse PCAPs to determine high bandwidth offends.

Create PCAPs:

ifconfig eth0 promisc
netstat -i | grep eth #check for the P flag
mkdir /pcaps/
nohup tcpdump -i 1 -w /pcaps/pcap -C 20 -W 250 'host 192.168.100.27' -Z root & #not really secure, look at -Z

Note that querycsv.py/sqlite3.py doesn’t like _underscores_, -dashes-, and files that contains only numbers.

This will generate /pcaps/pcapNNN…

Process PCAPs on a Windows box with tshark.exe:

echo echo ipsrc,ipdst,dstporttcp,srcporttcp,len ^> %1.csv > %temp%\tshark_len.bat
echo ".\Wireshark\tshark.EXE" -r %1 -T fields -e ip.src -e ip.dst -e tcp.dstport -e tcp.srcport -e frame.len -E separator=, -E quote=d ^>^> %1.csv >> %temp%\tshark_len.bat
for /r . %G in (pcap*) do %temp%\tshark_len.bat %G

`tcpdump` has relatively the same syntax.

Query CSVs with SQL statements:
Use a python module to quickly return calculations (http://pythonhosted.org/querycsv/):

pip install querycsv

Per-file operations
Find the total bytes incoming to host:

querycsv.py -i test.csv "select ipsrc, ipdst, dstporttcp, dstporttcp, srcporttcp, sum(len) as 'len_in_bytes' from test group by ipsrc"


Find the total bytes outgoing from host:

querycsv.py -i test.csv "select ipsrc, ipdst, dstporttcp, dstporttcp, srcporttcp, sum(len) as 'len_in_bytes' from test group by ipdst"

For all files:

echo ipsrc,ipdst,dstporttcp,srcporttcp,len > mergedpcap.csv
cat *.csv | grep -v "ipsrc,ipdst,dstporttcp,srcporttcp,len" >> mergedpcap.csv

Find the total bytes incoming to host:

querycsv.py -i mergedpcap.csv "select distinct dstporttcp, ipsrc, ipdst, sum(len) as 'len_in_bytes' from mergedpcap group by ipdst" > incoming.log
[/source ]
The record where ipsrc is the targeted host (in this case 192.168.100.27), will return the TOTAL length of all packets sent from the targeted host.  (all uploadeded, yes UPloaded, bytes)


Find the total bytes outgoing from host:

querycsv.py -i mergedpcap.csv "select distinct dstporttcp, ipsrc, ipdst, sum(len) as 'len_in_bytes' from mergedpcap group by ipdst" > outgoing.log

The record where ipdst is the targeted host (in this case 192.168.100.27), will return the TOTAL length of all packets sent to the targeted host. (all downloaded, yes DOWNloaded, bytes)

Other solutions:
You’ll notice that querycsv first imports the csv data to an in memory sqlite3 db. This makes offering a full set of sql queries and functions trivial.

There exists other options to solve this sort of situation:
1) PCAP to SQL platforms: pcap2sql and c5 sigma.
2) SQL querying PCAP directly: PacketQ (which lacks some SQL queries and functions, see here). Here is a neat example of displaying some results.
3) robust solutions like pcapr-local, with integration to mu studio.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: