capturing network traffic into database

Capturing Network Traffic into Database

Key Words: Sniffer, Network Analyzer, Wireshark, MySQL, Database, PCAP

to MySQL

How to Store Packets into Database (for example, MySQL)

• Having packets in database can be very convenient:– More performance– Parallel writing– Quick analysis– Data Mining (if you want)– Long time storage

How???

PCAP (or real-time

capturing)XML output MySQL

What do we need?

• tshark (supplied with WireShark)• PHP with XML, Xpath support• MySQL database

My workstation

• All examples here are done in Windows 7, but if you want, you will not need a lot of time to make them for Linux

• Our task: capture TCP packets (IP from, IP to, port from, port to, length, sequence) into database. Example can be any, for instance, checking for network scanning.

Distributed

• This can be distributed, no problem, but you need to use extra network or filters (otherwise, you will hang your system: 1 sniffed packet sent make 1 more, and so on).

Distributed

WorkStation

WorkStation

WorkStation

Remote SQL Server

Remote SQL Server

Remote SQL Server

Getting traffic XML format

• tshark -r "D:\test.pcap" -T pdml > D:\test_T.xml– Converting pcap into XML

Or• tshark -T pdml | you_application.exe– Real-Time

Output XML example

Warning

• Such converting to XML consume a lot of space (50x)! PCAP file from 200 Kb grew into 10 Mb XML!!!

• In this case you might find useful to divide one big pcap file into several of smaller size

• Also filtering is good idea, so you can throw out fields useless for you.

XML output file structure

• It is very simple (I crossed out trivial parts, so real lines are bigger):

<?xml version="1.0"?><?xml-stylesheet type="text/xsl" href="pdml2html.xsl"?><pdml >

<packet> Packet info </packet>

<packet> Packet info

</packet>And so on.

XML structure – packets (Example, DNS query)

<packet> <proto name="geninfo" pos="0" showname="General information" size="73"> </proto> <proto name="frame" showname="Frame 1: 73 bytes on wire (584 bits), 73 bytes captured (584 bits)" size="73" pos="0"> </proto> <proto name="eth"> </proto> <proto name="ip" > </proto> <proto name="udp" showname="User Datagram Protocol, Src Port: 58150 (58150), Dst Port: domain (53)" size="8" pos="34"> </proto> <proto name="dns" showname="Domain Name System (query)" size="31" pos="42">

</proto></packet>(Child elements and attributes of proto are not shown here)

XML to MySQL

• You can use LOTS of options: C++/Java,etc.• I used SimpleXML and XPath with PHP: $file = "test_T.xml";

$my_file = simplexml_load_file($file );foreach ($my_file >xpath('//packet') as $packet)

{$packet_type = $packet->proto[4];echo $packet_type['name']; //protocol

}

And putting into databasefunction LoadToDataBase($con){

$stmt =$con->prepare("INSERT INTO tcp (capture_order, from_ip, to_ip, from_port, to_port, tcp_length, tcp_stream, tcp_stream_text, tcp_sequence_dec) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");

$stmt->bind_param('sssiiiisi', $this->capture_order,$this->from_ip, $this->to_ip, $this->from_port,$this->to_port,$this->tcp_length,$this->tcp_stream, $this->tcp_stream_text, $this->tcp_sequence_dec);

$stmt->execute();}Here $con is open connection to mysql, and all this vars I got in cycle. Please, refer to full code.

Thank you

• I hope you find this useful.

• Full code is available at http://tigrantsat.me/randd/pcaptomysql/