How To Capture network packets to MySQL

Question

I'm going to design a network Analyzer for WiFi (802.11) Currently I use tshark to capture and parse the WiFi frames and then pipe the output to a perl script to store the parsed information to Mysql database.

I just find out that I miss alot of frames in this process. I checked and the frames seem to be lost during the Pipe (when the output is delivered to perl to get srored in Mysql) Here is how it goes

(Tshark) -------frames are lost----> (Perl) --------> (MySQL) this is the how I pipe the output of tshark to script:

sudo tshark -i mon0 -t ad -T fields -e frame.time -e frame.len -e frame.cap_len -e radiotap.length | perl tshark-sql-capture.pl

this is simple template of the perl script I use (tshark-sql-capture.pl)

# preparing the MySQL
my $dns = "DBI:mysql:capture;localhost";
my $dbh = DBI->connect($dns,user,pass);
my $db = "captured";

while (<STDIN>) {
    chomp($data = <STDIN>);
    ($time, $frame_len, $cap_len, $radiotap_len) = split "  ", $data;
    my $sth = $dbh-> prepare("INSERT INTO $db VALUES (str_to_date('$time','%M %d, %Y %H:%i:%s.%f'), '$frame_len', '$cap_len', '$radiotap_len'\n)" );
    $sth->execute;
}

#Terminate MySQL
$dbh->disconnect;

Any Idea which can help to make the performance better is appreciated.Or may be there is an Alternative mechanism which can do better. Right now my performance is 50% means I can store in mysql around half of the packets I'v captured.

Disable any keys you've got on the capture table. Each individual update will cause the key to be updated, which can be a major slowdown when you're doing an insert storm. Turn off keys, then rebuild/re-enable the index after you've finished the inserting. — Marc B, Sep 13 '11 at 21:33

score 1 · Answer 1 · answered Mar 22 '13 at 16:35

For pipe problems, you can improve packet capture with GULP http://staff.washington.edu/corey/gulp/

From the Man pages:

1) reduce packet loss of a tcpdump packet capture:
      (gulp -c works in any pipeline as it does no data interpretation)

        tcpdump -i eth1 -w - ... | gulp -c > pcapfile
      or if you have more than 2, run tcpdump and gulp on different CPUs
        taskset -c 2 tcpdump -i eth1 -w - ... | gulp -c > pcapfile

      (gulp uses CPUs #0,1 so use #2 for tcpdump to reduce interference)

Karoly Horvath · Accepted Answer · 2011-09-13T20:51:05.340

1

Things written in a pipe don't get lost, what's probably really going on is that tshark tries to write to the pipe but perl+mysql is too slow to process the input so the pipeb is full, write would block so tshark just drops the packets.

Bottleneck could be either MySQL or Perl itself but probably the DB. Check CPU usage, measure insert rate. Then pick a faster DB or write to multiple DBs. You can also try batch inserts and increasing the size of the pipe buffer.

Update

while (<STDIN>)

this reads a line into $_, then you ignore it.

edited Sep 13 '11 at 20:51

answered Sep 13 '11 at 19:16

Karoly Horvath

94,607
11
117
176

I filtered my tshark capture so I decresed the frame capture rate to e.x 10 per min. The problem got even worse. I don't see any frame in DB!!! It seems I may have some problem with my perl. I'm wondering how I can use a text file as a buffer between Tshark and Perl. so I can make them asynchronize!! thsrak--->textfile as buffer--->Perl---->MySQL The problem is that I don't know how I can keep the file open and continue to read the file whenever new frames is written in the file??! Do you have any Idea? – Hamid Sep 13 '11 at 20:44
yey. Thanks!! That solved my first problem now in heavy frame rate I have almost 100% performance!!!! But when I slow down the frame rate to for example 10 frames per minute I can not capture the frames. Do You know what happens when the perl script exit the while loop and after while (e.g 10 mins) another packet is captured by the tshark. I think I should go to study more about how pipe works in linux!! – Hamid Sep 13 '11 at 22:19

score 0 · Answer 3 · edited Oct 27 '12 at 02:23

0

you can use a FIFO file, then read the packets and inserts in mysql using insert delay.

sudo tshark -i mon0 -t ad -T fields -e frame.time -e frame.len -e frame.cap_len -e radiotap.length > MYFIFO

edited Oct 27 '12 at 02:23

bensiu

24,660
56
77
117

answered Feb 23 '12 at 19:42

carloscardo00

1

How To Capture network packets to MySQL

3 Answers3