Listing 2 The populateNetData.pl Perl script

#!/usr/bin/perl -w
#
#
# Filename: populateNetData.pl
# Programmer: Mihalis Tsoukalos
#
# Date: Monday 18 December 2006
#
# Storing network data in a MySQL database article
# for Sys Admin
#
# This Perl script is for demonstration only
# Do not use it in a production system without changes
#

use strict;
use DBI;
use DBD::mysql;
use POSIX;

my $datasource = "DBI:mysql:database=SA;host=localhost";
my $user = "mtsouk";
my $password = "xxx";

my $SQLcommand = "";

# source Port
my $sourcePort="";
# destination Port
my $destPort="";
# time Difference
my $dt="";
# date and time
my $d="";
# The $line variable holds each input line
my $line="";

# Rows inserted
my $ROWS_INSERTED = 0;


#
# The format of the NetData table
#
# mysql> desc NetData;
# +------------+-------------+------+-----+---------+-------+
# | Field      | Type        | Null | Key | Default | Extra |
# +------------+-------------+------+-----+---------+-------+
# | Date       | datetime    | NO   |     |         |       |
# | dt         | float       | YES  |     | NULL    |       |
# | sourcePort | varchar(12) | YES  |     | NULL    |       |
# | destPort   | varchar(12) | YES  |     | NULL    |       |
# +------------+-------------+------+-----+---------+-------+
# 4 rows in set (0.00 sec)
#

die <<Thanatos unless @ARGV;
    usage:
        $0 inputData
Thanatos

if ( @ARGV != 1 )
{
   die <<Thanatos
      usage info:
         Please use exactly 1 argument!
Thanatos
}

my $filename = shift @ARGV || "filename";

open (INPUT, "< $filename")
                || die "Cannot open $filename: $!\n";

#
# Start reading the input file
#
while ( defined($line = <INPUT>) )
{
    chomp $line;
    my @fields = split (/ /, $line);
    
    #
    # Input file format
    #
    # 900.11856939 0.308413 1024 http
    # 900.11857575 0.000636 http 1024
    # 900.1485114 0.300888 http 1024
    #
    #
    # The date format is as follows:
    # YYYY-MM-DD HH:MINUTE:SECOND
    #
    #
    # * * * Get the time and the date.
    #
    # The time is in minutes and should be converted in
    # hours, minutes and seconds.
    #
    # For simplicity reasons, the date is the current date
    #

    my $time = $fields[0];
    $dt = $fields[1];
    $sourcePort = $fields[2];
    $destPort = $fields[3];

    my $hour = floor( $time / 60 );
    my $min = int ( $time % 60 );
    #
    # Calculating the seconds is a little tricky!
    #
    my $sec = int ( ( $time - $hour * 60 - $min ) * 60 );

    my ($day, $month, $year) = (localtime)[3, 4, 5];
    $year += 1900;
    $month += 1;
    $d = $year."-".$month."-".$day;
    $d .= " $hour:$min:$sec";

    # Used for debugging purposes
    # print $d."\n";

    $SQLcommand = "INSERT INTO NetData (Date, dt, sourcePort, destPort)";
    $SQLcommand .= " VALUES ( '$d', '$dt', '$sourcePort', '$destPort' )";

    #
    # Open the MySQL connection
    #
    # NOTE:
    #        For this particular script, every new MySQL record
    #        uses a new Perl-MySQL connection. This may not be
    #        a good practice for you.
    #
    # Database handler
    my $dbh = DBI -> connect($datasource, $user, $password);

    # Execute the command
    $dbh->do( $SQLcommand );

    $ROWS_INSERTED++;

    # Close the MySQL connection.
    $dbh -> disconnect();
}

print "Number of rows inserted: $ROWS_INSERTED\n";
#
# You can execute the following SQL command to make
# sure that the number of inserted rows is right.
#
# mysql> select count(*) from NetData;
# +----------+
# | count(*) |
# +----------+
# |     4912 |
# +----------+
# 1 row in set (0.03 sec)
#
# mysql>
#

# Close the input file
close ( INPUT ) 
    || die "Cannot close $filename: $!\n";

exit 0;