New entry for Interim packet
Leigh Martell
leigh.martell at gmail.com
Thu Nov 20 21:40:58 CET 2008
Hello Tony,
If you set you interim update to 5 or 10min you could then run a script at
midnight to send accounting packets to finalize it in the MySQL DB with the
Acct-Terminate-Cause = Admin-Reset; so next interim packet radius will
automatically start a new one. Now here is where it can get tricky, radius
will not reset the counter in this case but you could write a script to fire
at accounting stop or monthly to subtract the previous months last
accounting packet with Termination cause Admin-Reset from the first
accouting packet of the current month...sorry if I am rambling but this will
work without a huge discrepency; please be careful and write your script
conditions properly(ie: don't subtract from an accounting packet that came
on the first past 12:30am).
Here is an example of a nightly close-session script I run to clean up stale
sessions.
#!/usr/bin/perl
use Authen::Radius;
use DBI;
use DateTime;
use DateTime::Format::Strptime;
getopen();
sub connect_db {
local($radius_name,$radius_user,$radius_passwd) = @_;
$connect_name = "connect to db";
$dbh_radius = DBI->connect("DBI:mysql:$radius_name:localhost",
$radius_user, $radius_passwd);
if(!$dbh_radius) {
print "Error $connect_name: Cannot connect to $radius_name -
$dbh_radius->errstr<BR>\n";
exit 2;
}
}
sub disconnect_db {
$dbh_radius->disconnect();
return 1;
}
sub getopen {
$radius_name = "radiusdb";
$radius_user = "username";
$radius_passwd = "password";
my $dtn = DateTime->now();
$date = $dtn->ymd . "%";
&connect_db($radius_name,$radius_user,$radius_passwd);
$qAccount = qq{SELECT * FROM radacct WHERE AcctStopTime =
\'0000-00-00 00:00:00\' AND AcctStartTime NOT LIKE \'$date\'};
$sth = $dbh_radius->prepare($qAccount);
$sth->execute() or warn "ERROR IN EXECUTE";
if (! defined($sth->rows()) || $sth->rows() == 0 ) {
print "NO OPEN SESSIONS\n";
exit 2;
}
$i = 0;
while ($i < $sth->rows) {
$test = $sth->fetchrow_hashref();
$nasport="$test->{NASPortId}";
$username="$test->{UserName}";
$nasporttype="$test->{NASPortType}";
if ($test->{AcctSessionTime} eq "0") {
$acctime="1";
} else {
$acctime="$test->{AcctSessionTime}";
}
$uniqueid="$test->{AcctUniqueId}";
$inoctets="$test->{AcctInputOctets}";
$outoctets="$test->{AcctOutputOctets}";
$nasip="$test->{NASIPAddress}";
$nasid="$test->{NasIdentifier}";
$callid="$test->{CallingStationId}";
$calledid="$test->{CalledStationId}";
$sessid="$test->{AcctSessionId}";
$authtype="$test->{AcctAuthentic}";
$framedip="$test->{FramedIPAddress}";
$realm="$test->{Realm}";
$acctstart="$test->{AcctStartTime}";
my $parser =
DateTime::Format::Strptime->new( pattern => '%Y-%m-%d
%H:%M:%S' );
my $dt = $parser->parse_datetime($acctstart);
my $dtn = DateTime->now();
$delay = ($dtn->epoch() - $dt->epoch()) -
$test->{AcctSessionTime};
print "=================\n";
print "RadAcctId: $test->{RadAcctId}\n";
print "UserName: $test->{UserName}\n";
print "AcctStart: " . $dt->ymd . ' ' . $dt->hms . "\n";
print "AcctStop: " . $dtn->ymd . ' ' . $dtn->hms . "\n";
print "AcctSessionTime: $acctime \n";
print "AcctStopDelay: $delay\n";
radsend();
sleep 1;
$i++;
}
$sth->finish();
&disconnect_db();
}
sub radsend {
my $r = new Authen::Radius(Host => 'localhost:1813', Service =>
radacct, Secret => 'SHARESECRET');
Authen::Radius->load_dictionary('/usr/share/freeradius/dictionary');
$r->clear_attributes;
$r->add_attributes (
{ Name => 'User-Name', Value => $username},
{ Name => 'NAS-Port', Value => $nasport},
{ Name => 'NAS-Port-Type', Value => $nasporttype},
{ Name => 'Acct-Session-Time', Value => $acctime},
{ Name => 'Acct-Unique-Session-Id', Value => $uniqueid},
{ Name => 'Acct-Input-Octets', Value => $inoctets},
{ Name => 'Acct-Output-Octets', Value => $outoctets},
{ Name => 'Acct-Terminate-Cause', Value => "Admin-Reset"},
{ Name => 'Acct-Status-Type', Value => "Stop" },
{ Name => 'NAS-IP-Address', Value => $nasip},
{ Name => 'NAS-Identifier', Value => $nasid },
{ Name => 'Calling-Station-Id', Value => $callid },
{ Name => 'Called-Station-Id', Value => $calledid },
{ Name => 'Acct-Delay-Time', Value => $delay },
{ Name => 'Acct-Session-Id', Value => $sessid},
{ Name => 'Acct-Authentic', Value => $authtype},
{ Name => 'Framed-IP-Address', Value => $framedip},
{ Name => 'Realm', Value => $realm, Type => 'string' }
);
$r->send_packet(ACCOUNTING_REQUEST);
$rcv = $r->recv_packet(ACCOUNTING_RESPONSE);
my $error=$r->strerror();
if ( $error eq "none" ) {
print "New Status: Session Closed\n";
} else {
print "Error: Session not Closed\nOUTPUT: $error\n";
}
}
I am not the best programmer in the world but it works, I hope this helps
spark some creative to solve your issue.
--
Leigh Martell
On Thu, Nov 20, 2008 at 2:15 PM, Tony Spencer <tony at tonyspencer.co.uk>wrote:
> I'd rather not disconnect 4,000 users in one go.
>
> > -----Original Message-----
> > From: freeradius-users-bounces+tony=tonyspencer.co.uk@
> lists.freeradius.org
> > [mailto:freeradius-users-
> > bounces+tony=tonyspencer.co.uk at lists.freeradius.org] On Behalf Of
> Marinko
> > Tarlac
> > Sent: 20 November 2008 19:02
> > To: FreeRadius users mailing list
> > Subject: Re: New entry for Interim packet
> >
> > Create CRON script which starts 5-10 min after midnight (first day in
> > the month) and disconnect all active users. Then you will have
> > AcctStopTime information in your database and you can sum traffic from
> > previous month...
> >
> > Tony Spencer wrote:
> > >
> > > Hello
> > >
> > > Our setup is as follows:
> > >
> > > Centos 5.2
> > >
> > > FreeRADIUS Version 2.0.2
> > >
> > > MySQL Version: 4.1.20
> > >
> > > We are using FreeRadius for our ADSL users and its working fine.
> > >
> > > Except when it comes to working out the usage stats for each user at
> > > the end of each month.
> > >
> > > Its easy to do with all sessions that started in the previous month
> > > and have a Stop status.
> > >
> > > But it's difficult when a session rolled over to the next month
> > > because the status is Alive.
> > >
> > > We're trying to find a why to make FreeRadius:
> > >
> > > Enter a new entry into the Radacct table for a session for an Interim
> > > update
> > >
> > > Mark the previous session with a stop Status and update the OctetsIn
> > > and OctetsOut for that session with the current value.
> > >
> > > Set the new session OctetsIn and OctetsOut at zero until the next
> > > update and then it starts from the beginning again.
> > >
> > > However we can't find a way of making FreeRadius:
> > >
> > > Run 2 sql statements in the same update.
> > >
> > > Set the new session counter to zero and not roll over the next updates
> > > Octets.
> > >
> > > We have found the following site:
> > > http://www.netexpertise.eu/en/freeradius/daily-accounting.html with a
> > > way of doing this within MySQL with procedures, but apparently this
> > > only works with MySQL 5.
> > >
> > > Having installed MySQL 5 on a test server and importing our Radius
> > > database we tried running the first procedure but get an error:
> > >
> > > ERROR 1064 (42000): You have an error in your SQL syntax; check the
> > > manual that corresponds to your MySQL server version for the right
> > > syntax to use near 'DECLARE COUNTER_LIMIT BIGINT(12)' at line 1
> > >
> > > mysql> SET COUNTER_LIMIT = POW(2,32);
> > >
> > > ERROR 1193 (HY000): Unknown system variable 'COUNTER_LIMIT
> > >
> > > Has anyone any ideas on how to do what we require or has anyone had
> > > any luck with the instructions on the URL?
> > >
> > > Thanks in advance.
> > >
> > > Tony
> > >
> > >
> ------------------------------------------------------------------------
> > >
> > > -
> > > List info/subscribe/unsubscribe? See
> > http://www.freeradius.org/list/users.html
> >
> > -
> > List info/subscribe/unsubscribe? See
> > http://www.freeradius.org/list/users.html
>
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20081120/d8bce8ad/attachment.html>
More information about the Freeradius-Users
mailing list