Sending NTOP Results to mySQL (on Slackware 8.0)

Synopsis:
NTOP is great for generating stats on network bandwidth usage. To analyze bandwidth usage
over time, the results can be saved to a mySQL database (you can also use postgress,
Access, or any other database that talks via ODBC). These instructions show how to use a perl
script (included with ntop) to save results to a local mySQL database.

Directions:

1) Untar and install NTOP (get the latest build from http://www.ntop.org).

2) Log into mySQL and create a database called NTOP (i.e., log in and type create database NTOP).

3) Return to the ntop source files directory and change to the databases directory.

4) Create the mySQL tables using the script mySQLdefs.txt. On my machine I was able to do this using
the command mysql -u root -p < mySQLdefs.txt. Note: this text file simply contains the SQL commands
to create the needed tables.

5) Edit the file mySQLserver.pl to allow a connection to mySQL. Here's a quick look:

my $buffer = "";
my $bufLength = 1514;
my $DBDServer = "DBI:mysql:NTOP";
my $socket = null;
my $rv;

# Listen to the udp port
$socket = IO::Socket::INET->new(
LocalAddr => 'localhost',
LocalPort => 4000,
Proto => 'udp',
Type => SOCK_DGRAM);

# Open the database connection
my $dbh = DBI->connect('DBI:mysql:NTOP','ntop','zaphoid') or die "Couldn't connect to database: " .
DBI->errstr;

I added user ntop and the password zaphoid to the connection string used by the connect call to the DBI
object.

Note: this perl script listens for the NTOP output on socket 4000. The script then uses this text output
(which are SQL commands) to add, delete, or update information in the mySQL database.

6) Save and run the perl script by typing: ./mySQLserver.pl& This will run the script and send the
any output from socket 4000 to the screen.

7) Run NTOP using this command line: ntop -w 3333 -i eth0 -b localhost:4000 -u ntop -E -d

Quick Explanation of command line options:
-w 3333 Port for NTOP html reports
-i eth0 Listen on device eth0
-b localhost:4000 send the database text commands to local socket 4000
-u ntop run ntop as the local user 'ntop' (which I created on my machine)
-E Enable use of NMAP
-d Run NTOP as a daemon


Notes:
- I actually run the perl script with the command ./mySQLserver.pl > /dev/null& This keeps the output from
scrawling across the screen.

- This will generate lots of data. Make sure you have lots of room for the mySQL server tables or prepare
scripts to clean out information after a specified time.

Terra Firma Software Solutions, Inc.