require DBI;
require Getopt::Long;
$GZCAT = "/usr/bin/zcat";
$ZCAT = "/usr/bin/zcat";
$BZCAT = "/usr/bin/bzcat";
$database = "radius";
$port = "3306";
$user = "postgres";
$password = "";
$progname = "H323 Detail2DB";
$version = 2.2;
my $passno = 0; my $duplicates = 0; my $verbose = 0; my %duplicate_records = ();
my $starttime = time();
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 or warn "Disconnection failed: $DBI::errstr\n";
}
sub process_duplicates {
if ($verbose > 1) { print "DEBUG: 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 procedure_insert { if ($verbose > 0) { print "Record: $passno) Conf ID: $h323_conf_id Setup Time: $h323_setup_time Call Length: $AcctSessionTime "; }
if ($h323_call_type eq 'VoIP') {
$sth2 = $dbh->prepare("SELECT VoIPInsertRecord('$UserName', '$NasIPAddress', '$AcctSessionTime', '$AcctInputOctets', '$AcctOutputOctets',
'$Called_Station_Id', '$Calling_Station_Id', '$AcctDelayTime', '$h323_call_origin', '$h323_setup_time',
'$h323_connect_time','$h323_disconnect_time', '$h323_disconnect_cause', (NULLIF('$h323_remote_address', '')::inet), '$h323_voice_quality', '$h323_conf_id')");
}
elsif ($h323_call_type eq 'Telephony') {
$sth2 = $dbh->prepare("SELECT TelephonyInsertRecord('$UserName', '$NasIPAddress', '$AcctSessionTime', '$AcctInputOctets', '$AcctOutputOctets',
'$Called_Station_Id', '$Calling_Station_Id', '$AcctDelayTime', '$Cisco_NAS_Port', '$h323_call_origin',
'$h323_setup_time', '$h323_connect_time','$h323_disconnect_time', '$h323_disconnect_cause', '$h323_voice_quality', '$h323_conf_id')");
} else { print "ERROR: Unsupported h323calltype \"$h323_call_type\"\n" }
$sth2->execute();
if ($verbose > 0) { print "sent to DB\n"; }
$sth2->finish();
}
sub db_insert {
if ($h323_call_type eq 'VoIP') {
$sth2 = $dbh->prepare("INSERT into StopVoIP (
AcctTime, UserName, NASIPAddress, AcctSessionTime, AcctInputOctets, AcctOutputOctets,
CalledStationId, CallingStationId, AcctDelayTime, H323RemoteAddress, h323gwid, h323callorigin,
callid, h323connecttime, h323disconnectcause, h323disconnecttime, h323setuptime, h323voicequality)
values(($Timestamp)::abstime, '$UserName', '$NasIPAddress', '$AcctSessionTime', '$AcctInputOctets',
'$AcctOutputOctets', '$Called_Station_Id', '$Calling_Station_Id', '$AcctDelayTime',
NULLIF('$h323_remote_address', '')::INET, '$h323_gw_id','$h323_call_origin', '$h323_conf_id',
NULLIF('$h323_connect_time', '')::TIMESTAMPTZ, '$h323_disconnect_cause',
NULLIF('$h323_disconnect_time', '')::TIMESTAMPTZ, NULLIF('$h323_setup_time', '')::TIMESTAMPTZ,
NULLIF('$h323_voice_quality','')::INT4)");
}
elsif ($h323_call_type eq 'Telephony') {
$sth2 = $dbh->prepare("INSERT into StopTelephony (
AcctTime, UserName, NASIPAddress, AcctSessionTime,
AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctDelayTime,
CiscoNASPort, h323callorigin, callid, h323connecttime, h323disconnectcause, h323disconnecttime, h323setuptime, h323voicequality)
values(($Timestamp)::abstime, '$UserName', '$NasIPAddress', '$AcctSessionTime', '$AcctInputOctets', '$AcctOutputOctets',
'$Called_Station_Id', '$Calling_Station_Id', '$AcctDelayTime', '$Cisco_NAS_Port', '$h323_call_origin', '$h323_conf_id',
'$h323_connect_time', '$h323_disconnect_cause', '$h323_disconnect_time', '$h323_setup_time', '$h323_voice_quality')");
} else {
if ($h323_call_type) { print "ERROR: Unsupported h323calltype: \"$h323_call_type\"\n"; }
else { print "ERROR: Missing \"h323calltype\". This doesn't appear to be a VoIP record."; }
return; }
$sth2->execute();
if ($verbose > 0) { print "added to DB\n"; }
$sth2->finish();
}
sub db_update {
my $sth2= $dbh->prepare("UPDATE radacct SET CalledStationId = '$Called_Station_Id',
AcctTerminateCause = '$AcctTerminateCause', H323RemoteAddress = '$h323_remote_address',
AcctStatusType = '$AcctStatusType', callid = '$h323_conf_id', h323calltype = '$h323_call_type',
CiscoNASPort = '$Cisco_NAS_Port', h323disconnectcause = '$h323_disconnect_cause',
h323connecttime = '$h323_connect_time', h323disconnecttime = '$h323_disconnect_time',
h323setuptime = '$h323_setup_time' WHERE AcctSessionId = 'AcctSessionId' AND UserName = '$UserName'
AND NASIPAddress = '$NasIPAddress' AND h323confid = '$h323_conf_id'");
$sth2->execute();
my $returned_rows = $sth2->rows;
if ($verbose > 0) { print " $returned_rows record(s) updated\n" }
$sth2->finish();
}
sub db_read {
if ($verbose > 0) { print "Record: $passno) ConfID: $h323_conf_id Timestamp: $radius_record_timestamp Length: $AcctSessionTime "; }
my $sth = $dbh->prepare("SELECT RadAcctId FROM Stop$h323_call_type
WHERE AcctTime = ($Timestamp)::abstime
AND NASIPAddress = '$NasIPAddress'
AND callid = '$h323_conf_id'")
or die "\nCouldn't prepare statement: " . $dbh->errstr . "\n";
my @data;
$sth->execute() or die "\nCouldn't execute statement: " . $sth->errstr . "\n";
my $returned_rows = $sth->rows;
if ($sth->rows == 0) {
&db_insert; } elsif ($sth->rows == 1) {
if ($verbose > 0) { print "already in DB.\n"; }
} else {
$duplicates++; print "********* More than One Match! We have a problem!\n";
}
$sth->finish;
}
sub process_record {
$radius_record_timestamp = @record[0];
chomp $radius_record_timestamp;
if ($verbose > 1) { print "DEBUG: Processing new record with time: $radius_record_timestamp \n"; }
$UserName=""; $NasPort=""; $NasPortType="";
$NasIPAddress = ""; $AcctStatusType=""; $AcctSessionTime="";
$AcctInputOctets=""; $AcctOutputOctets=""; $AcctTerminateCause="";
$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) {
s/^\s+//; # Strip leading spaces.
s/^Quintum-//; # Strip leading "Quintum-".
chomp;
$AcctStatusType = $_ if s/Acct-Status-Type = //;
if ($AcctStatusType eq "Stop") { } elsif ($AcctStatusType eq "Start") {
if ($verbose > 1) { print "DEBUG: Skipping \"Start\" record\n"; }
return;
} elsif ($AcctStatusType eq "Alive"){
if ($verbose > 1) { print "DEBUG: Skipping \"Alive\" record\n"; }
return;
};
$UserName = $_ if s/User-Name = //;
$NasIPAddress = $_ if s/NAS-IP-Address = //;
$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 = $_;
};
}
$UserName =~ s/\"//g;
$h323_remote_address =~ s/\"//g;
$Called_Station_Id =~ s/\"//g;
$h323_disconnect_cause =~ s/\"//g;
$h323_setup_time =~ s/\"//g;
$h323_connect_time =~ s/\"//g;
$h323_disconnect_time =~ s/\"//g;
$h323_conf_id =~ s/\"//g;
$h323_call_type =~ s/\"//g;
$h323_call_origin =~ s/\"//g;
$h323_voice_quality =~ s/\"//g;
$Cisco_NAS_Port =~ s/\"//g;
$h323_setup_time =~ s/^\.*//;
$h323_connect_time =~ s/^\.*//;
$h323_disconnect_time =~ s/^\.*//;
if ($h323_connect_time eq "0") { $h323_connect_time = "" };
if ($h323_disconnect_time eq "0") { $h323_disconnect_time = "" };
if ($h323_call_type) {
$passno++;
if (&procedure_get()) { &procedure_insert; }
else { &db_read; }
} else { if ($verbose > 1) { print "DEBUG: Skipping non-h323 record\n"; } }
}
sub read_record {
my $keepreading = 1;
@record = ();
while ($keepreading) {
$_ = <DETAIL>;
print "$_" if ($verbose > 1);
if ( /^$/ ) {
$keepreading = 0; } else {
$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) { if ($verbose >= 0) { print "INFO: Skipping file \"$filename\" as it is not readable or does not exist.\n" }
return;
}
if ( $filename =~ /.gz$/ ) { 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";
} 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: h323detail2db.pl [ options ] detailfile(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 " -p --procedure Use Postgresql stored procedure (BROKEN!)\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 procedure_get() {
return $stored_procedure;
}
sub procedure_set($) {
$stored_procedure = $_[0];
}
sub main {
if (!scalar(@ARGV)) {
&print_usage_info();
exit(SUCCESS);
};
@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", "p|procedure");
Getopt::Long::Configure("no_getopt_compat", "bundling", "no_ignore_case");
Getopt::Long::GetOptions(@valid_opts);
select STDOUT; $| = 1;
if ($opt_V) {
my $rcs_info = 'CVS Revision $Revision$ created on $Date$ by $Author$ ';
$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://www.peternixon.net/\n";
print "Copyright (c) 2002-2004 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; }
&procedure_set($opt_p);
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);
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 valid 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();