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 <[EMAIL PROTECTED]>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- > > [EMAIL PROTECTED] 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 >
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html