3 # syslog2db - Extract Clarent VoIP CDRs from billing_record files and
4 # insert them into a Postgresql database.
6 # Author: Peter Nixon <codemonkey@peternixon.net>
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/
18 # Modules we use to make things easier
25 #use strict; # Errrm. That looks like effort :-)
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";
37 #### You should not have to modify anything below here
39 $| = 1; #Unbuffered output
40 my $progname = "clarent2db.pl";
41 my $progname_long = "Clarent Billing Record to DB Importer";
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();
49 # Database Information
50 my $database = "clarent";
51 my $defaulthostname = "localhost";
53 my $user = "postgres";
57 my $defaulttimezone = "UTC";
58 my $defaultyear = 2003;
61 my %working_record = ();
64 'Jan' => '01', 'Feb' => '02', 'Mar' => '03',
65 'Apr' => '04', 'May' => '05', 'Jun' => '06',
66 'Jul' => '07', 'Aug' => '08', 'Sep' => '09',
67 'Oct' => '10', 'Nov' => '11', 'Dec' => '12',
68 'jan' => '01', 'feb' => '02', 'mar' => '03',
69 'apr' => '04', 'may' => '05', 'jun' => '06',
70 'jul' => '07', 'aug' => '08', 'sep' => '09',
71 'oct' => '10', 'nov' => '11', 'dec' => '12',
76 if ($verbose > 1) { print "DEBUG: Connecting to Database Server: $hostname\n" }
77 if ($hostname eq 'localhost') {
78 if ($verbose > 1) { print "DEBUG: localhost connection so using UNIX socket instead of network socket.\n" }
79 $dbh = DBI->connect("DBI:Pg:dbname=$database", "$user", "$password")
80 or die "Couldn't connect to database: " . DBI->errstr;
83 $dbh = DBI->connect("DBI:Pg:dbname=$database;host=$hostname", "$user", "$password")
84 or die "Couldn't connect to database: " . DBI->errstr;
89 ### Now, disconnect from the database
90 if ($verbose > 1) { print "DEBUG: Disconnecting from Database Server\n" }
92 or warn "Disconnection failed: $DBI::errstr\n";
97 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"; }
98 my $sth = $dbh->prepare("SELECT ID FROM billing_record
100 AND ip_addr_ingress = ?
102 or die "Couldn't prepare statement: " . $dbh->errstr;
105 $sth->execute($working_record{start_time}, $working_record{ip_addr_ingress}, $working_record{h323confid}) # Execute the query
106 or die "Couldn't execute statement: " . $sth->errstr;
107 my $returned_rows = $sth->rows;
109 if ($sth->rows == 0) {
111 } elsif ($sth->rows == 1) {
112 if ($verbose > 0) { print "Exists in DB.\n"; }
115 # FIXME: Log this somewhere!
116 print "********* More than One Match! We have a problem!\n";
124 $sth2 = $dbh->prepare("INSERT into billing_record (local_SetupTime, start_time, duration, service_code, phone_number,
125 ip_addr_ingress, ip_addr_egress, bill_type, disconnect_reason, extended_reason_code, dialed_number, codec, h323ConfID, port_number)
126 values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
128 $sth2->execute($working_record{local_setuptime}, $working_record{start_time}, $working_record{duration},
129 $working_record{service_code}, $working_record{phone_number}, $working_record{ip_addr_ingress}, $working_record{ip_addr_egress},
130 $working_record{bill_type}, $working_record{disconnect_reason}, $working_record{extended_reason_code}, $working_record{dialed_number},
131 $working_record{codec}, $working_record{h323confid}, $working_record{port_number});
132 #my $returned_rows = $sth2->rows;
133 if ($verbose > 0) { print "$sth2->rows rows added to DB\n"; }
139 my $filename = shift;
140 if ($verbose > 1) { print "DEBUG: Reading detail file: $filename\n" }
141 if ( $filename =~ /.gz$/ ) {
142 open (FILE, "$GZCAT $filename |") || warn "read_detailfile(\"$filename\"): $!\n";
143 } elsif ( $filename =~ /.Z$/ ) {
144 open (FILE, "$ZCAT $filename |") || warn "read_detailfile(\"$filename\"): $!\n";
145 } elsif ( $filename =~ /.bz2$/ ) {
146 open (FILE, "$BZCAT $filename |") || warn "read_detailfile(\"$filename\"): $!\n";
148 open (FILE, "<$filename") || warn "read_detailfile(\"$filename\"): $!\n";
150 $valid_input = (eof(FILE) ? 0 : 1);
151 if ($verbose > 1) { print "DEBUG: Starting to read records from $filename\n"; }
152 while($valid_input) {
153 $valid_input = 0 if (eof(FILE));
154 if ($verbose > 2) { print "DEBUG: Reading Record\n"; }
157 if ($verbose > 1) { print "DEBUG Raw Record: $_"; }
163 sub record_match($) {
166 # Spilt the Call record up into fields
167 my @callrecord = split(/,/, $_);
169 if (scalar(@callrecord) == 70) { # Check that we have the right number of fields for a Clarent record
170 if ($verbose > 1) { print "DEBUG: Clean Record: @callrecord\n"; }
171 $recordno++; %working_record = ();
172 $working_record{local_setuptime} = clarent2normaltime($callrecord[0]);
173 $working_record{start_time} = $callrecord[3]; # This is in Unix timetamp format, relative to the originating gateway.
174 # It is therefore useless unless ALL gateways are set with the same timezone,
175 # so I don't bother to convert it to datetime format.
176 $working_record{duration} = $callrecord[4];
177 $working_record{service_code} = $callrecord[5];
178 $working_record{phone_number} = $callrecord[6];
179 $working_record{ip_addr_ingress} = $callrecord[7];
180 $working_record{ip_addr_egress} = $callrecord[8];
181 $working_record{h323confid} = $callrecord[9];
182 $working_record{bill_type} = $callrecord[12];
183 $working_record{disconnect_reason} = $callrecord[15];
184 $working_record{extended_reason_code} = $callrecord[16];
185 $working_record{port_number} = $callrecord[21];
186 $working_record{dialed_number} = $callrecord[60];
187 $working_record{codec} = $callrecord[67];
191 } else { if ($verbose > 1) { print "DEBUG: ERROR: Record is not in Clarent format: $str\n"; } }
196 sub clarent2normaltime($) {
198 (\S{3})\/(\d+)\/(\d{4}) # Month Day Year
200 (\d+):(\d+):(\d+) # Hour Min Sec
204 \w*?:? # RESEND: (Discarded)
206 \S{1} # U (Discarded) FIXME: does anyone know what this value means??
208 my $month = $months_map{$1}; defined $month or croak "ERROR: Unknown month \"$1\"\n";
214 return "$years-$month-$days $hours:$minutes:$seconds";
216 if ($verbose > 0) { print "ERROR: Not in Clarent time format: $str\n"; }
221 sub print_usage_info {
223 my $leader = "$progname_long Ver: $version Usage Information";
224 my $underbar = $leader;
225 $underbar =~ s/./-/g;
226 print "$leader\n$underbar\n";
228 print " Syntax: $progname [ options ] file\n";
230 print " -h --help Show this usage information\n";
231 print " -v --verbose Turn on verbose\n";
232 print " -x --debug Turn on debugging\n";
233 print " -V --version Show version and copyright\n";
234 print " -H --host Database host to connect to (Default: localhost)\n";
239 # Parse the command line for options
240 if (!scalar(@ARGV)) {
246 # See the Getopt::Long man page for details on the syntax of this line
247 @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");
248 Getopt::Long::Configure("no_getopt_compat", "bundling", "no_ignore_case");
249 Getopt::Long::GetOptions(@valid_opts);
251 # Post-parse the options stuff
252 select STDOUT; $| = 1;
254 # Do not edit this variable. It is updated automatically by CVS when you commit
255 my $rcs_info = 'CVS Revision $Revision$ created on $Date$ by $Author$ ';
257 $rcs_info =~ s/\$\s*Revision: (\S+) \$/$1/;
258 $rcs_info =~ s/\$\s*Date: (\S+) (\S+) \$/$1 at $2/;
259 $rcs_info =~ s/\$\s*Author: (\S+) \$ /$1/;
262 print "$progname Version $version by Peter Nixon <codemonkey\@peternixon.net>\n";
263 print "Copyright (c) 2003 Peter Nixon\n";
264 print " ($rcs_info)\n";
273 print "DEBUG: Debug mode is enabled.\n";
275 } elsif ($quiet) { $verbose -= $quiet; }
278 if ($opt_H) { &db_connect($opt_H);
279 } else { &db_connect($defaulthostname); }
281 if (scalar(@ARGV) > 1) {
282 foreach $file (@ARGV) { # Loop through the defined files
291 my $runtime = (time() - $starttime);
292 if ($runtime < 1) { $runtime = 0.5; } # Prevent divide-by-zero errors
293 my $speed = ($recordno / $runtime);
295 print "\n$recordno records from $lineno lines in $fileno files were processed in ~$runtime seconds (~$speed records/sec)\n";
297 print "\n$recordno records from $lineno lines in $file were processed in ~$runtime seconds (~$speed records/sec)\n";
303 print "ERROR: Please specify one or more detail files to import.\n";