New entry for Interim packet
Tony Spencer
tony at tonyspencer.co.uk
Thu Nov 20 23:25:45 CET 2008
Hi Leigh
Your script is most useful.
We have interim updates every 2 hours and hadn't implemented anything to
close stale sessions.
So now I've modified your script and will run it every night to look for
sessions where the AcctStatus = Alive and where the updated column, that we
have added to see when the last interim update was, isn't today's date.
I've taken onboard your suggestion of setting Alive sessions to Stop and
then working out the Octet difference from the new session and the
Admin-Rest session and I've tested it on a test account we have. However
after setting the session to Stop with an Admin-Reset AcctStatus the new
update indeed did put a new entry in radacct, but there is no way to tie the
forced Stop session with the new session. The AcctSessionId and AcctUniqueId
are different across the 2 sessions.
Any ideas?
Tony
_____
From: freeradius-users-bounces+tony=tonyspencer.co.uk at lists.freeradius.org
[mailto:freeradius-users-bounces+tony=tonyspencer.co.uk at lists.freeradius.org
] On Behalf Of Leigh Martell
Sent: 20 November 2008 20:41
To: FreeRadius users mailing list
Subject: Re: New entry for Interim packet
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 at 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/0413f9d5/attachment.html>
More information about the Freeradius-Users
mailing list