src/billing/h323detail2db.pl

   1 #!/usr/bin/perl
   2 #
   3 # Author:       Peter Nixon <codemonkey@peternixon.net>
   4 # Summary:      Extract information from Radius detail log and
   5 #               compare/insert/update a Postgresql database.
   6 # Copy Policy:  GNU Public Licence Version 2
   7 # URL:          http://www.peternixon.net/code/
   8 # Supported:    PostgreSQL (tested on version 7.2, 7.3, 7.4 and 8) and FreeRadius
   9 # Copyright:    2004 Peter Nixon http://www.petenixon.net
  10 #
  11 # This program is free software; you can redistribute it and/or modify
  12 # it under the terms of Version 2 of the GNU General Public License as
  13 # published by the Free Software Foundation.
  14 #
  15 # This program is distributed in the hope that it will be useful,
  16 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  17 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  18 # GNU General Public License for more details.
  19 #
  20 # $Id$
  21 #
  22
  23
  24
  25 # Modules that we use to get things done.
  26 require DBI;
  27 require Getopt::Long;
  28
  29 ## Program and File locations
  30 # gzcat - 'cat for .gz / gzip files'
  31 # If you don't have gzcat and do have gzip then use: ln gzip gzcat
  32 $GZCAT = "/usr/bin/zcat";
  33 # zcat - 'cat for .Z / compressed files'
  34 $ZCAT = "/usr/bin/zcat";
  35 # bzcat - 'cat for .bz2 files'
  36 $BZCAT = "/usr/bin/bzcat";
  37
  38
  39 # Default Variables
  40 $database    = "radius";
  41 $port        = "3306";
  42 $user        = "postgres";
  43 $password    = "";
  44
  45
  46
  47 #### You should not have to modify anything below here
  48 $progname = "H323 Detail2DB";
  49 $version = 2.2;
  50
  51 # Set up some basic variables
  52 my $passno = 0; my $duplicates = 0; my $verbose = 0; my %duplicate_records = ();
  53 my $starttime = time();
  54
  55
  56 sub db_connect {
  57         my $hostname = shift;
  58         if ($verbose > 1) { print "DEBUG: Connecting to Database Host: $hostname\n" }
  59         if ($hostname eq 'localhost') {
  60         if ($verbose > 1) { print "DEBUG: localhost connection so using UNIX socket instead of network socket.\n" }
  61                 $dbh = DBI->connect("DBI:Pg:dbname=$database", "$user", "$password")
  62                         or die "Couldn't connect to database: " . DBI->errstr;
  63         } else {
  64                 $dbh = DBI->connect("DBI:Pg:dbname=$database;host=$hostname", "$user", "$password")
  65                         or die "Couldn't connect to database: " . DBI->errstr;
  66         }
  67 }
  68
  69 sub db_disconnect {
  70         my $hostname = shift;
  71         if ($verbose > 1) { print "DEBUG: Disconnecting from Database Host: $hostname\n" }
  72         $dbh->disconnect                # Disconnect from the database
  73             or warn "Disconnection failed: $DBI::errstr\n";
  74 }
  75
  76 sub process_duplicates {
  77         if ($verbose > 1) { print "DEBUG: Now processing $duplicates duplicate records\n" }
  78         foreach my $a1 ( keys %duplicate_records ) {
  79                 print "$a1:\n";
  80                 for my $a2 ( keys %{ $duplicate_records{$a1} } ) {
  81                         print "\t$a2 = $duplicate_records{$a1}{$a2}\n";
  82                 }
  83         print "\n";
  84         }
  85 }
  86
  87
  88 sub procedure_insert {          # FIXME: Does not work with current SQL schema. Use standard method
  89         if ($verbose > 0) { print "Record: $passno) Conf ID: $h323_conf_id   Setup Time: $h323_setup_time  Call Length: $AcctSessionTime   "; }
  90         if ($h323_call_type eq 'VoIP') {
  91         $sth2 = $dbh->prepare("SELECT VoIPInsertRecord('$UserName', '$NasIPAddress', '$AcctSessionTime', '$AcctInputOctets', '$AcctOutputOctets',
  92                 '$Called_Station_Id', '$Calling_Station_Id', '$AcctDelayTime', '$h323_call_origin', '$h323_setup_time',
  93                 '$h323_connect_time','$h323_disconnect_time', '$h323_disconnect_cause', (NULLIF('$h323_remote_address', '')::inet), '$h323_voice_quality', '$h323_conf_id')");
  94         }
  95         elsif ($h323_call_type eq 'Telephony') {
  96         $sth2 = $dbh->prepare("SELECT TelephonyInsertRecord('$UserName', '$NasIPAddress', '$AcctSessionTime', '$AcctInputOctets', '$AcctOutputOctets',
  97                 '$Called_Station_Id', '$Calling_Station_Id', '$AcctDelayTime', '$Cisco_NAS_Port', '$h323_call_origin',
  98                 '$h323_setup_time', '$h323_connect_time','$h323_disconnect_time', '$h323_disconnect_cause', '$h323_voice_quality', '$h323_conf_id')");
  99         } else { print "ERROR: Unsupported h323calltype \"$h323_call_type\"\n" }
 100         $sth2->execute();
 101
 102         if ($verbose > 0) { print "sent to DB\n"; }
 103         $sth2->finish();
 104 }
 105
 106 sub db_insert {
 107         if ($h323_call_type eq 'VoIP') {
 108         $sth2 = $dbh->prepare("INSERT into StopVoIP (
 109                 AcctTime, UserName, NASIPAddress, AcctSessionTime, AcctInputOctets, AcctOutputOctets,
 110                 CalledStationId, CallingStationId, AcctDelayTime, H323RemoteAddress, h323gwid, h323callorigin,
 111                 callid, h323connecttime, h323disconnectcause, h323disconnecttime, h323setuptime, h323voicequality)
 112                 values(($Timestamp)::abstime, '$UserName', '$NasIPAddress', '$AcctSessionTime', '$AcctInputOctets',
 113                 '$AcctOutputOctets', '$Called_Station_Id', '$Calling_Station_Id', '$AcctDelayTime',
 114                 NULLIF('$h323_remote_address', '')::INET, '$h323_gw_id','$h323_call_origin', '$h323_conf_id',
 115                 NULLIF('$h323_connect_time', '')::TIMESTAMPTZ, '$h323_disconnect_cause',
 116                 NULLIF('$h323_disconnect_time', '')::TIMESTAMPTZ, NULLIF('$h323_setup_time', '')::TIMESTAMPTZ,
 117                 NULLIF('$h323_voice_quality','')::INT4)");
 118
 119         }
 120         elsif ($h323_call_type eq 'Telephony') {
 121         $sth2 = $dbh->prepare("INSERT into StopTelephony (
 122                 AcctTime, UserName, NASIPAddress, AcctSessionTime,
 123                 AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctDelayTime,
 124                 CiscoNASPort, h323callorigin, callid, h323connecttime, h323disconnectcause, h323disconnecttime, h323setuptime, h323voicequality)
 125                 values(($Timestamp)::abstime, '$UserName', '$NasIPAddress', '$AcctSessionTime', '$AcctInputOctets', '$AcctOutputOctets',
 126                 '$Called_Station_Id', '$Calling_Station_Id', '$AcctDelayTime', '$Cisco_NAS_Port', '$h323_call_origin', '$h323_conf_id',
 127                 '$h323_connect_time', '$h323_disconnect_cause', '$h323_disconnect_time', '$h323_setup_time', '$h323_voice_quality')");
 128         } else {
 129                 if ($h323_call_type) { print "ERROR: Unsupported h323calltype: \"$h323_call_type\"\n"; }
 130                 else { print "ERROR: Missing \"h323calltype\". This doesn't appear to be a VoIP record."; }
 131                 return;         # Not a VoIP record. Bailout
 132          }
 133         $sth2->execute();
 134         #my $returned_rows = $sth2->rows;
 135         if ($verbose > 0) { print "added to DB\n"; }
 136         $sth2->finish();
 137 }
 138
 139 ## This sub can be used to update data in an existing database if you have some fields not in the Database.
 140 sub db_update {
 141         my $sth2= $dbh->prepare("UPDATE radacct SET CalledStationId = '$Called_Station_Id',
 142                 AcctTerminateCause = '$AcctTerminateCause', H323RemoteAddress = '$h323_remote_address',
 143                 AcctStatusType = '$AcctStatusType', callid = '$h323_conf_id', h323calltype = '$h323_call_type',
 144                 CiscoNASPort = '$Cisco_NAS_Port', h323disconnectcause = '$h323_disconnect_cause',
 145                 h323connecttime = '$h323_connect_time', h323disconnecttime = '$h323_disconnect_time',
 146                 h323setuptime = '$h323_setup_time' WHERE AcctSessionId = 'AcctSessionId' AND UserName = '$UserName'
 147                 AND NASIPAddress = '$NasIPAddress' AND h323confid = '$h323_conf_id'");
 148         $sth2->execute();
 149         my $returned_rows = $sth2->rows;
 150         if ($verbose > 0) { print " $returned_rows record(s) updated\n" }
 151         $sth2->finish();
 152
 153 }
 154
 155 sub db_read {
 156         if ($verbose > 0) { print "Record: $passno) ConfID: $h323_conf_id Timestamp: $radius_record_timestamp Length: $AcctSessionTime "; }
 157         my $sth = $dbh->prepare("SELECT RadAcctId FROM Stop$h323_call_type
 158                 WHERE AcctTime = ($Timestamp)::abstime
 159                 AND NASIPAddress = '$NasIPAddress'
 160                 AND callid = '$h323_conf_id'")
 161                 or die "\nCouldn't prepare statement: " . $dbh->errstr . "\n";
 162
 163         my @data;
 164         $sth->execute()             # Execute the query
 165                 or die "\nCouldn't execute statement: " . $sth->errstr . "\n";
 166         my $returned_rows = $sth->rows;
 167
 168           if ($sth->rows == 0) {
 169                 &db_insert;     # It's a new record. All systems go.
 170           } elsif ($sth->rows == 1) {
 171                 if ($verbose > 0) { print "already in DB.\n"; }
 172                 # FIXME: Make updates an option!
 173                 #while (@data = $sth->fetchrow_array()) {
 174                 #my $dbAcctSessionId = $data[1];
 175                 ##&db_update;
 176                 #}
 177           } else {
 178                 $duplicates++;   # FIXME: Log this somewhere!
 179                 print "********* More than One Match! We have a problem!\n";
 180           }
 181
 182         $sth->finish;
 183 }
 184
 185 sub process_record {
 186         $radius_record_timestamp = @record[0];
 187         chomp $radius_record_timestamp;
 188         if ($verbose > 1) { print "DEBUG: Processing new record with time: $radius_record_timestamp \n"; }
 189         # Clear the variable we use so that we don't have rubbish from the last loop
 190         $UserName=""; $NasPort=""; $NasPortType="";
 191         $NasIPAddress = ""; $AcctStatusType=""; $AcctSessionTime="";
 192         $AcctInputOctets=""; $AcctOutputOctets=""; $AcctTerminateCause="";
 193         $ServiceType=""; $FramedProtocol=""; $FramedIPAddress="";
 194         $Timestamp=""; $AcctDelayTime=0; $ConnectInfo=""; $Called_Station_Id="";
 195         $SQL_User_Name=""; $Cisco_NAS_Port=""; $Client_IP_Address="";
 196         $h323_remote_address=""; $h323_disconnect_cause=""; $h323_gw_id="";
 197         $h323_conf_id=""; $h323_call_type=""; $h323_disconnect_time="";
 198         $h323_connect_time=""; $h323_setup_time=""; $Calling_Station_Id="";
 199         $h323_call_origin=""; $h323_voice_quality=""; $h323_gw_id="";
 200
 201         foreach (@record) {             # Parse the lines of data into variables.
 202
 203         # Initial cleanup of junk from the line of data
 204         s/^\s+//;       # Strip leading spaces.
 205         s/^Quintum-//;  # Strip leading "Quintum-".
 206         chomp;          # Strip trailing CR
 207
 208         $AcctStatusType = $_ if s/Acct-Status-Type = //;
 209         if ($AcctStatusType eq "Stop") {                # All the data we need is in Stop records.
 210         } elsif ($AcctStatusType eq "Start") {
 211                 if ($verbose > 1) { print "DEBUG: Skipping \"Start\" record\n"; }
 212                 return;
 213         } elsif ($AcctStatusType eq "Alive"){
 214                 if ($verbose > 1) { print "DEBUG: Skipping \"Alive\" record\n"; }
 215                 return;
 216         };
 217
 218         $UserName = $_ if s/User-Name = //;
 219         $NasIPAddress = $_ if s/NAS-IP-Address = //;
 220         $AcctSessionTime = $_ if s/Acct-Session-Time = //;
 221         $AcctInputOctets = $_ if s/Acct-Input-Octets = //;
 222         $AcctOutputOctets = $_ if s/Acct-Output-Octets = //;
 223         $AcctDelayTime = $_ if s/Acct-Delay-Time = //;
 224         $Called_Station_Id = $_ if s/Called-Station-Id = //;
 225         $Calling_Station_Id = $_ if s/Calling-Station-Id = //;
 226         $Cisco_NAS_Port = $_ if s/Cisco-NAS-Port = //;
 227         $Timestamp = $_ if s/Timestamp = //;
 228         if (s/h323-call-type = \"h323-call-type=//) {
 229                         $h323_call_type = substr($_, 0, -1);
 230                 } elsif (s/h323-call-type = //) {
 231                         $h323_call_type = $_;
 232             };
 233         if (s/h323-remote-address = \"h323-remote-address=//) {
 234                         $h323_remote_address = $_;
 235                 } elsif (s/h323-remote-address = //) {
 236                         $h323_remote_address = $_;
 237             };
 238         if (s/h323-disconnect-cause = \"h323-disconnect-cause=//) {
 239                         $h323_disconnect_cause = $_;
 240                 } elsif (s/h323-disconnect-cause = //) {
 241                         $h323_disconnect_cause = $_;
 242             };
 243         if (s/h323-conf-id = \"h323-conf-id=//) {
 244                         $h323_conf_id = substr($_, 0, -1);
 245                 } elsif (s/h323-conf-id = //) {
 246                         $h323_conf_id = $_;
 247             };
 248         if (s/h323-connect-time = \"h323-connect-time=//) {
 249                         $h323_connect_time = substr($_, 0, -1);
 250                 } elsif (s/h323-connect-time = //) {
 251                         $h323_connect_time = $_;
 252             };
 253         if (s/h323-disconnect-time = \"h323-disconnect-time=//) {
 254                         $h323_disconnect_time = substr($_, 0, -1);
 255                 } elsif (s/h323-disconnect-time = //) {
 256                         $h323_disconnect_time = $_;
 257             };
 258         if (s/h323-setup-time = \"h323-setup-time=//) {
 259                         $h323_setup_time = substr($_, 0, -1);
 260                 } elsif (s/h323-setup-time = //) {
 261                         $h323_setup_time = $_;
 262             };
 263         if (s/h323-call-origin = \"h323-call-origin=//) {
 264                         $h323_call_origin = substr($_, 0, -1);
 265                 } elsif (s/h323-call-origin = //) {
 266                         $h323_call_origin = $_;
 267             };
 268         if (s/h323-gw-id = \"h323-gw-id=//) {
 269                         $h323_gw_id = substr($_, 0, -1);
 270                 } elsif (s/h323-gw-id = //) {
 271                         $h323_gw_id = $_;
 272             };
 273         if (s/h323-voice-quality = \"h323-voice-quality=//) {
 274                         $h323_voice_quality = substr($_, 0, -1);
 275                 } elsif (s/h323-voice-quality = //) {
 276                         $h323_voice_quality = $_;
 277             };
 278                 # FIXME: ugh, definitely look into using backreference.
 279                 # something like s/(\S+)\s*=\s*\1/\1 = / or so
 280           }
 281
 282
 283         # Remove quotation marks from a bunch of different fields (Stupid Cisco)
 284         $UserName =~ s/\"//g;
 285         $h323_remote_address =~ s/\"//g;
 286         $Called_Station_Id =~ s/\"//g;
 287         $h323_disconnect_cause =~ s/\"//g;
 288         $h323_setup_time =~ s/\"//g;
 289         $h323_connect_time =~ s/\"//g;
 290         $h323_disconnect_time =~ s/\"//g;
 291         $h323_conf_id =~ s/\"//g;
 292         $h323_call_type =~ s/\"//g;
 293         $h323_call_origin =~ s/\"//g;
 294         $h323_voice_quality =~ s/\"//g;
 295         $Cisco_NAS_Port =~ s/\"//g;
 296
 297         # Remove Remove . from the start of time fields (routers that have lost ntp timesync temporarily)
 298         $h323_setup_time =~ s/^\.*//;
 299         $h323_connect_time =~ s/^\.*//;
 300         $h323_disconnect_time =~ s/^\.*//;
 301
 302         # Ignore broken fields from some stupid, non-cisco gateways (They shall remain nameless)
 303         if ($h323_connect_time eq "0") { $h323_connect_time = "" };
 304         if ($h323_disconnect_time eq "0") { $h323_disconnect_time = "" };
 305
 306         # If its a valid record continue onto the database functions
 307         # FIXME: More checks needed here.
 308         if ($h323_call_type) {
 309                 $passno++;
 310                 #@duplicate_records{$passno} += @record;
 311                 if (&procedure_get()) { &procedure_insert; }
 312                 else { &db_read; }
 313         } else { if ($verbose > 1) { print "DEBUG: Skipping non-h323 record\n"; } }
 314 }
 315
 316 sub read_record {
 317         my $keepreading = 1;
 318         @record = ();
 319         while ($keepreading) {
 320                 $_ = <DETAIL>;
 321                 print "$_" if ($verbose > 1);
 322                 if ( /^$/ ) {
 323                         $keepreading = 0;       # End of record
 324                 } else {
 325                         $record[++$#record] = $_;
 326                 }
 327         }
 328         &process_record;
 329 }
 330
 331 sub read_detailfile {
 332         my $file_starttime = time(); my $filename = shift; my @record = (); my $record_no = 0;
 333         if ($verbose > 1) { print "DEBUG: Reading detail file: $filename\n" }
 334         if ((-r $filename) != 1) {              # test if the file exists and is readable
 335                 if ($verbose >= 0) { print "INFO: Skipping file \"$filename\" as it is not readable or does not exist.\n" }
 336                 return;
 337          }
 338         if ( $filename =~ /.gz$/ ) {            # Deal with compressed files
 339                 open (DETAIL, "$GZCAT $filename |") || warn "read_detailfile(\"$filename\"): $!\n";
 340         } elsif ( $filename =~ /.Z$/ ) {
 341                 open (DETAIL, "$ZCAT $filename |") || warn "read_detailfile(\"$filename\"): $!\n";
 342         } elsif ( $filename =~ /.bz2$/ ) {
 343                 open (DETAIL, "$BZCAT $filename |") || warn "read_detailfile(\"$filename\"): $!\n";
 344         } else {
 345                 open (DETAIL, "<$filename") || warn "read_detailfile(\"$filename\"): $!\n";
 346         }
 347         $valid_input = (eof(DETAIL) ? 0 : 1);
 348         if ($verbose > 1) { print "DEBUG: Starting to read records from $filename\n"; }
 349         while($valid_input) {
 350                 $valid_input = 0 if (eof(DETAIL));
 351                 if ($verbose > 1) { print "DEBUG: Reading Record\n"; }
 352                 &read_record;
 353                 $record_no++;
 354         }
 355         my $file_runtime = (time() - $file_starttime);
 356         if ($file_runtime < 1) { $file_runtime = 1; }
 357         my $file_speed = ($record_no / $file_runtime);
 358         if ($verbose >= 0) { print "\n $record_no total records read from $filename were processed in $file_runtime seconds ($file_speed records/sec) \n"; }
 359 }
 360
 361 sub print_usage_info {
 362         print "\n";
 363         $leader = "$progname $version Usage Information";
 364         $underbar = $leader;
 365         $underbar =~ s/./-/g;
 366         print "$leader\n$underbar\n";
 367         print "\n";
 368         print "  Syntax:   h323detail2db.pl [ options ] detailfile(s)\n";
 369         print "\n";
 370         print "    -d --database                    Database to use\n";
 371         print "    -h --help                        Show this usage information\n";
 372         print "    -H --host                        Database host to connect to (Default: localhost)\n";
 373         print "    -p --procedure                   Use Postgresql stored procedure (BROKEN!)\n";
 374         print "    -q --quiet                       Turn on quiet mode (No Output)\n";
 375         print "    -v --verbose                     Turn on verbose\n";
 376         print "    -V --version                     Show version and copyright\n";
 377         print "    -x --debug                       Turn on debugging\n";
 378         print "\n";
 379 }
 380
 381 sub procedure_get() {
 382         return $stored_procedure;
 383 }
 384
 385 sub procedure_set($) {
 386         $stored_procedure = $_[0];
 387 }
 388
 389
 390 sub main {
 391         # Parse the command line for options
 392         if (!scalar(@ARGV)) {
 393                 &print_usage_info();
 394                 exit(SUCCESS);
 395         };
 396
 397         # See the Getopt::Long man page for details on the syntax of this line
 398         @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");
 399         Getopt::Long::Configure("no_getopt_compat", "bundling", "no_ignore_case");
 400         Getopt::Long::GetOptions(@valid_opts);
 401
 402         # Post-parse the options stuff
 403         select STDOUT; $| = 1;
 404         if ($opt_V) {
 405                 # Do not edit this variable.  It is updated automatically by CVS when you commit
 406                 my $rcs_info = 'CVS Revision $Revision$ created on $Date$ by $Author$ ';
 407
 408                 $rcs_info =~ s/\$\s*Revision: (\S+) \$/$1/;
 409                 $rcs_info =~ s/\$\s*Date: (\S+) (\S+) \$/$1 at $2/;
 410                 $rcs_info =~ s/\$\s*Author: (\S+) \$ /$1/;
 411
 412                 print "\n";
 413                 print "$progname Version $version by Peter Nixon - http://www.peternixon.net/\n";
 414                 print "Copyright (c) 2002-2004 Peter Nixon\n";
 415                 print "  ($rcs_info)\n";
 416                 print "\n";
 417                 return SUCCESS;
 418         } elsif ($opt_h) {
 419                 &print_usage_info();
 420                 exit(SUCCESS);
 421         }
 422
 423         if ($opt_x) {
 424                 print "DEBUG: Debug mode is enabled.\n";
 425                 $verbose = 2;
 426         } elsif ($quiet) { $verbose -= $quiet; }
 427         &procedure_set($opt_p);
 428         if ($opt_d) {
 429                 if ($verbose > 0) { print "Using database \"$opt_d\" instead of default database \"$database\"\n"; }
 430                 $database = $opt_d;
 431         }
 432
 433         if (@ARGV) {
 434                 my $db_host;
 435                 if ($opt_H) { $db_host = $opt_H; }
 436                 else { $db_host = "localhost"; }
 437                 &db_connect($db_host);
 438
 439                 # Loop through the defined files
 440                 foreach $file (@ARGV) {
 441                         &read_detailfile($file);
 442                 }
 443                 &process_duplicates;
 444                 &db_disconnect($db_host);
 445
 446                 my $runtime = (time() - $starttime);
 447                 if ($runtime < 1) { $runtime = 1; }
 448                 my $speed = ($passno / $runtime);
 449                 if ($verbose >= 0) { print "\n $passno valid records were processed in $runtime seconds ($speed records/sec) \n"; }
 450         } else {
 451                 print "ERROR: Please specify one or more detail file(s) to import.\n";
 452                 exit(FAILURE);
 453         }
 454
 455 }
 456
 457
 458 exit &main();