SQL radwho radsqlwho

[Chris]

Here's what I'm using.  It very likely will not work out-of-the-box on  
a stock freeradius, and it's PostgreSQL-specific.

#!/usr/local/bin/perl
#
use Time::Local;
use DBI;
use Getopt::Std;
$SQL_HOST="pgsql.example.com";

getopts('ah');
if ($opt_h) {
print STDERR <<EOF;
usage: radsqlwho [-a] [-h] [logname]
   -a       anonymous output
   -h       print this help message
   logname  print only records for this logname
EOF
exit;
}

$logname = $ARGV[0];

my $datasource = "dbi:Pg:dbname=radius;host=$SQL_HOST";
my $dbh = DBI->connect($datasource) ||
        die "Could not connect:$DBI::errstr";

my $sth = $dbh->prepare(qq[SET DATESTYLE TO 'Postgres';]);
unless ($sth->execute) {
        print "SET DATESTYLE failed: " . $DBI::errstr . "\n";
}
$sth->finish;

my $sth = $dbh->prepare(qq[SET TIME ZONE 'Africa/Mogadishu';]);
unless ($sth->execute) {
        print "SET TIME ZONE failed: " . $DBI::errstr . "\n";
}
$sth->finish;

unless (length($logname)) {
	$query="SELECT  
username 
,nasipaddress 
,modulationtype,framedipaddress,nasportid,split_part(connectspeed,  
'-', 1) AS connectspeed, to_char(now() - acctstarttime, 'HH24:MI:SS')  
AS sessiontime, to_char(acctstarttime, 'HH24:MI Mon DD') AS starttime  
FROM radacct WHERE acctstoptime IS NULL ORDER BY acctstarttime";
} else {
        $logname=$dbh->quote($logname);
	$query="SELECT  
username 
,nasipaddress 
,modulationtype,framedipaddress,nasportid,split_part(connectspeed,  
'-', 1) AS connectspeed, to_char(now() - acctstarttime, 'HH24:MI:SS')  
AS sessiontime, to_char(acctstarttime, 'HH24:MI Mon DD') AS starttime  
FROM radacct WHERE username=$logname AND acctstoptime IS NULL ORDER BY  
acctstarttime";
}

&detail_format;

my $sth = $dbh->prepare(qq[$query]);
unless ($sth->execute) {
        print "SELECT failed: " . $DBI::errstr . "\n";
}

if ($sth->rows) {
        my $hash_ref;
        while ($hash_ref = $sth->fetchrow_hashref) {
                $username=$hash_ref->{username};
                $framedip=$hash_ref->{framedipaddress};
                $modtype=$hash_ref->{modulationtype};
                $speed=$hash_ref->{connectspeed};
                $sesstime=$hash_ref->{sessiontime};
                $starttime=$hash_ref->{starttime};

                if ($opt_a) {
                        $username='********';
                }
                write;
        }
}
$sth->finish;
$dbh->disconnect;

sub detail_format
{
$%=0;
$-=0;
$==59;
$^ = "DETAIL_TOP";
$~ = "DETAIL";
}

format DETAIL_TOP =
Login ID        IP Address       Modulation  Speed  Time       When
----------------------------------------------------------------------------
.

format DETAIL =
@<<<<<<<<<<<<<  @<<<<<<<<<<<<<<  @<<<<<<<<<  @<<<<  @<<<<<<<<   
@<<<<<<<<<<<<
$username,     $framedip,       $modtype, $speed, $sesstime, $starttime
.

--
Have you "man unlang"ed today?




-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html