(RADIATOR) RADONLINE cleanup script

[Ingvar Bjarnason]

Hi all,

    Just wanted to share a little perl script we are using to clean stale
sessions from our session database (nb. this is a temporary fix, the long
term plan is to ask the access servers which has proven to be rather
involved).   This script basically finds entries older than 3 hours (no
alive packet for 3 hours usually means the session is dead), deletes them
and free´s up the corresponding ip from a dynamic IP pool also in sql.
This script is run from cron every hour.   (nb. this version is boiled down,
the actual script also does reporting via email of any deletions etc.)
Hope someone finds this useful.

    Best regards,
                                Ingvar

----
Ingvar Bjarnason
Engineer/Data Division
Iceland Telecom

**************************************
#!/usr/bin/perl
use DBI;
use strict;

my $username = 'username';
my $password = 'password';
my $hostname = 'localhost';

my $dbh = DBI->connect("DBI:mysql:radius:$hostname", $username, $password)
|| die "Cannot connect to DB\n";
my ($sth, $sth1);

my $sql;
my $sql2;

# Remove entries older than 3 hrs
my $currdate = `date +%s` - 10800;

$sql = "select USERNAME from RADONLINE where TIME_STAMP < $currdate";

$sth = $dbh->prepare($sql);
$sth->execute;
my $rowcnts = $sth->rows;

if( $rowcnts ) {
   $sql = "delete from RADONLINE where TIME_STAMP < $currdate";
   $sth1 = $dbh->prepare($sql);
   $sth1->execute;
   $sth1->finish;

   while (my @data = $sth->fetchrow_array) {
     $sql = "update RADPOOL set STATE=0 where USERNAME='$data[0]'";
     $sth1 = $dbh->prepare($sql);
     $sth1->execute;
     $sth1->finish;
   }
}

$sth->finish;
$dbh->disconnect;
********************************

===
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.