(RADIATOR) Re: Fwd: A simple usage table rotation script
Mike McCauley
mikem at open.com.au
Wed Nov 6 18:17:59 CST 2002
Hello Gordon,
Thanks very much for your contribution.
We have added it to the RAdmin goodies for the next release.
Thanks again.
Cheers.
On Wed, 6 Nov 2002 18:53, Hugh Irvine wrote:
> Mikey -
>
> Contributions....
>
> cheers
>
> Hugh
>
> Begin forwarded message:
> > From: "Gordon Smith" <gordon at morenet.net.nz>
> > Date: Wed Nov 6, 2002 8:15:22 AM Australia/Melbourne
> > To: <radiator at open.com.au>
> > Cc: "Hugh Irvine" <hugh at open.com.au>
> > Subject: A simple usage table rotation script
> >
> > Hi,
> >
> > 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
> > #
> > # gordon at morenet.net.nz
> > #
> > # 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;
> > }
>
> NB: I am travelling this week, so there may be delays in our
> correspondence.
--
Mike McCauley mikem at open.com.au
Open System Consultants Pty. Ltd Unix, Perl, Motif, C++, WWW
24 Bateman St Hampton, VIC 3188 Australia http://www.open.com.au
Phone +61 3 9598-0985 Fax +61 3 9598-0955
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
Platypus, Freeside, TACACS+, PAM, external, Active Directory, EAP, TLS,
TTLS etc on Unix, Windows, MacOS etc.
===
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.
More information about the radiator
mailing list