#!/usr/bin/perl
#
# Author:       Peter Nixon http://peternixon.net/
# Summary:      Extract information from RADIUS detail log and
#		compare/insert/update an SQL database.
# URL:          http://www.peternixon.net/code/
# Supported:    PostgreSQL (tested on version 7.2, 7.3, 7.4 and 8.x) and FreeRadius
# Copyright:    2004-2007 Peter Nixon http://peternixon.net/
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of Version 2 of the GNU General Public License as
# published by the Free Software Foundation.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# $Id: detail2db.pl,v 1.11 2007/04/12 07:25:27 peter Exp $
#



# Modules that we use to get things done.
require DBI;
require Getopt::Long;
use Time::HiRes qw( usleep );

## Program and File locations
# If you don't have gzcat and do have gzip then use: ln gzip gzcat
# bzcat - 'cat for .bz2 files'
$BZCAT = "/usr/bin/bzcat";
# gzcat - 'cat for .gz / gzip files'
$GZCAT = "/usr/bin/zcat";
# cat for .rar files
$RARCAT = "/usr/bin/unrar p";
# zcat - 'cat for .Z / compressed files'
$ZCAT = "/usr/bin/zcat";



# Default Variables
$database    = "radius";
$port        = "3306";
$user        = "raduser";
$password    = "password";
$acct_table  = "radacct";

# Number of microseconds to rest between each DB query so as not to overwhelm other work
$restusecs = 3000;


#### You should not have to modify anything below here
$progname = "RADIUS Detail2DB";
$version = 2.4;

# Set up some basic variables
my $passno = 0; my $duplicates = 0; my $verbose = 0; my %duplicate_records = ();
my $starttime = time();


# Map of dictionary attribute names to DB field names:
%attr_map = (
	"Acct-Authentic"	=> "AcctAuthentic",
	"Acct-Input-Octets"	=> "AcctInputOctets",
	"Acct-Output-Octets"	=> "AcctOutputOctets",
	"Acct-Session-Id"	=> "AcctSessionId",
	"Acct-Session-Time"	=> "AcctSessionTime",
	"Acct-Terminate-Cause"	=> "AcctTerminateCause",
	"Framed-IP-Address"	=> "FramedIPAddress",
	"Framed-Protocol"	=> "FramedProtocol",
	"NAS-IP-Address"	=> "NASIPAddress",
	"NAS-Port-Id"		=> "NASPortID",
	"NAS-Port"		=> "NASPortID",
	"NAS-Port-Type"		=> "NASPortType",
	"Service-Type"		=> "ServiceType",
	"User-Name"		=> "UserName",
	"Calling-Station-Id"	=> "CallingStationId",
	"Called-Station-Id"	=> "CalledStationId"
);

sub db_connect {
	my $hostname = shift;
	if ($verbose > 1) { print "DEBUG: Connecting to Database Host: $hostname\n" }
	if ($hostname eq 'localhost') {
	if ($verbose > 1) { print "DEBUG: localhost connection so using UNIX socket instead of network socket.\n" }
		$dbh = DBI->connect("DBI:Pg:dbname=$database", "$user", "$password")
        	        or die "Couldn't connect to database: " . DBI->errstr;
	} else {
		$dbh = DBI->connect("DBI:Pg:dbname=$database;host=$hostname", "$user", "$password")
        	        or die "Couldn't connect to database: " . DBI->errstr;
	}
}

sub db_disconnect {
	my $hostname = shift;
	if ($verbose > 1) { print "DEBUG: Disconnecting from Database Host: $hostname\n" }
	$dbh->disconnect		# Disconnect from the database
	    or warn "Disconnection failed: $DBI::errstr\n";
}

sub process_duplicates {
	if ($verbose > 0) { print "Now processing $duplicates duplicate records\n" }
	foreach my $a1 ( keys %duplicate_records ) {
		print "$a1:\n";
		for my $a2 ( keys %{ $duplicate_records{$a1} } ) {
			print "\t$a2 = $duplicate_records{$a1}{$a2}\n";
		}
	print "\n";
	}
}

sub record_missing {
	print " ADDING.." if ($verbose > 0);
	&db_insert;
}

sub record_exists {
	if ($verbose > 0) { print " EXISTS"; }
	## FIXME: Make updates an option!
	&db_update;
}

sub record_duplicate {
	$duplicates++;
	## FIXME: Log this somewhere!
	#@duplicate_records{$passno} += @record;
	print " DUPLICATE!\n";
	## FIXME: Make deletes an option!
	&db_delete;
}

sub db_insert {
	if ($AcctStatusType eq 'Stop') {
        $sth2 = $dbh->prepare("INSERT into $acct_table (
		AcctStartTime, AcctStopTime, UserName, NASIPAddress, AcctSessionId, AcctSessionTime, AcctUniqueId,
                AcctInputOctets, AcctOutputOctets, AcctTotalOctets, CalledStationId, CallingStationId, AcctStopDelay)
                VALUES(($AcctStartTime)::abstime, ($Timestamp)::abstime, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
	} else { 
		if ($AcctStatusType) { print "ERROR: Currently unsupported RADIUS packet type: \"$AcctStatusType\"\n"; }
		else { print "ERROR: Missing \"AcctStatusType\". This doesn't appear to be a RADIUS Accounting record."; }
		return;
	 }
	$sth2->execute($UserName, $NasIPAddress, $AcctSessionId, $AcctSessionTime, $AcctUniqueId,
	 $AcctInputOctets, $AcctOutputOctets, $AcctTotalOctets, $Called_Station_Id, $Calling_Station_Id, $AcctDelayTime)
		 or die "\nCouldn't execute INSERT: " . $sth2->errstr . "\n";
 	if ($verbose > 0) { print "COMMITTED\n"; }
	$sth2->finish();
}

## This sub can be used to update data in an existing database if you have some fields not in the Database.
sub db_update {
	my $sth2= $dbh->prepare("UPDATE $acct_table SET
		AcctStopTime = ($Timestamp)::abstime, AcctSessionTime = ?, AcctInputOctets = ?,
		AcctOutputOctets = ?, AcctTotalOctets = ?, AcctTerminateCause = ?
		WHERE AcctUniqueId = ?");
	$sth2->execute($AcctSessionTime, $AcctInputOctets, $AcctOutputOctets, $AcctTotalOctets, $AcctTerminateCause, $AcctUniqueId);
	my $returned_rows = $sth2->rows;
	if ($verbose > 0) { print " $returned_rows record(s) updated\n" }
	$sth2->finish();
}

sub db_read {
	if ($verbose > 0) { print "\#$passno Id: $AcctUniqueId Time: $radius_record_timestamp Length: $AcctSessionTime Data: $AcctTotalOctets"; }
	usleep ($restusecs) if ($restusecs); # Give the DB a rest
	my $sth = $dbh->prepare("SELECT RadAcctId FROM $acct_table WHERE AcctUniqueId = ?")
                or die "\nCouldn't prepare statement: " . $dbh->errstr . "\n";

	my @data, $duped_ID, $duped_ID2, $dupe_count = 0;
	$sth->execute($AcctUniqueId)             # Execute the query
		or die "\nCouldn't execute statement: " . $sth->errstr . "\n";
	my $returned_rows = $sth->rows;

          if ($sth->rows == 0) {
		&record_missing;	# It's a new record. All systems go.
          } elsif ($sth->rows == 1) {
		&record_exists;
          } else {
        	while (@data = $sth->fetchrow_array()) {
	        	$duped_ID = $duped_ID2;
	        	$duped_ID2 = $data[0];
			if ($dupe_count > 0) { &record_duplicate($duped_ID); }
			$dupe_count++;
        	}
          }
	$sth->finish;
}

sub db_delete {
	my $deleteid = shift;
	if ($verbose > 0) { print "Deleting duplicate record Id: $deleteid from database.\n"; }
	my $sth = $dbh->prepare("DELETE FROM $acct_table WHERE RadAcctId = ?")
                or die "\nCouldn't prepare statement: " . $dbh->errstr . "\n";
	$sth->execute($deleteid)             # Execute the query
		or die "\nCouldn't execute statement: " . $sth->errstr . "\n";
	$sth->finish;
}

sub process_record {
	$radius_record_timestamp = @record[0];
	chomp $radius_record_timestamp;
	print "DEBUG: Processing new record with time: $radius_record_timestamp \n" if ($verbose > 1); 
	# Clear the variables we use so that we don't have rubbish from the last loop
	$UserName=""; $NasPort=""; $NasPortType="";
	$NasIPAddress = ""; $AcctStatusType=""; $AcctSessionTime=""; $AcctSessionId=""; $AcctStartTime="";
	$AcctInputOctets=""; $AcctOutputOctets=""; $AcctTotalOctets=""; $AcctTerminateCause=""; $AcctUniqueId="";
	$ServiceType=""; $FramedProtocol=""; $FramedIPAddress="";
	$Timestamp=""; $AcctDelayTime=0; $ConnectInfo=""; $Called_Station_Id="";
	$SQL_User_Name=""; $Cisco_NAS_Port=""; $Client_IP_Address="";
	$h323_remote_address=""; $h323_disconnect_cause=""; $h323_gw_id="";
	$h323_conf_id=""; $h323_call_type=""; $h323_disconnect_time="";
	$h323_connect_time=""; $h323_setup_time=""; $Calling_Station_Id="";
	$h323_call_origin=""; $h323_voice_quality=""; $h323_gw_id="";

	foreach (@record) {  		# Parse the lines of data into variables.

	# Initial cleanup of junk from the line of data
	s/^\s+//;	# Strip leading spaces.
	s/^Quintum-//;	# Strip leading "Quintum-".
    	chomp;		# Strip trailing CR
	&dequote;	# Remove quotation marks from a bunch of different fields (Stupid Cisco)

	$AcctStatusType = $_ if s/Acct-Status-Type = //;
	$UserName = $_ if s/User-Name = //;
	$NasIPAddress = $_ if s/NAS-IP-Address = //;
	$AcctSessionId = $_ if s/Acct-Session-Id = //;
	$AcctUniqueId = $_ if s/Acct-Unique-Session-Id = //;
	$AcctSessionTime = $_ if s/Acct-Session-Time = //;
	$AcctInputOctets = $_ if s/Acct-Input-Octets = //;
	$AcctOutputOctets = $_ if s/Acct-Output-Octets = //;
	$AcctDelayTime = $_ if s/Acct-Delay-Time = //;
	$Called_Station_Id = $_ if s/Called-Station-Id = //;
	$Calling_Station_Id = $_ if s/Calling-Station-Id = //;
	$Cisco_NAS_Port = $_ if s/Cisco-NAS-Port = //;
	$Timestamp = $_ if s/Timestamp = //;
	if (s/h323-call-type = \"h323-call-type=//) {
                        $h323_call_type = substr($_, 0, -1);
                } elsif (s/h323-call-type = //) {
                        $h323_call_type = $_;
            };
	if (s/h323-remote-address = \"h323-remote-address=//) {
			$h323_remote_address = $_;
		} elsif (s/h323-remote-address = //) {
			$h323_remote_address = $_;
	    };
	if (s/h323-disconnect-cause = \"h323-disconnect-cause=//) {
                        $h323_disconnect_cause = $_;
                } elsif (s/h323-disconnect-cause = //) {
                        $h323_disconnect_cause = $_;
            };
	if (s/h323-conf-id = \"h323-conf-id=//) {
                        $h323_conf_id = substr($_, 0, -1);
                } elsif (s/h323-conf-id = //) {
                        $h323_conf_id = $_;
            };
	if (s/h323-connect-time = \"h323-connect-time=//) {
                        $h323_connect_time = substr($_, 0, -1);
                } elsif (s/h323-connect-time = //) {
                        $h323_connect_time = $_;
            };
	if (s/h323-disconnect-time = \"h323-disconnect-time=//) {
                        $h323_disconnect_time = substr($_, 0, -1);
                } elsif (s/h323-disconnect-time = //) {
                        $h323_disconnect_time = $_;
            };
	if (s/h323-setup-time = \"h323-setup-time=//) {
                        $h323_setup_time = substr($_, 0, -1);
                } elsif (s/h323-setup-time = //) {
                        $h323_setup_time = $_;
            };
        if (s/h323-call-origin = \"h323-call-origin=//) {
                        $h323_call_origin = substr($_, 0, -1);
                } elsif (s/h323-call-origin = //) {
                        $h323_call_origin = $_;
            };
        if (s/h323-gw-id = \"h323-gw-id=//) {
                        $h323_gw_id = substr($_, 0, -1);
                } elsif (s/h323-gw-id = //) {
                        $h323_gw_id = $_;
            };
        if (s/h323-voice-quality = \"h323-voice-quality=//) {
                        $h323_voice_quality = substr($_, 0, -1);
                } elsif (s/h323-voice-quality = //) {
                        $h323_voice_quality = $_;
            };
                # FIXME: ugh, definitely look into using backreference.
                # something like s/(\S+)\s*=\s*\1/\1 = / or so
	}

	# Remove Remove . from the start of time fields (routers that have lost ntp timesync temporarily)
	$h323_setup_time =~ s/^\.*//;
	$h323_connect_time =~ s/^\.*//;
	$h323_disconnect_time =~ s/^\.*//;

	# Ignore broken fields from some stupid, non-cisco gateways (They shall remain nameless)
	if ($h323_connect_time eq "0") { $h323_connect_time = "" };
	if ($h323_disconnect_time eq "0") { $h323_disconnect_time = "" };

	if ($AcctStatusType eq 'Interim-Update') {
		if ($verbose > 1) { print "DEBUG: Skipping \"Interim-Update\" record\n"; }
		return;
	} elsif ($AcctStatusType eq 'Start') {
		if ($verbose > 1) { print "DEBUG: Skipping \"Start\" record\n"; }
		return;
	} elsif ($AcctStatusType eq 'Stop') {
		#if ($verbose > 1) { print "DEBUG: Skipping \"Stop\" record\n"; }
		#return;
		print "DEBUG: \"Stop\" record\n" if ($verbose > 1);
	} elsif ($AcctStatusType eq 'Alive'){
		if ($verbose > 1) { print "DEBUG: Skipping \"Alive\" record\n"; }
		return;
	} else{
		if ($verbose > 1) { print "DEBUG: Skipping record with no Acct-Status-Type ($AcctStatusType)\n"; }
		return;
	};

	$AcctTotalOctets = $AcctInputOctets + $AcctOutputOctets;
	$AcctStartTime = ($Timestamp - $AcctSessionTime) if ($AcctStatusType eq 'Stop');
	# If its a valid Accounting record continue onto the database functions
	if ($AcctStatusType) { 
		$passno++;
		&db_read;
	} else { if ($verbose > 1) { print "DEBUG: Not a RADIUS Accounting record. Skipped.\n"; } }
}

sub read_record {
	my $keepreading = 1;
	@record = ();
	while ($keepreading) {
		$_ = <DETAIL>;
		print "$_" if ($verbose > 2);
		if ( /^$/ ) {
			$keepreading = 0;	# End of record
		} else {
			$record[++$#record] = $_;
		}
	}
	&process_record;
}

sub read_detailfile {
	my $file_starttime = time(); my $filename = shift; my @record = (); my $record_no = 0;
	if ($verbose > 1) { print "DEBUG: Reading detail file: $filename\n" }
	if ((-r $filename) != 1) { 		# test if the file exists and is readable
		if ($verbose >= 0) { print "INFO: Skipping file \"$filename\" as it is not readable or does not exist.\n" }
		return;
	 }
	if ( $filename =~ /.gz$/ ) {		# Deal with compressed files
		open (DETAIL, "$GZCAT $filename |") || warn "read_detailfile(\"$filename\"): $!\n";
	} elsif ( $filename =~ /.Z$/ ) {
		open (DETAIL, "$ZCAT $filename |") || warn "read_detailfile(\"$filename\"): $!\n";
	} elsif ( $filename =~ /.bz2$/ ) {
		open (DETAIL, "$BZCAT $filename |") || warn "read_detailfile(\"$filename\"): $!\n";
        } elsif ( $filename =~ /.rar$/ ) {
                open (DETAIL, "$RARCAT $filename |") || warn "read_file(\"$filename\"): $!\n";
	} else {
		open (DETAIL, "<$filename") || warn "read_detailfile(\"$filename\"): $!\n";
	}
	$valid_input = (eof(DETAIL) ? 0 : 1);
	if ($verbose > 1) { print "DEBUG: Starting to read records from $filename\n"; }
	while($valid_input) {
		$valid_input = 0 if (eof(DETAIL));
		if ($verbose > 1) { print "DEBUG: Reading Record\n"; }
		&read_record;
		$record_no++;
	}
	my $file_runtime = (time() - $file_starttime);
	if ($file_runtime < 1) { $file_runtime = 1; }
	my $file_speed = ($record_no / $file_runtime); 
        if ($verbose >= 0) { print "\n$record_no total records read from $filename were processed in $file_runtime seconds ($file_speed records/sec) \n"; }
}

sub print_usage_info {
	print "\n";
	$leader = "$progname $version Usage Information";
	$underbar = $leader;
	$underbar =~ s/./-/g;
	print "$leader\n$underbar\n";
	print "\n";
	print "  Syntax:   detail2db.pl [ options ] file(s)\n";
	print "\n";
	print "    -d --database                    Database to use\n";
	print "    -h --help                        Show this usage information\n";
	print "    -H --host                        Database host to connect to (Default: localhost)\n";
	print "    -q --quiet                       Turn on quiet mode (No Output)\n";
	print "    -v --verbose                     Turn on verbose\n";
	print "    -V --version                     Show version and copyright\n";
	print "    -x --debug                       Turn on debugging\n";
	print "\n";
}

sub dequote {
	# Strip quotes from Attributes
	s/\"//g;
	return $_;
};

sub main {
        # Parse the command line for options
        if (!scalar(@ARGV)) {
        	&print_usage_info();
		exit(SUCCESS);
	};

	# See the Getopt::Long man page for details on the syntax of this line
	@valid_opts = ("h|help", "V|version", "f|file=s", "x|debug", "d|database=s", "v|verbose+" => \$verbose, "q|quiet+" => \$quiet, "D|date=s", "H|host=s");
	Getopt::Long::Configure("no_getopt_compat", "bundling", "no_ignore_case");
	Getopt::Long::GetOptions(@valid_opts);

	# Post-parse the options stuff
	select STDOUT; $| = 1;
	if ($opt_V) {
		# Do not edit this variable.  It is updated automatically by CVS when you commit
		my $rcs_info = 'CVS Revision $Revision: 1.11 $ created on $Date: 2007/04/12 07:25:27 $ by $Author: peter $ ';

		$rcs_info =~ s/\$\s*Revision: (\S+) \$/$1/;
		$rcs_info =~ s/\$\s*Date: (\S+) (\S+) \$/$1 at $2/;
		$rcs_info =~ s/\$\s*Author: (\S+) \$ /$1/;

		print "\n";
		print "$progname Version $version by Peter Nixon - http://peternixon.net/\n";
		print "Copyright (c) 2002-2006 Peter Nixon\n";
		print "  ($rcs_info)\n";
		print "\n";
		return SUCCESS;
	} elsif ($opt_h) {
	        &print_usage_info();
	        exit(SUCCESS);
	}

	if ($opt_x) { 
		print "DEBUG: Debug mode is enabled.\n"; 
		$verbose = 2;
	} elsif ($quiet) { $verbose -= $quiet; }
	if ($opt_d) { 
		if ($verbose > 0) { print "Using database \"$opt_d\" instead of default database \"$database\"\n"; }
		$database = $opt_d;
	}

	if (@ARGV) {
		my $db_host;
		if ($opt_H) { $db_host = $opt_H; }
		else { $db_host = "localhost"; }
		&db_connect($db_host);

        	# Loop through the defined files
	        foreach $file (@ARGV) {
			&read_detailfile($file);
	        }
		&process_duplicates;
		&db_disconnect($db_host);

		my $runtime = (time() - $starttime);
		if ($runtime < 1) { $runtime = 1; }
		my $speed = ($passno / $runtime); 
	        if ($verbose >= 0) { print "\n $passno Stop records were processed in $runtime seconds ($speed records/sec) \n"; }
	} else {
		print "ERROR: Please specify one or more detail file(s) to import.\n";
		exit(FAILURE);
	}

}


exit &main();