h323detail2db.pl

使用最广泛的radius的linux的源码

#!/usr/bin/perl
#
# Author:       Peter Nixon <[e-mail]>
# Summary:      Extract information from Radius detail log and
#		compare/insert/update a Postgresql database.
# Copy Policy:  GNU Public Licence Version 2
# URL:          http://www.peternixon.net/code/
# Supported:    PostgreSQL (tested on version 7.2, 7.3, 7.4 and 8) and FreeRadius
# Copyright:    2004 Peter Nixon http://www.petenixon.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: h323detail2db.pl,v 1.19 2007/05/14 22:26:58 nbk Exp $
#



# Modules that we use to get things done.
require DBI;
require Getopt::Long;

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


# Default Variables
$database    = "radius";
$port        = "3306";
$user        = "postgres";
$password    = "";



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

# Set up some basic variables
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		# Disconnect from the database
	    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 {		# FIXME: Does not work with current SQL schema. Use standard method
	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;		# Not a VoIP record. Bailout
	 }
	$sth2->execute();
	#my $returned_rows = $sth2->rows;
 	if ($verbose > 0) { print "added to DB\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 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()             # Execute the query
		or die "\nCouldn't execute statement: " . $sth->errstr . "\n";
	my $returned_rows = $sth->rows;

          if ($sth->rows == 0) {
		&db_insert;	# It's a new record. All systems go.
          } elsif ($sth->rows == 1) {
                if ($verbose > 0) { print "already in DB.\n"; }
		# FIXME: Make updates an option!
                #while (@data = $sth->fetchrow_array()) {
                #my $dbAcctSessionId = $data[1];
		##&db_update;
                #}
          } else {
		$duplicates++;	 # FIXME: Log this somewhere!
                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"; }
	# Clear the variables we use so that we don't have rubbish from the last loop
	$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) {  		# 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

	$AcctStatusType = $_ if s/Acct-Status-Type = //;
	if ($AcctStatusType eq "Stop") {		# All the data we need is in Stop records.
	} 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 = //) {