From a5fba19707ec1a01db18fa55862e742170feccdf Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Thu, 8 Mar 2012 13:37:53 -0800 Subject: [PATCH] match CDRs to services by IP address, #16723 --- FS/FS/Schema.pm | 5 +++++ FS/FS/cdr.pm | 31 +++++++++++++++++++++++++++++++ FS/FS/part_pkg/voip_cdr.pm | 18 +++++++++++++----- FS/FS/svc_pbx.pm | 8 ++++++++ bin/cdr-opensips.import | 17 +++++++++++++++-- httemplate/search/cdr.html | 11 +++++++++++ httemplate/view/svc_pbx.cgi | 6 ++++-- 7 files changed, 87 insertions(+), 9 deletions(-) diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index b2dddcafc..1112f52d7 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -2940,6 +2940,10 @@ sub tables_hashref { 'lastapp', 'varchar', '', $char_d, \"''", '', 'lastdata', 'varchar', '', $char_d, \"''", '', + #currently only opensips + 'src_ip_addr', 'varchar', 'NULL', 15, '', '', + 'dst_ip_addr', 'varchar', 'NULL', 15, '', '', + #these don't seem to be logged by most of the SQL cdr_* modules #except tds under sql-illegal names, so; # ... don't rely on them for rating? @@ -3039,6 +3043,7 @@ sub tables_hashref { [ 'sessionnum' ], [ 'subscriber' ], [ 'freesidestatus' ], [ 'freesiderewritestatus' ], [ 'cdrbatch' ], [ 'cdrbatchnum' ], + [ 'src_ip_addr' ], [ 'dst_ip_addr' ], ], }, diff --git a/FS/FS/cdr.pm b/FS/FS/cdr.pm index ff07a59ee..9b707194d 100644 --- a/FS/FS/cdr.pm +++ b/FS/FS/cdr.pm @@ -87,6 +87,10 @@ following fields are currently supported: =item lastdata - Last application data +=item src_ip_addr - Source IP address (dotted quad, zero-filled) + +=item dst_ip_addr - Destination IP address (same) + =item startdate - Start of call (UNIX-style integer timestamp) =item answerdate - Answer time of call (UNIX-style integer timestamp) @@ -187,6 +191,8 @@ sub table_info { 'dstchannel' => 'Destination channel', #'lastapp' => '', #'lastdata' => '', + 'src_ip_addr' => 'Source IP', + 'dst_ip_addr' => 'Dest. IP', 'startdate' => 'Start date', 'answerdate' => 'Answer date', 'enddate' => 'End date', @@ -1570,6 +1576,31 @@ sub _upgrade_data { } +=item ip_addr_sql FIELD RANGE + +Returns an SQL condition to search for CDRs with an IP address +within RANGE. FIELD is either 'src_ip_addr' or 'dst_ip_addr'. RANGE +should be in the form "a.b.c.d-e.f.g.h' (dotted quads), where any of +the leftmost octets of the second address can be omitted if they're +the same as the first address. + +=cut + +sub ip_addr_sql { + my $class = shift; + my ($field, $range) = @_; + $range =~ /^[\d\.-]+$/ or die "bad ip address range '$range'"; + my @r = split('-', $range); + my @saddr = split('\.', $r[0] || ''); + my @eaddr = split('\.', $r[1] || ''); + unshift @eaddr, (undef) x (4 - scalar @eaddr); + for(0..3) { + $eaddr[$_] = $saddr[$_] if !defined $eaddr[$_]; + } + "$field >= '".sprintf('%03d.%03d.%03d.%03d', @saddr) . "' AND ". + "$field <= '".sprintf('%03d.%03d.%03d.%03d', @eaddr) . "'"; +} + =back =head1 BUGS diff --git a/FS/FS/part_pkg/voip_cdr.pm b/FS/FS/part_pkg/voip_cdr.pm index 3c456dc02..aaad974cf 100644 --- a/FS/FS/part_pkg/voip_cdr.pm +++ b/FS/FS/part_pkg/voip_cdr.pm @@ -20,6 +20,8 @@ tie my %cdr_svc_method, 'Tie::IxHash', 'svc_phone.phonenum' => 'Phone numbers (svc_phone.phonenum)', 'svc_pbx.title' => 'PBX name (svc_pbx.title)', 'svc_pbx.svcnum' => 'Freeside service # (svc_pbx.svcnum)', + 'svc_pbx.ip.src' => 'PBX name to source IP address', + 'svc_pbx.ip.dst' => 'PBX name to destination IP address', ; tie my %rating_method, 'Tie::IxHash', @@ -75,8 +77,9 @@ tie my %unrateable_opts, 'Tie::IxHash', }, 'cdr_svc_method' => { 'name' => 'CDR service matching method', - 'type' => 'radio', - 'options' => \%cdr_svc_method, +# 'type' => 'radio', + 'type' => 'select', + 'select_options' => \%cdr_svc_method, }, 'rating_method' => { 'name' => 'Rating method', @@ -102,7 +105,7 @@ tie my %unrateable_opts, 'Tie::IxHash', 'calls_included' => { 'name' => 'Number of calls included at no usage charge', }, - 'min_included' => { 'name' => 'Minutes included when using the "single price per minute" rating method or when using the "prefix" rating method ("region group" billing)', + 'min_included' => { 'name' => 'Minutes included when using the "single price per minute" or "prefix" rating method', }, 'min_charge' => { 'name' => 'Charge per minute when using "single price per minute" rating method', @@ -359,7 +362,7 @@ sub calc_usage { my $use_duration = $self->option('use_duration'); - my($svc_table, $svc_field) = split('\.', $cdr_svc_method); + my($svc_table, $svc_field, $by_ip_addr) = split('\.', $cdr_svc_method); my @cust_svc; if( $self->option('bill_inactive_svcs',1) ) { @@ -388,7 +391,12 @@ sub calc_usage { 'status' => '', 'for_update' => 1, ); # $last_bill, $$sdate ) - $options{'by_svcnum'} = 1 if $svc_field eq 'svcnum'; + if ( $svc_field eq 'svcnum' ) { + $options{'by_svcnum'} = 1; + } + elsif ($svc_table eq 'svc_pbx' and $svc_field eq 'ip') { + $options{'by_ip_addr'} = $by_ip_addr; + } #my @invoice_details_sort; diff --git a/FS/FS/svc_pbx.pm b/FS/FS/svc_pbx.pm index 37ab174d2..f8b96050d 100644 --- a/FS/FS/svc_pbx.pm +++ b/FS/FS/svc_pbx.pm @@ -283,6 +283,10 @@ with the chosen prefix. =item by_svcnum => 1: Select CDRs where the svcnum field matches, instead of title/charged_party. Normally this field is set after processing. +=item by_ip_addr => 'src' or 'dst': Select CDRs where the src_ip_addr or +dst_ip_addr field matches title. In this case, some special logic is applied +to allow title to indicate a range of IP addresses. + =item begin, end: Start and end of date range, as unix timestamp. =item cdrtypenum: Only return CDRs with this type number. @@ -309,6 +313,10 @@ sub get_cdrs { if ( $options{'by_svcnum'} ) { $hash{'svcnum'} = $self->svcnum; } + elsif ( $options{'by_ip_addr'} =~ /^src|dst$/) { + my $field = 'cdr.'.$options{'by_ip_addr'}.'_ip_addr'; + push @where, FS::cdr->ip_addr_sql($field, $self->title); + } else { #matching by title my $title = $self->title; diff --git a/bin/cdr-opensips.import b/bin/cdr-opensips.import index 489fac6d6..b8169d523 100755 --- a/bin/cdr-opensips.import +++ b/bin/cdr-opensips.import @@ -82,15 +82,26 @@ while ( $row = $sth->fetchrow_hashref ) { #i guess now we're NANPA-centric, but at least we warn on non-numeric numbers my $src = ''; - if ( $row->{'caller_id'} =~ /^sip:(\+1)?(\d+)@/ ) { + my $src_ip = ''; + if ( $row->{'caller_id'} =~ /^sip:(\+1?)?(\w+)@(.*)/ ) { $src = $2; + my $rest = $3; + if ($rest =~ /^([\d\.]{7,15})/) { + # canonicalize it so that ascii sort order works + $src_ip = sprintf('%03d.%03d.%03d.%03d', split('\.', $1)); + } } else { warn "unparseable caller_id ". $row->{'caller_id'}. "\n"; } my $dst = ''; - if ( $row->{'callee_id'} =~ /^sip:(\+1)?(\d+)@/ ) { + my $dst_ip = ''; + if ( $row->{'callee_id'} =~ /^sip:(\+1?)?(\w+)@(.*)/ ) { $dst = $2; + my $rest = $3; + if ($rest =~ /^([\d\.]{7,15})/) { + $dst_ip = sprintf('%03d.%03d.%03d.%03d', split('\.', $1)); + } } else { warn "unparseable callee_id ". $row->{'callee_id'}. "\n"; } @@ -108,6 +119,8 @@ while ( $row = $sth->fetchrow_hashref ) { $cdr->startdate($date); $cdr->src($src); $cdr->dst($dst); + $cdr->src_ip_addr($src_ip); + $cdr->dst_ip_addr($dst_ip); } elsif ( $row->{'method'} eq 'ACK' ) { $cdr->answerdate($date); diff --git a/httemplate/search/cdr.html b/httemplate/search/cdr.html index 5e917db2e..d0d7292d1 100644 --- a/httemplate/search/cdr.html +++ b/httemplate/search/cdr.html @@ -223,6 +223,17 @@ if ( $cgi->param('svcnum') =~ /^([\d, ]+)$/ ) { } ### +# src/dst_ip_addr +### +foreach my $field ('src_ip_addr','dst_ip_addr') { + if ( $cgi->param($field) ) { + my $search = FS::cdr->ip_addr_sql($field, $cgi->param($field)); + push @search, $search; + push @qsearch, $search; + } +} + +### # cdrbatchnum (or legacy cdrbatch) ### diff --git a/httemplate/view/svc_pbx.cgi b/httemplate/view/svc_pbx.cgi index 79cafed4d..a1afeb22c 100644 --- a/httemplate/view/svc_pbx.cgi +++ b/httemplate/view/svc_pbx.cgi @@ -1,6 +1,6 @@ <% include('elements/svc_Common.html', 'table' => 'svc_pbx', - 'edit_url' => $p."edit/svc_Common.html?svcdb=svc_pbx;svcnum=", + 'edit_url' => $p."edit/svc_Common.html?svcdb=svc_pbx;svcnum=", 'labels' => \%labels, 'html_foot' => $html_foot, ) @@ -43,12 +43,14 @@ my $html_foot = sub { my $cdr_svc_method = $voip_pkg->option('cdr_svc_method') || 'svc_phone.phonenum'; - return '' unless $cdr_svc_method =~ /^svc_pbx\.(\w+)$/; + return '' unless $cdr_svc_method =~ /^svc_pbx\.(.*)$/; my $field = $1; my $search; if ( $field eq 'title' ) { $search = 'charged_party='. uri_escape($svc_pbx->title); + } elsif ( $field =~ /^ip\.(\w+)$/ ) { + $search = "$1_ip_addr=". uri_escape($svc_pbx->title); } elsif ( $field eq 'svcnum' ) { $search = 'svcnum='. $svc_pbx->svcnum; } else { -- 2.11.0