diff options
| -rw-r--r-- | FS/FS/cust_svc.pm | 44 | ||||
| -rw-r--r-- | FS/FS/part_export/sqlradius.pm | 107 | ||||
| -rw-r--r-- | httemplate/elements/header.html | 4 | ||||
| -rw-r--r-- | httemplate/index.html | 8 | ||||
| -rw-r--r-- | httemplate/search/sqlradius.cgi | 249 | ||||
| -rw-r--r-- | httemplate/search/sqlradius.html | 70 | ||||
| -rwxr-xr-x | httemplate/view/svc_acct.cgi | 8 |
7 files changed, 450 insertions, 40 deletions
diff --git a/FS/FS/cust_svc.pm b/FS/FS/cust_svc.pm index 118ab79d2..0c17c9706 100644 --- a/FS/FS/cust_svc.pm +++ b/FS/FS/cust_svc.pm @@ -547,48 +547,22 @@ Meaningless for records where B<svcdb> is not "svc_acct". sub get_session_history { my($self, $start, $end, $attrib) = @_; - my $username = $self->svc_x->username; + #$attrib ??? - my @part_export = $self->part_svc->part_export('sqlradius') - or die "no sqlradius export configured for this service type"; + my @part_export = $self->part_svc->part_export('sqlradius'); + push @part_export, $self->part_svc->part_export('sqlradius_withdomain'); + die "no sqlradius or sqlradius_withdomain export configured for this". + "service type" + unless @part_export; #or return undef; my @sessions = (); foreach my $part_export ( @part_export ) { - - my $dbh = DBI->connect( map { $part_export->option($_) } - qw(datasrc username password) ) - or die "can't connect to sqlradius database: ". $DBI::errstr; - - #select a unix time conversion function based on database type - my $str2time; - if ( $dbh->{Driver}->{Name} =~ /^mysql(PP)?$/ ) { - $str2time = 'UNIX_TIMESTAMP('; - } elsif ( $dbh->{Driver}->{Name} eq 'Pg' ) { - $str2time = 'EXTRACT( EPOCH FROM '; - } else { - warn "warning: unknown database type ". $dbh->{Driver}->{Name}. - "; guessing how to convert to UNIX timestamps"; - $str2time = 'extract(epoch from '; - } - - my @fields = qw( acctstarttime acctstoptime acctsessiontime - acctinputoctets acctoutputoctets framedipaddress ); - - my $sth = $dbh->prepare('SELECT '. join(', ', @fields). - " FROM radacct - WHERE UserName = ? - AND $str2time AcctStopTime ) >= ? - AND $str2time AcctStopTime ) <= ? - ORDER BY AcctStartTime DESC - ") or die $dbh->errstr; - $sth->execute($username, $start, $end) or die $sth->errstr; - - push @sessions, map { { %$_ } } @{ $sth->fetchall_arrayref({}) }; - + push @sessions, $part_export->usage_sessions( $self->svc_x, $start, $end ); } - \@sessions + + \@sessions; } diff --git a/FS/FS/part_export/sqlradius.pm b/FS/FS/part_export/sqlradius.pm index fd5bb89fd..85e5969fc 100644 --- a/FS/FS/part_export/sqlradius.pm +++ b/FS/FS/part_export/sqlradius.pm @@ -333,5 +333,112 @@ sub sqlradius_connect { DBI->connect(@_) or die $DBI::errstr; } +#-- + +=item usage_sessions TIMESTAMP_START TIMESTAMP_END [ SVC_ACCT [ IP [ SQL_SELECT ] ] ] + +TIMESTAMP_START and TIMESTAMP_END are specified as UNIX timestamps; see +L<perlfunc/"time">. Also see L<Time::Local> and L<Date::Parse> for conversion +functions. + +SVC_ACCT, if specified, limits the results to the specified account. + +IP, if specified, limits the results to the specified IP address. + +#SQL_SELECT defaults to * if unspecified. It can be useful to set it to +#SUM(acctsessiontime) or SUM(AcctInputOctets), etc. + +Returns an array of hash references +Returns an arrayref of hashrefs with the following fields: + +=over 4 + +=item username + +=item framedipaddress + +=item acctstarttime + +=item acctstoptime + +=item acctsessiontime + +=item acctinputoctets + +=item acctoutputoctets + +=back + +=cut + +#some false laziness w/cust_svc::seconds_since_sqlradacct + +sub usage_sessions { + my( $self, $start, $end ) = splice(@_, 0, 3); + my $svc_acct = @_ ? shift : ''; + my $ip = @_ ? shift : ''; + #my $select = @_ ? shift : '*'; + + $end ||= 2147483647; + + return () if $self->option('ignore_accounting'); + + my $dbh = sqlradius_connect( map $self->option($_), + qw( datasrc username password ) ); + + #select a unix time conversion function based on database type + my $str2time; + if ( $dbh->{Driver}->{Name} =~ /^mysql(PP)?$/ ) { + $str2time = 'UNIX_TIMESTAMP('; + } elsif ( $dbh->{Driver}->{Name} eq 'Pg' ) { + $str2time = 'EXTRACT( EPOCH FROM '; + } else { + warn "warning: unknown database type ". $dbh->{Driver}->{Name}. + "; guessing how to convert to UNIX timestamps"; + $str2time = 'extract(epoch from '; + } + + my @fields = ( + qw( username realm framedipaddress + acctsessiontime acctinputoctets acctoutputoctets + ), + "$str2time acctstarttime ) as acctstarttime", + "$str2time acctstoptime ) as acctstoptime", + ); + + my @param = (); + my $where = ''; + + if ( $svc_acct ) { + my $username = $self->export_username($svc_acct); + if ( $svc_acct =~ /^([^@]+)\@([^@]+)$/ ) { + $where = '( UserName = ? OR ( UserName = ? AND Realm = ? ) ) AND'; + push @param, $username, $1, $2; + } else { + $where = 'UserName = ? AND'; + push @param, $username; + } + } + + if ( length($ip) ) { + $where .= ' FramedIPAddress = ? AND'; + push @param, $ip; + } + + push @param, $start, $end; + + my $sth = $dbh->prepare('SELECT '. join(', ', @fields). + " FROM radacct + WHERE $where + $str2time AcctStopTime ) >= ? + AND $str2time AcctStopTime ) <= ? + ORDER BY AcctStartTime DESC + ") or die $dbh->errstr; + $sth->execute(@param) or die $sth->errstr; + + [ map { { %$_ } } @{ $sth->fetchall_arrayref({}) } ]; + +} + 1; diff --git a/httemplate/elements/header.html b/httemplate/elements/header.html index 1d7bf09ab..10e4e40f1 100644 --- a/httemplate/elements/header.html +++ b/httemplate/elements/header.html @@ -1,6 +1,7 @@ <% - my($title, $menubar) = @_; + my($title, $menubar) = ( shift, shift ); my $etc = @_ ? shift : ''; #$etc is for things like onLoad= etc. + my $head = @_ ? shift : ''; #$head is for things that go in the <HEAD> section %> <HTML> <HEAD> @@ -10,6 +11,7 @@ <META HTTP-Equiv="Cache-Control" Content="no-cache"> <META HTTP-Equiv="Pragma" Content="no-cache"> <META HTTP-Equiv="Expires" Content="0"> + <%= $head %> </HEAD> <BODY BGCOLOR="#e8e8e8"<%= $etc %>> <FONT SIZE=6> diff --git a/httemplate/index.html b/httemplate/index.html index 4534d3a41..08d8f9058 100644 --- a/httemplate/index.html +++ b/httemplate/index.html @@ -113,6 +113,7 @@ <TR><TH BGCOLOR="#cccccc">Reports</TH></TR> <TR><TD> <BR> + <A HREF="search/sqlradius.html">RADIUS sessions</A><BR><BR> Auditing pre-Freeside services with no customer record <UL> <LI>unlinked accounts (<A HREF="search/svc_acct.cgi?UN_svcnum">by service number</A>) (<A HREF="search/svc_acct.cgi?UN_username">by username</A>) (<A HREF="search/svc_acct.cgi?UN_uid">by uid</A>) @@ -151,8 +152,11 @@ <TR><TH BGCOLOR="#cccccc">Sysadmin</TH></TR> <TR><TD> <BR> - <A HREF="browse/nas.cgi">View active NAS ports</A> - <BR><A HREF="browse/queue.cgi">View pending job queue</A> + <!-- <BR>View active NAS ports: + <A HREF="browse/nas.cgi">session server</A> + <!-- or <A HREF="browse/nas-sqlradius.cgi">RADIUS</A> + <BR> --> + <A HREF="browse/queue.cgi">View pending job queue</A> <BR><A HREF="misc/cust_main-import.cgi">Batch import customers from CSV file</A> <BR><A HREF="misc/cust_main-import_charges.cgi">Batch import charges from CSV file</A> <BR><A HREF="misc/dump.cgi">Download database dump</A> diff --git a/httemplate/search/sqlradius.cgi b/httemplate/search/sqlradius.cgi new file mode 100644 index 000000000..3c5046bea --- /dev/null +++ b/httemplate/search/sqlradius.cgi @@ -0,0 +1,249 @@ +<%= include( '/elements/header.html', 'RADIUS Sessions', + include('/elements/menubar.html', + 'Main menu' => $p, # popurl(2), + ), + + ) +%> + +<% + ### + # parse cgi params + ### + + #sort of false laziness w/cust_pay.cgi + my $beginning = ''; + my $ending = ''; + if ( $cgi->param('beginning') + && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { + $beginning = str2time($1); + } + if ( $cgi->param('ending') + && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { + $ending = str2time($1) + 86399; + } + if ( $cgi->param('begin') && $cgi->param('begin') =~ /^(\d+)$/ ) { + $beginning = $1; + } + if ( $cgi->param('end') && $cgi->param('end') =~ /^(\d+)$/ ) { + $ending = $1; + } + + my $cgi_svc_acct = ''; + if ( $cgi->param('svcnum') =~ /^(\d+)$/ ) { + $cgi_svc_acct = qsearchs( 'svc_acct', { 'svcnum' => $1 } ); + } elsif ( $cgi->param('username') =~ /^([^@]+)\@([^@]+)$/ ) { + my %search = { 'username' => $1 }; + my $svc_domain = qsearchs('svc_domain', { 'domain' => $2 } ); + if ( $svc_domain ) { + $search{'domsvc'} = $svc_domain->svcnum; + } else { + delete $search{'username'}; + } + $cgi_svc_acct = qsearchs( 'svc_acct', \%search ) + if keys %search; + } elsif ( $cgi->param('username') =~ /^(.+)$/ ) { + $cgi_svc_acct = qsearchs( 'svc_acct', { 'username' => $1 } ); + } + + my $ip = ''; + if ( $cgi->param('ip') =~ /^((\d+\.){3}\d+)$/ ) { + $ip = $1; + } + + ### + # field formatting subroutines + ### + + my %user2svc_acct = (); + my $user_format = sub { + my ( $user, $session, $part_export ) = @_; + + my $svc_acct = ''; + if ( exists $user2svc_acct{$user} ) { + $svc_acct = $user2svc_acct{$user}; + } else { + my %search = (); + if ( $part_export->exporrtype eq 'sqlradius_withdomain' ) { + my $domain; + if ( $user =~ /^([^@]+)\@([^@]+)$/ ) { + $search{'username'} = $1; + $domain = $2; + } else { + $search{'username'} = $user; + $domain = $session->{'realm'}; + } + my $svc_domain = qsearchs('svc_domain', { 'domain' => $domain } ); + if ( $svc_domain ) { + $search{'domsvc'} = $svc_domain->svcnum; + } else { + delete $search{'username'}; + } + } elsif ( $part_export->exporttype eq 'sqlradius' ) { + $search{'username'} = $user; + } else { + die "guru meditation #420"; + } + if ( keys %search ) { + my @svc_acct = + grep { qsearchs( 'export_svc', { + 'exportnum' => $part_export->exportnum, + 'svcpart' => $_->cust_svc->svcpart, + } ) + } qsearch( 'svc_acct', \%search ); + if ( @svc_acct ) { + warn 'multiple svc_acct records for user $user found; '. + 'using first arbitrarily' + if scalar(@svc_acct) > 1; + $user2svc_acct{$user} = $svc_acct = shift @svc_acct; + } + } + } + + if ( $svc_acct ) { + my $svcnum = $svc_acct->svcnum; + qq(<A HREF="${p}view/svc_acct.cgi?$svcnum"><B>$user</B></A>); + } else { + "<B>$user</B>"; + } + + }; + + my $customer_format = sub { + my( $unused, $session ) = @_; + return ' ' unless exists $user2svc_acct{$session->{'username'}}; + my $svc_acct = $user2svc_acct{$session->{'username'}}; + my $cust_pkg = $svc_acct->cust_svc->cust_pkg; + return ' ' unless $cust_pkg; + my $cust_main = $cust_pkg->cust_main; + + qq!<A HREF="${p}view/cust_main.cgi?!. $cust_main->custnum. '">'. + $cust_pkg->cust_main->name. '</A>'; + }; + + my $time_format = sub { + my $time = shift; + $time > 0 + ? time2str('%T%P %a %b %o %Y', $time ) + : ' '; + }; + + my $duration_format = sub { + my $seconds = shift; + my $hour = int($seconds/3600); + my $min = int( ($seconds%3600) / 60 ); + my $sec = $seconds%60; + '<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0>'. + '<TR><TD ALIGN="right">'. + ( $hour ? "<B>$hour</B>h" : ' ' ). + '</TD><TD ALIGN="right">'. + ( ( $hour || $min ) ? "<B>$min</B>m" : ' ' ). + '</TD><TD ALIGN="right">'. + "<B>$sec</B>s". + '</TD></TR></TABLE>'; + }; + + my $octets_format = sub { + my $octets = shift; + my $megs = $octets / 1048576; + sprintf('<B>%.3f</B> megs', $megs); + #my $gigs = $octets / 1073741824 + #sprintf('<B>%.3f</B> gigabytes', $gigs); + }; + + ### + # the fields + ### + + tie my %fields, 'Tie::IxHash', + 'username' => { + name => 'User', + attrib => 'UserName', + fmt => $user_format, + }, + 'realm' => { + name => 'Realm', + attrib => 'Realm', + }, + 'dummy' => { + name => 'Customer', + attrib => '', + fmt => $customer_format, + }, + 'framedipaddress' => { + name => 'IP Address', + attrib => 'Framed-IP-Address', + fmt => sub { my $ip = shift; + length($ip) ? $ip : ' '; + }, + }, + 'acctstarttime' => { + name => 'Start time', + attrib => 'Acct-Start-Time', + fmt => $time_format, + }, + 'acctstoptime' => { + name => 'End time', + attrib => 'Acct-Stop-Time', + fmt => $time_format, + }, + 'acctsessiontime' => { + name => 'Duration', + attrib => 'Acct-Session-Time', + fmt => $duration_format, + }, + 'acctinputoctets' => { + name => 'Upload', # (from user)', + attrib => 'Acct-Input-Octets', + fmt => $octets_format, + }, + 'acctoutputoctets' => { + name => 'Download', # (to user)', + attrib => 'Acct-Output-Octets', + fmt => $octets_format, + }, + ; + $fields{$_}->{fmt} ||= sub { length($_[0]) ? shift : ' '; } + foreach keys %fields; + + ### + # and finally, display the thing + ### + + foreach my $part_export ( map $_->rebless, + qsearch( 'part_export', { 'exporttype' => 'sqlradius' } ), + qsearch( 'part_export', { 'exporttype' => 'sqlradius_withdomain' } ) + ) { + %user2svc_acct = (); +%> + +<%= $part_export->exporttype %> to <%= $part_export->machine %><BR> +<%= include( '/elements/table.html' ) %> +<TR> + <% foreach my $field ( keys %fields ) { %> + <TH> + <%= $fields{$field}->{name} %><BR> + <FONT SIZE=-1><%= $fields{$field}->{attrib} %></FONT> + </TH> + <% } %> +</TR> +<% foreach my $session ( + @{ $part_export->usage_sessions( $beginning, $ending, $cgi_svc_acct, $ip ) } +) { %> + <TR> + <% foreach my $field ( keys %fields ) { %> + <TD ALIGN="right"> + <%= &{ $fields{$field}->{fmt} }( $session->{$field}, + $session, + $part_export, + ) + %> + </TD> + <% } %> + </TR> +<% } %> + +</TABLE> +<BR><BR> + +<% } %> diff --git a/httemplate/search/sqlradius.html b/httemplate/search/sqlradius.html new file mode 100644 index 000000000..48a3d8680 --- /dev/null +++ b/httemplate/search/sqlradius.html @@ -0,0 +1,70 @@ +<%= include( '/elements/header.html', 'Search RADIUS sessions', '', '', ' +<LINK REL="stylesheet" TYPE="text/css" HREF="../elements/calendar-win2k-2.css" TITLE="win2k-2"> +<SCRIPT TYPE="text/javascript" SRC="../elements/calendar_stripped.js"></SCRIPT> +<SCRIPT TYPE="text/javascript" SRC="../elements/calendar-en.js"></SCRIPT> +<SCRIPT TYPE="text/javascript" SRC="../elements/calendar-setup.js"></SCRIPT> +') %> +<FORM NAME="OneTrueForm" ACTION="sqlradius.cgi" METHOD="POST"> +<% #include( '/elements/table.html' ) %> +<%= ntable('#cccccc') %> +<TR> + <TD ALIGN="right">Username: </TD> + <TD><INPUT TYPE="text" NAME="username"></TD> +</TR> +<TR> + <TD></TD> + <TD><FONT SIZE="-1"><I>(leave blank to show all users)</I></FONT></TD> +</TR> +<TR> + <TD ALIGN="right">IP address: </TD> + <TD><INPUT TYPE="text" NAME="ip"></TD> +</TR> +<TR> + <TD></TD> + <TD><FONT SIZE="-1"><I>(leave blank to show all IPs)</I></FONT></TD> +</TR> +<TR> + <TD ALIGN="right">From: </TD> + <TD> + <INPUT TYPE="text" NAME="beginning" ID="beginning_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="beginning_button" STYLE="cursor: pointer" TITLE="Select date"> + </TD> + <SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "beginning_text", + ifFormat: "%m/%d/%Y", + button: "beginning_button", + align: "BR" + }); + </SCRIPT> +</TR> +<TR> + <TD></TD> + <TD><i>m/d/y</i></TD> +</TR> +<TR> + <TD ALIGN="right">To: </TD> + <TD> + <INPUT TYPE="text" NAME="ending" ID="ending_text" VALUE="" SIZE=11 MAXLENGTH=10> <IMG SRC="../images/calendar.png" ID="ending_button" STYLE="cursor:pointer" TITLE="Select date"> + </TD> + <SCRIPT TYPE="text/javascript"> + Calendar.setup({ + inputField: "ending_text", + ifFormat: "%m/%d/%Y", + button: "ending_button", + align: "BR" + }); + </SCRIPT> +</TR> +<TR> + <TD></TD> + <TD><i>m/d/y</i> + <BR><FONT SIZE="-1">(leave one or both dates blank for an open-ended search)</FONT> + </TD> +</TR> +</TABLE> +<BR><INPUT TYPE="submit" VALUE="View sessions"> +</FORM> +</BODY> +</HTML> + + diff --git a/httemplate/view/svc_acct.cgi b/httemplate/view/svc_acct.cgi index be58e4e1d..1322a69fd 100755 --- a/httemplate/view/svc_acct.cgi +++ b/httemplate/view/svc_acct.cgi @@ -93,8 +93,9 @@ if ( $part_svc->part_export('sqlradius') } if ( $cust_pkg ) { - print ' since last bill ('. time2str("%C", $last_bill). ') - '. - $plandata{recur_included_hours}. ' total hours in plan<BR>'; + print ' since last bill ('. time2str("%C", $last_bill). ')'. + print ' - '. $plandata{recur_included_hours}. ' total hours in plan<BR>' + if length($plandata{recur_included_hours}); } else { print ' (no billing cycle available for unaudited account)<BR>'; } @@ -102,6 +103,9 @@ if ( $part_svc->part_export('sqlradius') print 'Input: <B>'. sprintf("%.3f", $input). '</B> megabytes<BR>'; print 'Output: <B>'. sprintf("%.3f", $output). '</B> megabytes<BR>'; + my $href = qq!<A HREF="${p}search/sqlradius.cgi?svcnum=$svcnum!; + print qq!View sessions: this billing cycle | $href">all sessions</A>!; + print '<BR>'; } |