(RADIATOR) Re: A simple usage table rotation script

[Hugh Irvine]

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,
>
> 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.

-- 
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 radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.