Hi Gordon -
Many thanks - I have forwarded your mail to Mike and this will get included in the goodies.
cheers
Hugh
On Wednesday, November 6, 2002, at 08:15 AM, Gordon Smith wrote:
Hi,NB: I am travelling this week, so there may be delays in our correspondence.
Just thought I'd share this simple solution, if anyone is interested.
:-)
We use Radmin, and had been looking for an easy way to archive each
month's usage without impacting radius.
The pretty much ruled out doing a select into, because of the CPU hit
you take doing that.
So I wrote a simple script, called as a cron job on the first day of the
month.
We're using mysql with innodb tables, so the database can fill up if the
data doesn't get archived off somewhere else.
This script handles the rotation of the usage table, then we can archive
data to CD when its convenient.
Oh, on a completely different topic, if anyone is using FreeTDS and
DBD::Sybase to insert info into MS-SQL databases, DON'T upgrade your
DBD::Sybase. It won't work after version 0.94. This is because of some
changes in the DBD::Sybase code that isn't in the freetds libraries.
Cheers,
Gordon Smith CCNA
Network Operations Manager
MoreNet Ltd
#!/usr/bin/perl -w
# Radius usage table rotation script
#
# Gordon Smith 31 Oct, 2002
#
# [EMAIL PROTECTED]
#
# MoreNet Ltd.
use strict;
use DBI;
# Database
my $radius_db = "DBI:mysql:radmin";
my $radius_user = "xxxx";
my $radius_pwd = "yyyy";
# local variables
my ($dbh, $last_month, $tablename, $sth, $sth2, $sth3, $sql);
# Generate the name of the table to create
$tablename = getdate();
# Establish database connection
$dbh = DBI->connect($radius_db, $radius_user, $radius_pwd)
|| die "Cannot connect to database!\n $DBI::errstr\n"
unless (defined $dbh);
# Rename RADUSAGE table
$sth = $dbh->prepare(
qq{ALTER TABLE RADUSAGE RENAME TO $tablename})
|| die "Unable to prepare rename table query:
".$dbh->errstr."\n";
$sth->execute();
$sth->finish();
# Create new RADUSAGE table
$sth2 = $dbh->prepare(
qq{CREATE TABLE RADUSAGE(
ACCTDELAYTIME INT(11) NULL,
ACCTINPUTOCTETS INT(11) NULL,
ACCTOUTPUTOCTETS INT(11) NULL,
ACCTSESSIONID VARCHAR(30) NULL,
ACCTSESSIONTIME INT(11) NULL,
ACCTSTATUSTYPE INT(11) NULL,
ACCTTERMINATECAUSE VARCHAR(50) NULL,
DNIS VARCHAR(30) NULL,
FRAMEDIPADDRESS VARCHAR(30) NULL,
NASIDENTIFIER VARCHAR(50) NOT NULL,
NASPORT INT(11) NULL,
TIME_STAMP INT(11) NULL,
USERNAME VARCHAR(50) NOT NULL,
CALLERID VARCHAR(30) NULL,
CONNECT_SPEED INT(11) NULL,
INDEX RADUSAGE_ui1 (USERNAME)
) TYPE=INNODB;
})
|| die "Unable to prepare new table query: ".$dbh->errstr."\n";
$sth2->execute();
$sth2->finish();
# Restart radius
# This assumes a restart wrapper - we use svscan, so we just kill
# the process. Change this system call to reflect the type of
# wrapper you are using. Not elegant, but it works.
system('/usr/bin/killall -9 radiusd');
# Now clean up the archive & remove everything except STOP records
$sql = "DELETE from $tablename where acctstatustype != '2'";
$sth3 = $dbh->prepare($sql);
$sth3->execute();
$sth3->finish();
# Close the database connection
$dbh->disconnect;
exit();
sub getdate {
# Gets the current date and creates a name to use for creation
# of an archive table for the previous month's data, then
# returns the name created
my @months =
("jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec "
);
# Get current time
# The month returned is an integer between 0 and 11
my ($second, $minute, $hour, $day, $month, $year, $weekday,
$dayofyear, $IsDST) = localtime(time);
# Tidy up the year
if ($year >= 100){
$year = $year - 100;
}
# Add a leading 0 if year is less than 2010
if ($year < 10){
$year = "0".$year;
}
# Get the last month so we can name the archive table correctly
if ($month == 0){ # january
$last_month = 11; # december
$year = $year - 1;
}else{
$last_month = $month - 1;
}
# generate the name of the archive table
$tablename = $months[$last_month] . $year;
# return the calculated value
return $tablename;
}
--
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. Available on *NIX, *BSD, Windows 95/98/2000, NT, MacOS X.
-
Nets: internetwork inventory and management - graphical, extensible,
flexible with hardware, software, platform and database independence.
===
Archive at http://www.open.com.au/archives/radiator/
Announcements on [EMAIL PROTECTED]
To unsubscribe, email '[EMAIL PROTECTED]' with
'unsubscribe radiator' in the body of the message.
