Ola
Yes you can have Ntop record all traffic data to a database, BUT unless you have a
need to record a history of traffic and have the server side resources required to do
so I would suggest looking into other means.
But none the less attached are Perl and SH scripts that will allow you to record data
from Ntop to a MYSQL db for a period of 31 days, any tables older are removed. This
works on the basis of one table per day. I must warn you how ever depending on the
size and state of your network 1 days traffic can be extremely large.
A script is included to create the db and tables, and a script to remove any old
tables.
The collector and exporter simply grab and sort net flow packets from Ntop, or any
where else on the bound IP and port. Exporter enters the sorted data into the db and
deletes the sorted text file. Please not the idea behind the collector and exporter
was borrowed from an existing script in the Ntop file dir thingit, so credit the
creator not me.
Another thing I must stress is hat this issue has been dealt with time and time again
on these mailing lists. Guys please read the backlog on the mailing list ... you might
be surprised as to what you find. O and google is your friend.
&]
You will need a basic understanding of PERL and MYSQL, I will help where I can but
free time is a luxury I don't often enjoy.
Jan
[EMAIL PROTECTED]
#!/usr/local/bin/perl -w
use DBI;
use strict;
sub prvmonth($);
my $db ="db name";
my @f = (localtime)[3..5];
my $tab =sprintf "`%d-%d-%d`\n", $f[0] , $f[1] +1 , $f[2] + 1900;
my $mysqlexe ="/usr/bin/mysql";
my $serverName = "your.server.somewhere";
my $serverPort = "your db port";
my $serverUser = "your db username";
my $serverPass = "your db password";
my $tbdata = <<"EOT";
(
`router_id` char(1) NOT NULL default '',
`src_ipn` bigint(20) unsigned NOT NULL default '0',
`dst_ipn` bigint(20) unsigned NOT NULL default '0',
`nxt_ipn` bigint(20) unsigned NOT NULL default '0',
`ifin` smallint(5) unsigned NOT NULL default '0',
`ifout` smallint(5) unsigned NOT NULL default '0',
`packets` int(10) unsigned NOT NULL default '0',
`octets` int(10) unsigned NOT NULL default '0',
`starttime` timestamp(14) NOT NULL,
`endtime` timestamp(14) NOT NULL,
`srcport` smallint(5) unsigned NOT NULL default '0',
`dstport` smallint(5) unsigned NOT NULL default '0',
`tcp` tinyint(3) unsigned NOT NULL default '0',
`prot` tinyint(3) unsigned NOT NULL default '0',
`tos` tinyint(3) unsigned NOT NULL default '0',
`srcas` smallint(5) unsigned NOT NULL default '0',
`dstas` smallint(5) unsigned NOT NULL default '0',
`srcmask` tinyint(3) unsigned NOT NULL default '0',
`dstmask` tinyint(3) unsigned NOT NULL default '0',
KEY `src_ipn` (`src_ipn`),
KEY `dst_ipn` (`dst_ipn`),
KEY `nxt_ipn` (`nxt_ipn`),
KEY `starttime` (`starttime`),
KEY `endtime` (`endtime`),
KEY `srcas` (`srcas`),
KEY `dstas` (`dstas`)
)
EOT
my $dbh = DBI->connect("DBI:mysql:database=$db;host=$serverName;
port=$serverPort",$serverUser,$serverPass);
my $now=time;
for (my $i=0;$i<31;$i++)
{
my $nextmonth=$now+(86400*$i);
my @lastm = localtime($nextmonth);
my $datestring = sprintf "`%d-%d-%d`", $lastm[3],$lastm[4]+1,$lastm[5]+1900;
print "$datestring\n";
my $sql = "CREATE TABLE IF NOT EXISTS $datestring $tbdata";
print "$sql\n";
my $cursor = $dbh->prepare($sql);
$cursor->execute;
}
system("/usr/bin/perl -w /location/of/collector.pl");
#!/usr/local/bin/perl -w
use DBI;
use strict;
sub prvmonth($);
my $sqlv = "";
my $delfile = $ARGV[0];
my $filedir ="/your/file dir/ndb";
my $savedir ="/your/save dir/sdb";
my $db ="your db name";
my @f = (localtime)[3..5];
my $tab =sprintf "`%d-%d-%d`\n", $f[0] , $f[1] +1 , $f[2] + 1900;
my $mysqlexe ="/usr/bin/mysql";
my $serverName = "your.server.somewhere";
my $serverPort = "your db port";
my $serverUser = "your db username";
my $serverPass = "your db password";
my $sqldel = "";
my $oldtab = "";
my $dbh = DBI->connect("DBI:mysql:database=$db;host=$serverName;
port=$serverPort",$serverUser,$serverPass);
print $tab;
my $now=time;
my $lastmonth=$now-2764800;
my @lastm = localtime($lastmonth);
my $datestring = sprintf "`%d-%d-%d`", $lastm[3],$lastm[4]+1,$lastm[5]+1900;
open(HANDLE, "$delfile");
my @raw_data=<HANDLE>;
for (my $i = 0; $i < $#raw_data; $i++)
{
my @values = split(/\t/,$raw_data[$i]);
$sqlv = "";
for (my $x = 0; $x < $#values; $x++){ $sqlv = $sqlv . $values[$x]; if($x < ($#values -
1)){ $sqlv = $sqlv . "','"; } }
my $sql = "INSERT INTO $tab ( `router_id` , `src_ipn` , `dst_ipn` , `nxt_ipn` , `ifin`
, `ifout` , `packets` , `octets` , `starttime` , `endtime` , `srcport` , `dstport` ,
`tcp` , `prot` , `tos` , `srcas` , `dstas` , `srcmask` , `dstmask` ) VALUES
('$sqlv')";
my $cursor = $dbh->prepare($sql);
$cursor->execute;
}
$oldtab = $datestring;
print $oldtab;
$sqldel = "DROP TABLE IF EXISTS $oldtab";
my $cursor2 = $dbh->prepare($sqldel);
$cursor2->execute;
$dbh->disconnect;
system("/bin/rm -f /Your/file dir/ndb/$delfile");
#!/usr/local/bin/perl -w
use IO::Socket;
use IO::Handle;
$basedir="/your/base/dir/";
$fileage=300;
$routers{196007100102}="your router1";
$routers{127000000001}="your router2";
$nexthopignore{IP address}=TRUE;
$nexthopignore{IP address}=TRUE;
sub timestr {
my $t=shift(@_);
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime($t);
$mon++;
$year+=1900;
sprintf("%4.4d%2.2d%2.2d%2.2d%2.2d%2.2d",$year,$mon,$mday,$hour,$min,$sec);
}
sub num2ipnum {
my $a=shift(@_);
my $a1=($a&0xff000000)>>24;
my $a2=($a&0x00ff0000)>>16;
my $a3=($a&0x0000ff00)>>8;
my $a4=($a&0x000000ff);
sprintf("%3.3d%3.3d%3.3d%3.3d",$a1,$a2,$a3,$a4);
}
select((select(STDOUT),$|=1)[0]);
my $flows = IO::Socket::INET->new(
Proto => "udp",
LocalAddr => "127.0.0.1",
LocalPort => "2055"
)
or die "cannot open socket to listen on 2055";
$outfilename=$basedir."/tmp.".$$.".".time();
open(OUTFILE,">".$outfilename) || die "open(OUTFILE): $!";
$fileopentime=time();
FOREVER: while(1) {
my ($buf,$rcvval);
$rcvval=$flows->recv($buf,2000,0) || die "recv: $!";
my $rtrkey=$routers{num2ipnum(unpack('x4 N x8',$rcvval))};
if (!($rtrkey)) {
printf "UNKNOWN ROUTER ";
next FOREVER;
}
my @FLOWS=unpack("a24 a48 a48 a48 a48 a48 a48 a48 a48 a48 a48 a48".
" a48 a48 a48 a48 a48 a48 a48 a48 a48 a48 a48".
" a48 a48 a48 a48 a48 a48 a48 a48",$buf);
my ($hver,$hcount,$hsysuptime,$hunix_secs,$hunix_nsecs,$hflow_sequence)=
unpack("n n N N N N x4",shift(@FLOWS));
next FOREVER if ($hver != 5);
next FOREVER if ($hcount != ((length($buf)-24)/48));
my $basetime=$hunix_secs+($hunix_nsecs/1000000000)-($hsysuptime/1000);
HANDLE_A_PACKET:
while(1) {
my $thisflow=shift(@FLOWS);
last HANDLE_A_PACKET
if ((!defined($thisflow))||length($thisflow)<48);
my ($fsrc,$fdst,$fnext,$fin,$fout,$fpkts,$focts,$fstrt,$fend,$fsrcp,
$fdstp,$ftcp,$fprot,$ftos,$fsas,$fdas,$fsmsk,$fdmsk)=
unpack("N3 n2 N4 n2 x C3 n2 C2 x2",$thisflow);
next HANDLE_A_PACKET
if ($nexthopignore{num2ipnum($fnext)});
printf(OUTFILE "%s\t",$rtrkey);
printf(OUTFILE "%s\t",num2ipnum($fsrc));
printf(OUTFILE "%s\t",num2ipnum($fdst));
printf(OUTFILE "%s\t",num2ipnum($fnext));
printf(OUTFILE "%d\t",$fin);
printf(OUTFILE "%d\t",$fout);
printf(OUTFILE "%d\t",$fpkts);
printf(OUTFILE "%d\t",$focts);
printf(OUTFILE "%s\t",timestr($basetime+($fstrt/1000)));
printf(OUTFILE "%s\t",timestr($basetime+($fend/1000)));
printf(OUTFILE "%d\t",$fsrcp);
printf(OUTFILE "%d\t",$fdstp);
printf(OUTFILE "%d\t",$ftcp);
printf(OUTFILE "%d\t",$fprot);
printf(OUTFILE "%d\t",$ftos);
printf(OUTFILE "%d\t",$fsas);
printf(OUTFILE "%d\t",$fdas);
printf(OUTFILE "%d\t",$fsmsk);
printf(OUTFILE "%d\t",$fdmsk);
printf(OUTFILE "\n");
}
printf ("%2.2d%s ",$hcount,$rtrkey);
if (time() > $fileopentime + $fileage) {
close OUTFILE;
$finalname=$basedir."/out.".$fileopentime;
rename($outfilename,$finalname);
printf("\n%s -> %s\n",$outfilename,$finalname);
$outfilename=$basedir."/tmp.".$$.".".time();
open(OUTFILE,">".$outfilename) || die "open(OUTFILE): $!";
$fileopentime=time();
system("/usr/bin/perl -w /location/of/exporter.pl $finalname");
}
}
#!/usr/local/bin/perl -w
use CGI qw(:standard);
use DBI;
use strict;
sub MDS_Timestamp_Yesterday($);
sub MDS_Timestamp_REST($);
sub MDS_Timestamp($);
my $serverName = "your.server.somewhere";
my $serverPort = "ur db port";
my $serverUser = "db user";
my $serverPass = "db Pass";
my $serverDb = "db name";
my $serverTabl = "trafic"; # from
my $dest_table = "trafic_data2"; # to
my $dbh = DBI->connect("DBI:mysql:database=$serverDb;host=$serverName;
port=$serverPort",$serverUser,$serverPass);
my $starttime = 0;
my $time_offset = 5*60; # 5 minutes
my $end_of_the_day = 60*60*24; # one Day
my $endtime = 0;
my $timestep = 0;
my $cursor;
$starttime = MDS_Timestamp 0; # first Timestamp
while($timestep < $end_of_the_day)
{
my $endtimestep = $timestep + $time_offset -1;
$endtime = MDS_Timestamp $endtimestep;
print ($starttime, " - " ,$endtime,"\n");
my $SQL = <<"EOT";
select distinct src_ipn,dst_ipn from $serverTabl WHERE starttime BETWEEN
$starttime AND $endtime
EOT
# print $SQL;
$cursor = $dbh->prepare($SQL);
$cursor->execute;
my @columns;
while ( @columns = $cursor->fetchrow )
{
$SQL = "SELECT src_ipn, sum(packets), sum(octets), $serverTabl.prot,
$serverTabl.srcport, $serverTabl.dstport, COUNT(*),dst_ipn FROM $serverTabl WHERE
$serverTabl.starttime BETWEEN $starttime AND $endtime and
$serverTabl.src_ipn=$columns[0] and $serverTabl.dst_ipn=$columns[1] group by
$serverTabl.prot";
# print $SQL;
my $cursor2 = $dbh->prepare($SQL);
$cursor2->execute;
my @columns_data ;
while (@columns_data = $cursor2->fetchrow)
{
$SQL = "DELETE FROM $dest_table WHERE src_ipn=$columns_data[0] and
starttime=$starttime and endtime=$endtime and prot=$columns_data[3] and
dst_ipn=$columns_data[7]";
# print $SQL;
my $cursor_delete = $dbh->prepare($SQL);
$cursor_delete->execute;
$cursor_delete->finish;
$SQL = "INSERT INTO $dest_table VALUES ( '', $columns_data[0],
$columns_data[1], $columns_data[2], $columns_data[3], $columns_data[6], $starttime,
$endtime, $columns_data[4], $columns_data[5], $columns_data[7] )";
my $cursor3 = $dbh->prepare($SQL);
# print ( " ",$columns_data[0]," ", $columns_data[2] , "\n");
$cursor3->execute;
$cursor3->finish;
}
$cursor2->finish;
#
}
$timestep = $timestep + $time_offset;
$starttime = MDS_Timestamp $timestep;
}
$cursor->finish;
$dbh->disconnect;
########################################################################
sub MDS_Timestamp($) {
my($time) = @_;
my $t = time - (60*60*24); # go one day back
my $yesterday = MDS_Timestamp_Yesterday($t);
my $rest = MDS_Timestamp_REST($time);
return sprintf "%08d%06d", $yesterday , $rest ;
}
sub MDS_Timestamp_Yesterday($) {
my($time) = @_;
my ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst)
= gmtime $time;
return sprintf "%04d%02d%02d",
$year + 1900, $mon + 1, $mday ;
} # MDS_Timestamp
sub MDS_Timestamp_REST($) {
my($resttime) = @_;
my ($hour, $min, $sec )
= gmtime $resttime;
return sprintf "%02d%02d%02d",
$sec, $min, $hour;
} # MDS_Timestamp
_______________________________________________
Ntop mailing list
[EMAIL PROTECTED]
http://listgateway.unipi.it/mailman/listinfo/ntop
