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;
}

