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