Download - Capturing Network Traffic into Database
![Page 1: Capturing Network Traffic into Database](https://reader036.vdocuments.us/reader036/viewer/2022081422/558432aad8b42a77068b4733/html5/thumbnails/1.jpg)
Capturing Network Traffic into Database
Key Words: Sniffer, Network Analyzer, Wireshark, MySQL, Database, PCAP
to MySQL
![Page 2: Capturing Network Traffic into Database](https://reader036.vdocuments.us/reader036/viewer/2022081422/558432aad8b42a77068b4733/html5/thumbnails/2.jpg)
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
![Page 3: Capturing Network Traffic into Database](https://reader036.vdocuments.us/reader036/viewer/2022081422/558432aad8b42a77068b4733/html5/thumbnails/3.jpg)
How???
PCAP (or real-time
capturing)XML output MySQL
![Page 4: Capturing Network Traffic into Database](https://reader036.vdocuments.us/reader036/viewer/2022081422/558432aad8b42a77068b4733/html5/thumbnails/4.jpg)
What do we need?
• tshark (supplied with WireShark)• PHP with XML, Xpath support• MySQL database
![Page 5: Capturing Network Traffic into Database](https://reader036.vdocuments.us/reader036/viewer/2022081422/558432aad8b42a77068b4733/html5/thumbnails/5.jpg)
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.
![Page 6: Capturing Network Traffic into Database](https://reader036.vdocuments.us/reader036/viewer/2022081422/558432aad8b42a77068b4733/html5/thumbnails/6.jpg)
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).
![Page 7: Capturing Network Traffic into Database](https://reader036.vdocuments.us/reader036/viewer/2022081422/558432aad8b42a77068b4733/html5/thumbnails/7.jpg)
Distributed
WorkStation
WorkStation
WorkStation
Remote SQL Server
Remote SQL Server
Remote SQL Server
![Page 8: Capturing Network Traffic into Database](https://reader036.vdocuments.us/reader036/viewer/2022081422/558432aad8b42a77068b4733/html5/thumbnails/8.jpg)
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
![Page 9: Capturing Network Traffic into Database](https://reader036.vdocuments.us/reader036/viewer/2022081422/558432aad8b42a77068b4733/html5/thumbnails/9.jpg)
Output XML example
![Page 10: Capturing Network Traffic into Database](https://reader036.vdocuments.us/reader036/viewer/2022081422/558432aad8b42a77068b4733/html5/thumbnails/10.jpg)
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.
![Page 11: Capturing Network Traffic into Database](https://reader036.vdocuments.us/reader036/viewer/2022081422/558432aad8b42a77068b4733/html5/thumbnails/11.jpg)
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.
![Page 12: Capturing Network Traffic into Database](https://reader036.vdocuments.us/reader036/viewer/2022081422/558432aad8b42a77068b4733/html5/thumbnails/12.jpg)
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)
![Page 13: Capturing Network Traffic into Database](https://reader036.vdocuments.us/reader036/viewer/2022081422/558432aad8b42a77068b4733/html5/thumbnails/13.jpg)
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
}
![Page 14: Capturing Network Traffic into Database](https://reader036.vdocuments.us/reader036/viewer/2022081422/558432aad8b42a77068b4733/html5/thumbnails/14.jpg)
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.
![Page 15: Capturing Network Traffic into Database](https://reader036.vdocuments.us/reader036/viewer/2022081422/558432aad8b42a77068b4733/html5/thumbnails/15.jpg)
Thank you
• I hope you find this useful.
• Full code is available at http://tigrantsat.me/randd/pcaptomysql/