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
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.
You can't do that on the radius server. Accounting is done by the NAS and it decides when session starts and ends - radius just receives that information. You can do that but it will get undone when next accounting packet with same accounting id arrives. If you are such a nasty provider that won't allow users to roll over unused allowance into the next period even during the existing session - simply disconnect them at the time the counter resets (use Login-Time on users connecting on the last day for instance). Be nasty to the end! If you are prepared to be sensible you should try maths rather then discontinuing sessions. Estimate the usage (if it doesn't *have* to be exact) for the time the session run into the new period and deduct that from the counter limit for each such user. Or be kind to your users and - ignore it. Ivan Kalik Kalik Informatika ISP
-----Original Message----- From: freeradius-users-bounces+tony=tonyspencer.co.uk@lists.freeradius.org [mailto:freeradius-users- bounces+tony=tonyspencer.co.uk@lists.freeradius.org] On Behalf Of tnt@kalik.net Sent: 20 November 2008 18:30 To: FreeRadius users mailing list Subject: Re: New entry for Interim packet
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.
You can't do that on the radius server. Accounting is done by the NAS and it decides when session starts and ends - radius just receives that information. You can do that but it will get undone when next accounting packet with same accounting id arrives.
If you are such a nasty provider that won't allow users to roll over unused allowance into the next period even during the existing session - simply disconnect them at the time the counter resets (use Login-Time on users connecting on the last day for instance). Be nasty to the end!
Its not about being a nasty provider. Its about ensuring users pay for what they use and between them pushing out bandwidth costs higher than what users pay us each month.
If you are prepared to be sensible you should try maths rather then discontinuing sessions. Estimate the usage (if it doesn't *have* to be exact) for the time the session run into the new period and deduct that from the counter limit for each such user.
I don't think disconnecting about 4,000 users is an ideal solution.
Or be kind to your users and - ignore it.
Ivan Kalik Kalik Informatika ISP
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
If you are such a nasty provider that won't allow users to roll over unused allowance into the next period even during the existing session - simply disconnect them at the time the counter resets (use Login-Time on users connecting on the last day for instance). Be nasty to the end!
Its not about being a nasty provider. Its about ensuring users pay for what they use and between them pushing out bandwidth costs higher than what users pay us each month.
It is being nasty, my friend. Lets say you have sold them 5GB for the month. They used 4GB. And you won't let them use the portion of 1GB that was left over even if they are on line when counter resets. They could easily see that as a rip off.
If you are prepared to be sensible you should try maths rather then discontinuing sessions. Estimate the usage (if it doesn't *have* to be exact) for the time the session run into the new period and deduct that from the counter limit for each such user.
I don't think disconnecting about 4,000 users is an ideal solution.
Well, tough. You can't break sessions without disconnecting users. Maths it is then. You can run a perl script on Stop packets that will adjust counter check attribute value if the counter was reset during the session. This should work with default counter value in radgroupcheck and individual in radcheck (group attribute takes presedance but this can be fixed with a little bit of unlang gymnastics). You will need to wipe those in radcheck when counter resets next month. Ivan Kalik Kalik Informatika ISP
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
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@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
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@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@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
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@lists.freeradius.org [mailto:freeradius-users-bounces+tony=tonyspencer.co.uk@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@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@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
Then create script for virtual disconnection :) Tony Spencer 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@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
Tony Spencer wrote:
I'd rather not disconnect 4,000 users in one go. In fact, this thing should be done with BRAS functionality. For example, some BRAS'es has support of restarting of accounting at least, also there is a "prepaid" feature, that could be helpful for you.
Also you can use CoA functions of your BRAS. -- With best regards, Evgeniy Kozhuhovskiy Leader of Services group, MGTS, RUE Beltelecom
participants (5)
-
Evgeniy Kozhuhovskiy -
Leigh Martell -
Marinko Tarlac -
tnt＠kalik.net -
Tony Spencer