X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fpart_export%2Fsqlradius.pm;h=10bccb0347b3b221ec279335057538a716925874;hb=673b9a458d9138523026963df6fa3b4683e09bae;hp=ccf9a7687fbf5b4c3f19d1bb13d23dabcc41e0ce;hpb=5c0de69821c0145e89403e173986b4d8b5b52725;p=freeside.git diff --git a/FS/FS/part_export/sqlradius.pm b/FS/FS/part_export/sqlradius.pm index ccf9a7687..10bccb034 100644 --- a/FS/FS/part_export/sqlradius.pm +++ b/FS/FS/part_export/sqlradius.pm @@ -1,13 +1,88 @@ package FS::part_export::sqlradius; -use vars qw(@ISA); -use FS::Record qw( dbh ); +use vars qw(@ISA $DEBUG %info %options $notes1 $notes2); +use Tie::IxHash; +use FS::Record qw( dbh qsearch ); use FS::part_export; +use FS::svc_acct; +use FS::export_svc; @ISA = qw(FS::part_export); +$DEBUG = 0; + +tie %options, 'Tie::IxHash', + 'datasrc' => { label=>'DBI data source ' }, + 'username' => { label=>'Database username' }, + 'password' => { label=>'Database password' }, + 'ignore_accounting' => { + type => 'checkbox', + label => 'Ignore accounting records from this database' + }, + 'hide_ip' => { + type => 'checkbox', + label => 'Hide IP address information on session reports', + }, + 'hide_data' => { + type => 'checkbox', + label => 'Hide download/upload information on session reports', + }, + 'show_called_station' => { + type => 'checkbox', + label => 'Show the Called-Station-ID on session reports', + }, +; + +$notes1 = <<'END'; +Real-time export of radcheck, radreply and usergroup +tables to any SQL database for +FreeRADIUS +or ICRADIUS. +END + +$notes2 = <<'END'; +An existing RADIUS database will be updated in realtime, but you can use +freeside-sqlradius-reset +to delete the entire RADIUS database and repopulate the tables from the +Freeside database. See the +DBI documentation +and the +documentation for your DBD +for the exact syntax of a DBI data source. + +END + +%info = ( + 'svc' => 'svc_acct', + 'desc' => 'Real-time export to SQL-backed RADIUS (FreeRADIUS, ICRADIUS)', + 'options' => \%options, + 'nodomain' => 'Y', + 'notes' => $notes1. + 'This export does not export RADIUS realms (see also '. + 'sqlradius_withdomain). '. + $notes2 +); + sub rebless { shift; } +sub export_username { + my($self, $svc_acct) = (shift, shift); + warn "export_username called on $self with arg $svc_acct" if $DEBUG; + $svc_acct->username; +} + sub _export_insert { my($self, $svc_acct) = (shift, shift); @@ -16,14 +91,14 @@ sub _export_insert { my %attrib = $svc_acct->$method(); next unless keys %attrib; my $err_or_queue = $self->sqlradius_queue( $svc_acct->svcnum, 'insert', - $table, $svc_acct->username, %attrib ); + $table, $self->export_username($svc_acct), %attrib ); return $err_or_queue unless ref($err_or_queue); } my @groups = $svc_acct->radius_groups; if ( @groups ) { my $err_or_queue = $self->sqlradius_queue( $svc_acct->svcnum, 'usergroup_insert', - $svc_acct->username, @groups ); + $self->export_username($svc_acct), @groups ); return $err_or_queue unless ref($err_or_queue); } ''; @@ -44,9 +119,9 @@ sub _export_replace { my $dbh = dbh; my $jobnum = ''; - if ( $old->username ne $new->username ) { + if ( $self->export_username($old) ne $self->export_username($new) ) { my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'rename', - $new->username, $old->username ); + $self->export_username($new), $self->export_username($old) ); unless ( ref($err_or_queue) ) { $dbh->rollback if $oldAutoCommit; return $err_or_queue; @@ -63,7 +138,7 @@ sub _export_replace { } keys %new ) { my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'insert', - $table, $new->username, %new ); + $table, $self->export_username($new), %new ); unless ( ref($err_or_queue) ) { $dbh->rollback if $oldAutoCommit; return $err_or_queue; @@ -80,7 +155,7 @@ sub _export_replace { my @del = grep { !exists $new{$_} } keys %old; if ( @del ) { my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'attrib_delete', - $table, $new->username, @del ); + $table, $self->export_username($new), @del ); unless ( ref($err_or_queue) ) { $dbh->rollback if $oldAutoCommit; return $err_or_queue; @@ -109,7 +184,7 @@ sub _export_replace { if ( @delgroups ) { my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'usergroup_delete', - $new->username, @delgroups ); + $self->export_username($new), @delgroups ); unless ( ref($err_or_queue) ) { $dbh->rollback if $oldAutoCommit; return $err_or_queue; @@ -125,7 +200,7 @@ sub _export_replace { if ( @newgroups ) { my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'usergroup_insert', - $new->username, @newgroups ); + $self->export_username($new), @newgroups ); unless ( ref($err_or_queue) ) { $dbh->rollback if $oldAutoCommit; return $err_or_queue; @@ -147,7 +222,7 @@ sub _export_replace { sub _export_delete { my( $self, $svc_acct ) = (shift, shift); my $err_or_queue = $self->sqlradius_queue( $svc_acct->svcnum, 'delete', - $svc_acct->username ); + $self->export_username($svc_acct) ); ref($err_or_queue) ? '' : $err_or_queue; } @@ -187,11 +262,15 @@ sub sqlradius_insert { #subroutine, not method } else { my $i_sth = $dbh->prepare( - "INSERT INTO rad$table ( UserName, Attribute, Value ) ". - "VALUES ( ?, ?, ? )" + "INSERT INTO rad$table ( UserName, Attribute, op, Value ) ". + "VALUES ( ?, ?, ?, ? )" ) or die $dbh->errstr; - $i_sth->execute( $username, $attribute, $attributes{$attribute} ) - or die $i_sth->errstr; + $i_sth->execute( + $username, + $attribute, + ( $attribute =~ /Password/i ? '==' : ':=' ), + $attributes{$attribute}, + ) or die $i_sth->errstr; } @@ -271,3 +350,203 @@ sub sqlradius_connect { DBI->connect(@_) or die $DBI::errstr; } +#-- + +=item usage_sessions TIMESTAMP_START TIMESTAMP_END [ SVC_ACCT [ IP [ PREFIX [ SQL_SELECT ] ] ] ] + +TIMESTAMP_START and TIMESTAMP_END are specified as UNIX timestamps; see +L. Also see L and L 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. + +PREFIX, if specified, limits the results to records with a matching +Called-Station-ID. + +#SQL_SELECT defaults to * if unspecified. It can be useful to set it to +#SUM(acctsessiontime) or SUM(AcctInputOctets), etc. + +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 + +=item calledstationid + +=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 $prefix = @_ ? 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 + calledstationid + ), + "$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; + } + + if ( length($prefix) ) { + #assume sip: for now, else things get ugly trying to match /^\w+:$prefix/ + $where .= " CalledStationID LIKE 'sip:$prefix\%' AND"; + } + + 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({}) } ]; + +} + +=item update_svc_acct + +=cut + +sub update_svc_acct { + my $self = shift; + + my $dbh = sqlradius_connect( map $self->option($_), + qw( datasrc username password ) ); + + my @fields = qw( radacctid username realm acctsessiontime ); + + my @param = (); + my $where = ''; + + my $sth = $dbh->prepare(" + SELECT RadAcctId, UserName, Realm, AcctSessionTime + FROM radacct + WHERE FreesideStatus IS NULL + AND AcctStopTime != 0 + ") or die $dbh->errstr; + $sth->execute() or die $sth->errstr; + + while ( my $row = $sth->fetchrow_arrayref ) { + my($RadAcctId, $UserName, $Realm, $AcctSessionTime) = @$row; + warn "processing record: ". + "$RadAcctId ($UserName\@$Realm for ${AcctSessionTime}s" + if $DEBUG; + + my %search = ( 'username' => $UserName ); + my $extra_sql = ''; + if ( ref($self) =~ /withdomain/ ) { #well... + $extra_sql = " AND '$Realm' = ( SELECT domain FROM svc_domain + WHERE svc_domain.svcnum = svc_acct.domsvc ) "; + my $svc_domain = qsearch + } + + my @svc_acct = + grep { qsearch( 'export_svc', { 'exportnum' => $self->exportnum, + 'svcpart' => $_->cust_svc->svcpart, } ) + } + qsearch( 'svc_acct', + { 'username' => $UserName }, + '', + $extra_sql + ); + + my $errinfo = "for RADIUS detail RadAcctID $RadAcctId ". + "(UserName $UserName, Realm $Realm)"; + my $status = 'skipped'; + if ( !@svc_acct ) { + warn "WARNING: no svc_acct record found $errinfo - skipping\n"; + } elsif ( scalar(@svc_acct) > 1 ) { + warn "WARNING: multiple svc_acct records found $errinfo - skipping\n"; + } else { + my $svc_acct = $svc_acct[0]; + warn "found svc_acct ". $svc_acct->svcnum. " $errinfo\n" if $DEBUG; + if ( $svc_acct->seconds !~ /^$/ ) { + warn " svc_acct.seconds found (". $svc_acct->seconds. + ") - decrementing\n" + if $DEBUG; + my $error = $svc_acct->decrement_seconds($AcctSessionTime); + die $error if $error; + $status = 'done'; + } else { + warn " no existing seconds value for svc_acct - skiping\n" if $DEBUG; + } + } + + warn "setting FreesideStatus to $status $errinfo\n" if $DEBUG; + my $psth = $dbh->prepare("UPDATE radacct + SET FreesideStatus = ? + WHERE RadAcctId = ?" + ) or die $dbh->errstr; + $psth->execute($status, $RadAcctId) or die $psth->errstr; + + } + +} + +1; +