Listing 4 Squid log parsing script

#!/usr/bin/perl

use DBI;
use POSIX;
use Getopt::Std;
use Sys::Syslog;
use IO::Select;
use Socket;
use File::Tail;

# ------   Configurable constants -------------------
$pw='password';
$database='fwlog';
$host='dbserver';
$dbusername='username';
$firewall="proxy1";
$logfile = "/var/log/squid/access.log";
#-----------------------------------------------------


# get commandline options
my %option= ();
getopts("dv?h:", \%option);

printusage() if $option{h} or $option{'?'};

if ($option{d}) {
    # go into daemon mode
    # fork once, let the parent exit
    my $pid = fork;
    exit if $pid;
    die "Couldn't fork: $!" unless defined($pid);

    # disassociated from our controlling terminal
    POSIX::setsid() or die "Can't start a new session: $!";
}


# open the database
my $dbh=DBI->connect("DBI:Pg:dbname=$database;host=$host",$dbusername,$pw);
fatalerror("Unable to connect: $DBI::errstr\n") unless (defined $dbh);

my $sth = $dbh->prepare("INSERT INTO traffic (firewall,datetime,sourceip,domain,url,username,status) VALUES (?,?,?,?,?,?,?)");

# open log file
my $file = File::Tail->new(name=>$logfile, interval=>1);

#Read each record
while (defined($line=$file->read)) {

    print $line if $option{v};

    # squid log format:
    # time elapsed remotehost code/status bytes method URL rfc931 peerstatus/peerhost type
    # example:
    # 1048013497.626    104 10.121.209.90 TCP_MISS/304 238 GET 
    # http://cdn.mapquest.com/mq/gglogo_16 stevendavis DIRECT/64.12.54.24 -
    # where time is a UTC unix timestamp.

    my ($timestamp, $client, $status, $url, $username) = (split /\s+/, $line) [0,2,3,6,7];

    $url =~ s#^http://##;
    my ($hostname, $path) = $url =~ m#^(.*?)([/:].*)$#;
    my ($seconds, $minutes, $hours, $day, $month, $year)  = (localtime($timestamp)) [0..5];

    $year = $year + 1900;
    my $datetime = "$year-$month-$day $hours:$minutes:$seconds";

    if ( ($status eq 'TCP_DENIED') or ($status eq 'UDP_DENIED') ) {
        $status = 'denied';
    } 
    else {
        $status = 'allowed';
    }

    $username = '' if $username eq '-'; 

    my $result = $sth->execute($firewall,$datetime, $client, $hostname, $path, $username, $status);
    fatalerror("Unable to perform INSERT:$DBI::errstr\nlogline= $_\ndatetime = \
      $datetime\nsourceip = $client\ndomain=$hostname\nurl = $url\n")  unless  \
      (defined $result);

}

sub printusage {
    print <<"END";

$0: Reads squid access log files.  Logs http traffic information to a database.
Usage: $0 [OPTION]... [DEVICENAME]

Options:
        -d          Became a daemon (run in the background) 
        -h or -?    Command line help (this screen)


END
}

sub fatalerror {
    my $message = shift;
    openlog($0, "nowait", "daemon");
    syslog("warning", "%s\n",$message);
    closelog();
    die;
}