sqlcounter, counting data and large amounts of it

[CoMeC]

Hey,

I don't know if I understand everything correctly, but just take a look at
this:

http://wiki.freeradius.org/index.php/FAQ#Why_do_Acct-Input-Octets_and_Acct-Output-Octets_wrap_at_4_GB.3F

Maybe that is the solution.

Are you using Mikrotik? Any issues? 
I am going to use it too, so I am very curious! :D

Best regards,

CoMeC

On Thu, 13 Dec 2007 15:55:00 +1300, "Russell Tester"
<russell at serverworks.co.nz> wrote:
> Hi All,
> 
> I am new to the freeradius list but have been running freeradius for
> some time. We are changing the way we do some of our accounting here and
> have a requirement to provide users with monthly prepaid cards for
> specific data values, namely 1,5 and 10GB.
> 
> I have no problems making the pass timeout after the month, that bit is
> fine using the expiration check value.
> 
> Where I am faced with a problem is telling the nas about (we use
> Mikrotik's) and counting above the hair pulling 4GB boundary.
> 
> An example of my sqlcounter is below, this works perfectly fine anywhere
> up to 4GB, note that I run two of these one for up and one for
> downloaded data. Not 100% accurate in terms of the user could
> theoretically get the max amount of data up and down from their first
> session but accurate enough for us for now. I believe Mikrtoik v3 has a
> Total-Limit attribute which will fix this when its released.
> 
> sqlcounter prepay-data-down {
> 	counter-name = Max-All-Session-Data
> 	check-name = Max-All-Data
> 	reply-name = Mikrotik-Recv-Limit
> 	sqlmod-inst = sql
> 	key = User-Name
> 	reset = never
> 	query = "SELECT SUM(AcctInputOctets) + SUM(AcctOutputOctets)
> FROM radacct WHERE UserName='%{%k}'"
> }
> 
> Two problems exist with this setup:
> 
> 1. When I specify a larger than 4GB value in radcheck I get an
> Access-Reject.
> 2. I have no way to send the equivalent gigawords attribute to the NAS
> as well, I believe I need to send both the Mikrotik-Recv-Limit and
> Mikrtoik-Recv-Limit-Gigawords values.
> 
> I have spent some time playing myself and have managed to send the
> gigawords attribute by using another sqlcounter, and another radcheck
> attribute for gigawords, but get lost somewhere in the middle of
> checking both the gigawords and octets values against the radacct data
> and getting a sensible reply from them.
> 
> The other question I have relates to the action the sqlcounter performs
> when the limit is reached. Is there any way to modify the reply
> attribute that gets passed to the nas when the limit is reached? Either
> by changing the reply message, or passing say a rate-limit value to the
> NAS to throttle the user.
> 
> Any help to get this working, or examples of existing setups would be
> greatly appreciated.
> 
> Cheers,
> Russell Tester
> 
> 
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html