src/billing/h323detail2db.pl

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