<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7653.38">
<TITLE>RE: Using accounting data for quotas</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Hello,<BR>
I've implemented this situation.<BR>
well, our solution was a bit more complex, we have "peak" and "off peak" times. for example weekends are "off peak".<BR>
Users have a limited traffic based on their group, say 25GB/month.<BR>
here is pretty much what i have done (simplified!):<BR>
1. add a "traffic" usage field in users table.<BR>
2. i have an "hourlytraffic" table, 1 row per online user/per hour.<BR>
3. i have a "dailytraffic" table, 1 row per online user/per day.<BR>
4. instead of plain SQL in sql module, i pass all the data to stored procedures.<BR>
5. when ever i get a new "interim-update" (every 5 minutes) i calculated the difference from sessions table. now i know how much traffic the user used in the last interim. based on the time i received the interim(off peak time or peak time) i know if i should add this traffic to usage of the user.<BR>
6. in the stored procedure, when ever a user has more usage then allowed by plan, i add a row to a "disconnect_job" table. every few minutes i disconnect sessions with COA from nas.<BR>
<BR>
the system is pretty flexible, different plans with different peak/off peak times, usage limits,etc. and it is very very stable!<BR>
just to let you know how this performs.<BR>
i run national DSL subscribers on this(about 50,000 online sessions) from over 50 nases all going through one freeradius/mysql box!<BR>
<BR>
few issues i ran into:<BR>
<BR>
Problem 1: hourlytraffic table will get huge!!! with millions and millions of rows. you wont be able to delete any data from that table since "delete from hourly traffic where...." will lock the table for a while.<BR>
<BR>
Solution 1: Use new mysql 5.1 partitioned tables. you can partition your hourly/sessionlogs/daily tables based on months/weeks or days ... and drop an old partition instead of deleting data.<BR>
<BR>
Problem 2: sometimes(very very rare) i get packets in wrong order. for example i get packet with 2GB traffic and another one with 1.9GB, if you just substract them in your stored procedure, you end up with a negative number or if you are not using signed variables, you end up with a huge number. no idea why i get these packets from time to time:<BR>
<BR>
solution 2: always make sure the packet you receive is in the right order. if it is not, drop it!<BR>
<BR>
<BR>
i hope this helps<BR>
<BR>
cheers,<BR>
parham<BR>
-----Original Message-----<BR>
From: freeradius-users-bounces+p_beheshti=rasana.net@lists.freeradius.org on behalf of ahmed adel<BR>
Sent: Sun 2/15/2009 12:43 PM<BR>
To: FreeRadius users mailing list<BR>
Subject: Re: Using accounting data for quotas<BR>
<BR>
I have implemented quota service based on Freeradius before, and I<BR>
think that it is close to what you are looking for except that in my<BR>
case there was a required action. Anyway it is normal that session<BR>
remain open for long time so if using SQL you can have a trigger on the<BR>
accounting table to update another table with a summary of the total<BR>
bandwidth used for each user and this is available for MySQL and MSSQL.<BR>
<BR>
As for simultaneous you can disable that using simultaneous-use option.<BR>
<BR>
Also it is normal that the updates come in different times but the trigger on the database will solve this issue.<BR>
<BR>
For the traffic counters there are two approaches one is that your<BR>
network equipment supports sending what is called Giga-Word counter<BR>
which is a counter that tells you how many times the counter has rolled<BR>
over, if not you will have to handle this in your SQL statment and this<BR>
is by keeping the last update in a field in the database and on the new<BR>
update compare the new update with the previous one and if the previous<BR>
one is bigger just by simple math compensate for the different.<BR>
<BR>
For the last point also the trigger on the database will solve it as you will have another table to hold the history in.<BR>
<BR>
Best Regards<BR>
Ahmed Adel<BR>
<BR>
<BR>
<BR>
________________________________<BR>
From: Jonathan Gazeley <jonathan.gazeley@bristol.ac.uk><BR>
To: freeradius-users@lists.freeradius.org<BR>
Sent: Friday, February 13, 2009 12:56:14 PM<BR>
Subject: Using accounting data for quotas<BR>
<BR>
I'm trying to find a way to extract useful data from accounting logs to use towards a quota. I'm a bit stuck and I'm wondering if anyone has tried anything similar with success. Let me explain...<BR>
<BR>
My accounting logs are sent to SQL with the inner ID. Periodically, the NAS updates the accounting record with total of upload and download during that session. I've tweaked the FR queries so they also update a new field called 'lastupdatetime'.<BR>
<BR>
I want to write an hourly script that will tell me (eg in a CSV file) how much traffic has been done for each username (not necessarily each session) during the last hour. Clearly this will take some sort of hourly summary that can be compared each hour. But it is still not straightforward:<BR>
<BR>
- Some sessions remain open for weeks.<BR>
- Some users have multiple simultaneous or multiple sequential sessions.<BR>
- The updates come in at different times.<BR>
- The traffic counters will roll over from time to time.<BR>
- It's not possible to query hourly on how traffic the user has used since forever, because records older than around 90 days are dropped.<BR>
<BR>
Any ideas? Or am I barking up completely the wrong tree...<BR>
<BR>
Cheers,<BR>
Jonathan<BR>
<BR>
----------------------------<BR>
Jonathan Gazeley<BR>
Systems Support Specialist<BR>
ResNet | Wireless & VPN Team<BR>
Information Services<BR>
University of Bristol<BR>
----------------------------<BR>
<BR>
-<BR>
List info/subscribe/unsubscribe? See <A HREF="http://www.freeradius.org/list/users.html">http://www.freeradius.org/list/users.html</A><BR>
<BR>
<BR>
<BR>
<BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>