src/billing/clarent2db.pl

   1 #!/usr/bin/perl
   2 #
   3 # syslog2db - Extract Clarent VoIP CDRs from billing_record files and
   4 # insert them into a Postgresql database.
   5 #
   6 # Author:       Peter Nixon <codemonkey@peternixon.net>
   7 # Date:         2003-05-07
   8 # Summary:      Clarent, VoIP, CDR, database, postgresql
   9 # Copyright:    2002, Peter Nixon <codemonkey@peternixon.net>
  10 # Copy Policy:  Free to copy and distribute provided all headers are left
  11 #               intact and no charge is made for this program.  I would
  12 #               appreciate copies of any modifications to the script.
  13 # URL:          http://www.peternixon.net/code/
  14 #
  15 # $Id$
  16
  17
  18 # Modules we use to make things easier
  19 use POSIX;
  20 require DBI;
  21 require Getopt::Long;
  22 use Carp;
  23 use Symbol;
  24 use Time::Local;
  25 #use strict;    # Errrm. That looks like effort :-)
  26
  27
  28 # Program and File locations
  29 # gzcat - 'cat for .gz / gzip files'
  30 # If you don't have gzcat and do have gzip then use: ln gzip gzcat
  31 my $GZCAT = "/usr/bin/zcat";
  32 # zcat - 'cat for .Z / compressed files'
  33 my $ZCAT = "/usr/bin/zcat";
  34 # bzcat - 'cat for .bz2 files'
  35 my $BZCAT = "/usr/bin/bzcat";
  36
  37 #### You should not have to modify anything below here
  38
  39 $| = 1;         #Unbuffered output
  40 my $progname = "clarent2db.pl";
  41 my $progname_long = "Clarent Billing Record to DB Importer";
  42 my $version = 0.2;
  43
  44 # Set up some basic variables
  45 my $double_match_no = 0; my $verbose = 0; my $recordno = 0; my $fileno = 0; my $lineno = 0;
  46 my $starttime = time();
  47
  48
  49 # Database Information
  50 my $database    = "clarent";
  51 my $defaulthostname    = "ist-db1";
  52 my $port        = "3306";
  53 my $user        = "postgres";
  54 my $password    = "";
  55
  56 # Defaults
  57 my $defaulttimezone = "UTC";
  58 my $defaultyear = 2002;
  59 my $dbh;
  60
  61 # fast timelocal
  62 my $str2time_last_time;
  63 my $str2time_last_day;
  64 my $str2time_last_month;
  65 my $enable_year_decrement = 1; # year-increment algorithm: if in january, if december is seen, decrement
  66                                # year
  67 my %working_record = ();
  68
  69 my %months_map = (
  70     'Jan' => 0, 'Feb' => 1, 'Mar' => 2,
  71     'Apr' => 3, 'May' => 4, 'Jun' => 5,
  72     'Jul' => 6, 'Aug' => 7, 'Sep' => 8,
  73     'Oct' => 9, 'Nov' =>10, 'Dec' =>11,
  74     'jan' => 0, 'feb' => 1, 'mar' => 2,
  75     'apr' => 3, 'may' => 4, 'jun' => 5,
  76     'jul' => 6, 'aug' => 7, 'sep' => 8,
  77     'oct' => 9, 'nov' =>10, 'dec' =>11,
  78 );
  79
  80 sub db_connect {
  81         my $hostname = shift;
  82         if ($verbose > 1) { print "DEBUG: Connecting to Database Server: $hostname\n" }
  83         if ($hostname eq 'localhost') {
  84         if ($verbose > 1) { print "DEBUG: localhost connection so using UNIX socket instead of network socket.\n" }
  85                 $dbh = DBI->connect("DBI:Pg:dbname=$database", "$user", "$password")
  86                         or die "Couldn't connect to database: " . DBI->errstr;
  87         }
  88         else {
  89                 $dbh = DBI->connect("DBI:Pg:dbname=$database;host=$hostname", "$user", "$password")
  90                         or die "Couldn't connect to database: " . DBI->errstr;
  91         }
  92 }
  93
  94 sub db_disconnect {
  95         ### Now, disconnect from the database
  96         if ($verbose > 1) { print "DEBUG: Disconnecting from Database Server\n" }
  97         $dbh->disconnect
  98             or warn "Disconnection failed: $DBI::errstr\n";
  99 }
 100
 101 sub db_read {
 102         $passno++;
 103         if ($verbose > 0) { print "Record: $passno) Conf ID: $working_record{h323confid}   Start Time: $working_record{start_time} IP: $working_record{ip_addr_egress} Call Length: $working_record{duration}\n"; }
 104         my $sth = $dbh->prepare("SELECT Id FROM billing_record
 105                 WHERE start_time= ?
 106                 AND ip_addr_ingress = ?
 107                 AND h323confid = ?")
 108                 or die "Couldn't prepare statement: " . $dbh->errstr;
 109
 110           my @data;
 111           $sth->execute($working_record{start_time}, $working_record{ip_addr_egress}, $working_record{h323confid})             # Execute the query
 112             or die "Couldn't execute statement: " . $sth->errstr;
 113            my $returned_rows = $sth->rows;
 114
 115           if ($sth->rows == 0) {
 116                 &db_insert;
 117           } elsif ($sth->rows == 1) {
 118                 if ($verbose > 0) { print "Exists in DB.\n"; }
 119           } else {
 120                 $double_match_no++;
 121                 # FIXME: Log this somewhere!
 122                 print "********* More than One Match! We have a problem!\n";
 123           }
 124
 125         $sth->finish;
 126
 127 }
 128
 129 sub db_insert {
 130         $sth2 = $dbh->prepare("INSERT into billing_record (local_SetupTime, start_time, duration, service_code, phone_number,
 131                 ip_addr_ingress, ip_addr_egress, bill_type, disconnect_reason, extended_reason_code, dialed_number, codec, h323ConfID)
 132                 values(?,?,?,?,?,?,?,?,?,?,?,?,?)");
 133
 134          $sth2->execute($working_record{local_setuptime}, $working_record{start_time}, $working_record{duration},
 135                 $working_record{service_code}, $working_record{phone_number}, $working_record{ip_addr_egress}, $working_record{ip_addr_egress},
 136                 $working_record{bill_type}, $working_record{disconnect_reason}, $working_record{extended_reason_code}, $working_record{dialed_number},
 137                 $working_record{codec}, $working_record{h323confid});             # Execute the query
 138         #my $returned_rows = $sth2->rows;
 139         if ($verbose > 0) { print "$sth2->rows rows added to DB\n"; }
 140         $sth2->finish();
 141
 142 }
 143
 144 sub file_read {
 145         my $filename = shift;
 146         if ($verbose > 1) { print "DEBUG: Reading detail file: $filename\n" }
 147         if ( $filename =~ /.gz$/ ) {
 148                 open (FILE, "$GZCAT $filename |") || warn "read_detailfile(\"$filename\"): $!\n";
 149         } elsif ( $filename =~ /.Z$/ ) {
 150                 open (FILE, "$ZCAT $filename |") || warn "read_detailfile(\"$filename\"): $!\n";
 151         } elsif ( $filename =~ /.bz2$/ ) {
 152                 open (FILE, "$BZCAT $filename |") || warn "read_detailfile(\"$filename\"): $!\n";
 153         } else {
 154                 open (FILE, "<$filename") || warn "read_detailfile(\"$filename\"): $!\n";
 155         }
 156         $valid_input = (eof(FILE) ? 0 : 1);
 157         if ($verbose > 1) { print "DEBUG: Starting to read records from $filename\n"; }
 158         while($valid_input) {
 159                 $valid_input = 0 if (eof(FILE));
 160                 if ($verbose > 1) { print "DEBUG: Reading Record\n"; }
 161                 $_ = <FILE>;
 162                 $lineno++;
 163                 if ($verbose > 1) { print "DEBUG RECORD: $_"; }
 164                 #&record_mangle($_);
 165                 &record_match($_);
 166         }
 167 }
 168
 169 # 0: sec, 1: min, 2: h, 3: day, 4: month, 5: year
 170 sub str2timez($$$$$$$) {
 171     my $GMT = pop @_;
 172     my $day_secs = $_[2]*3600+$_[1]*60+$_[0];
 173     if(defined $str2time_last_time) {
 174         if( $_[3] == $str2time_last_day and
 175             $_[4] == $str2time_last_month )
 176         {
 177             return $str2time_last_time + $day_secs;
 178         }
 179     }
 180
 181     my $time;
 182     if($GMT) {
 183         $time = timegm(@_);
 184     }
 185     else {
 186         $time = timelocal(@_);
 187     }
 188
 189     $str2time_last_time = $time - $day_secs;
 190     $str2time_last_day = $_[3];
 191     $str2time_last_month = $_[4];
 192
 193     return $time;
 194 }
 195
 196 sub getseconds($$) {
 197         my $time1 = pop @_;
 198         my $time2 = pop @_;
 199
 200         my $seconds = $time = str2time($time1) - str2time($time2);
 201         if ($verbose > 0) { print "******** Seconds: $seconds\n"; }
 202         return $seconds;
 203 }
 204
 205 sub record_match($) {
 206         chomp($_);
 207         #if ($verbose > 1) { print "DEBUG Record: $_\n"; }
 208         # Check to see if it is a Clarent Billing record
 209         if ( /^
 210             (\S{3})\/(\d+)\/(\d{4})     # Month Day Year
 211             \s
 212             (\d+):(\d+):(\d+)           # Hour Min Sec
 213             \s
 214             (\d{4})                     # msec??
 215             \s
 216             \w*?:?                      # RESEND: (Discarded)
 217             \s?
 218             \S{1},                      # U (Discarded)
 219             (\w+),                      # EGRESS
 220             (\w+),                      # ISTOUT
 221             (\d+),                      # start_time
 222             (\d+),                      # duration
 223             (\w),                       # service_code
 224             (\w+-\w+-\w+),              # phone_number
 225             (\d+\.\d+\.\d+\.\d+),       # ip_addr_ingress
 226             (\d+\.\d+\.\d+\.\d+),       # ip_addr_egress
 227             (\d+),                      # h323confid
 228             \w*,                        #
 229             (\w+),                      #
 230             (\w+),                      #
 231             (\d+),                      #
 232             (\w+),                      #
 233             (\w{2}),                    # disconnect_reason
 234             (\w{2}),                    # extended_reason_code
 235             (.*),                               # text we don't care about
 236             (\d+),                      # dialed_number??
 237             \w*,                        #
 238             \w*,                        #
 239             (\w+),                      # codec
 240             \w*,                        #
 241             \w*                         #
 242             /x ) {
 243                 $recordno++; %working_record = ();
 244                 if ($verbose > 0) { print "DEBUG: Cleaned Record: $3-$1-$2 $4:$5:$6 $7 $8 : $9 $10 $11 $12 $13 $14 $15 $16 $17 $18 $19 $20 $21 $22 $23 $24 :: $25 $26 $27 $28 $29\n"; }
 245                 # parse out values
 246                 my $month = $months_map{$1}; defined $month or croak "ERROR: Unknown month \"$1\"\n";
 247                 my $days = $2;
 248                 my $years = $3;
 249                 my $hours = $4;
 250                 my $minutes = $5;
 251                 my $seconds = $6;
 252                 $working_record{local_setuptime} = "$years-$month-$days $hours:$minutes:$seconds";
 253                 $working_record{start_time} = $10;
 254                 $working_record{duration} = $11;
 255                 $working_record{service_code} = $12;
 256                 $working_record{phone_number} = $13;
 257                 $working_record{ip_addr_ingress} = $14;
 258                 $working_record{ip_addr_egress} = $15;
 259                 $working_record{h323confid} = $16;
 260
 261         #       $cust_city_call_totals{$custname}{$nascity} += $mins;
 262                 # convert to unix time 0: sec, 1: min, 2: h, 3: day, 4: month, 5: year
 263                 my $unixtime = str2timez($seconds,$minutes,$hours,$days,$month,$years,$defaulttimezone);
 264                 if ($verbose > 0) { print "DEBUG: Time is $unixtime\n"; }
 265                 &db_read;
 266         } elsif ( /^
 267             (\S{3})\/(\d+)\/(\d{4})     # Month Day Year
 268             \s
 269             (\d+):(\d+):(\d+)           # Hour Min Sec
 270             \s
 271             (\d{4})                     # msec??
 272             \s
 273             FAILED\sTO\sSEND            # FAILED TO SEND (Discarded)
 274             /x ) {
 275                                 # Broken FAILED TO SEND record with date time and nothing else
 276         } elsif ( /^
 277             (\S{3})\/(\d+)\/(\d{4})     # Month Day Year
 278             \s
 279             (\d+):(\d+):(\d+)           # Hour Min Sec
 280             \s
 281             (\d{4})                     # msec??
 282             \s
 283             FAILED\sTO\sWRITE
 284                 \srcd\s\=\s\d+          # FAILED TO WRITE (Discarded)
 285             /x ) {
 286                                 # Broken FAILED TO WRITE record with date time and nothing else
 287         } elsif ( /^
 288             (\S{3})\/(\d+)\/(\d{4})     # Month Day Year
 289             \s
 290             (\d+):(\d+):(\d+)           # Hour Min Sec
 291             \s
 292             (\d{4})                     # msec??
 293             /x ) {
 294                                 # Broken record with date time and nothing else
 295         } else {
 296             if ($verbose > 0) { print "ERROR: Record is not in Clarent format: $str\n"; }
 297         };
 298 }
 299
 300 sub print_usage_info {
 301         print "\n";
 302         my $leader = "$progname_long Ver: $version Usage Information";
 303         my $underbar = $leader;
 304         $underbar =~ s/./-/g;
 305         print "$leader\n$underbar\n";
 306         print "\n";
 307         print "  Syntax:   $progname [ options ] file\n";
 308         print "\n";
 309         print "    -h --help                        Show this usage information\n";
 310         print "    -v --verbose                     Turn on verbose\n";
 311         print "    -x --debug                       Turn on debugging\n";
 312         #print "    -p --procedure                   Use Postgresql stored procedure (faster!)\n";
 313         print "    -V --version                     Show version and copyright\n";
 314         print "    -H --host                        Database host to connect to (Default: localhost)\n";
 315         print "\n";
 316 }
 317
 318 sub procedure_get() {
 319         return $stored_procedure;
 320 }
 321
 322 sub procedure_set($) {
 323         $stored_procedure = $_[0];
 324 }
 325
 326
 327 sub main {
 328         # Parse the command line for options
 329         if (!scalar(@ARGV)) {
 330                 &print_usage_info();
 331                 exit(SUCCESS);
 332         };
 333         my $quiet = 0;
 334
 335         # See the Getopt::Long man page for details on the syntax of this line
 336         @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");
 337         Getopt::Long::Configure("no_getopt_compat", "bundling", "no_ignore_case");
 338         Getopt::Long::GetOptions(@valid_opts);
 339
 340         # Post-parse the options stuff
 341         select STDOUT; $| = 1;
 342         if ($opt_V) {
 343                 # Do not edit this variable.  It is updated automatically by CVS when you commit
 344                 my $rcs_info = 'CVS Revision $Revision$ created on $Date$ by $Author$ ';
 345
 346                 $rcs_info =~ s/\$\s*Revision: (\S+) \$/$1/;
 347                 $rcs_info =~ s/\$\s*Date: (\S+) (\S+) \$/$1 at $2/;
 348                 $rcs_info =~ s/\$\s*Author: (\S+) \$ /$1/;
 349
 350                 print "\n";
 351                 print "$progname Version $version by Peter Nixon <codemonkey\@peternixon.net>\n";
 352                 print "Copyright (c) 2003 Peter Nixon\n";
 353                 print "  ($rcs_info)\n";
 354                 print "\n";
 355                 return SUCCESS;
 356         } elsif ($opt_h) {
 357                 &print_usage_info();
 358                 exit(SUCCESS);
 359         }
 360
 361         if ($opt_x) {
 362                 print "DEBUG: Debug mode is enabled.\n";
 363                 $verbose = 2;
 364         } elsif ($quiet) { $verbose -= $quiet; }
 365         &procedure_set($opt_p);
 366
 367         if (@ARGV) {
 368                 if ($opt_H) { &db_connect($opt_H);
 369                 } else { &db_connect($defaulthostname); }
 370
 371                 # Loop through the defined files
 372                 foreach my $file (@ARGV) {
 373                         $fileno++;
 374                         &file_read($file);
 375                 }
 376         my $runtime = (time() - $starttime);
 377         if ($runtime > 0) {
 378         } else { $runtime = 1; }
 379         my $speed = ($recordno / $runtime);
 380         if ($verbose >= 0) {
 381                 if ($fileno > 1) {
 382                         print "\n $recordno records from $lineno lines in $fileno files were processed in ~$runtime seconds (~$speed records/sec) \n";
 383                 } else {
 384                         print "\n $recordno records from $lineno lines in $filename were processed in ~$runtime seconds (~$speed records/sec) \n";
 385                 }
 386         }
 387
 388                 &db_disconnect;
 389         } else {
 390                 print "ERROR: Please specify one or more detail file(s) to import.\n";
 391                 exit(FAILURE);
 392         }
 393 }
 394
 395 exit &main();