1 / 15

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

ryo
Download Presentation

Capturing Network Traffic into Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Capturing Network Traffic into Database Key Words: Sniffer, Network Analyzer, Wireshark, MySQL, Database, PCAP to MySQL

  2. 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

  3. How???

  4. What do we need? • tshark (supplied with WireShark) • PHP with XML, Xpath support • MySQL database

  5. 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.

  6. 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).

  7. Distributed Remote SQL Server WorkStation Remote SQL Server Remote SQL Server WorkStation WorkStation

  8. 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

  9. Output XML example

  10. 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.

  11. 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.

  12. 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)

  13. 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 }

  14. And putting into database function 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.

  15. Thank you • I hope you find this useful. • Full code is available at http://tigrantsat.me/randd/pcaptomysql/

More Related