SQL radwho radsqlwho

22 Jun 2008


      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?

SQL radwho radsqlwho

Chris