sqlcounter, counting data and large amounts of it

[tnt at kalik.co.yu]

Use rlm_perl instead of sqlcounter. That way you can return both gigaword
and octet limiting VSAs.

Ivan Kalik
Kalik Informatika ISP


Dana 13/12/2007, "Russell Tester" <russell at serverworks.co.nz> piše:

>CoMeC,
>
>Thanks for your reply, Yes I have read the FAQ, and understand why we
>need to wrap at 4GB, just can't get a solution working to limit users
>above 4GB. Accounting works fine above 4GB.
>
>We have been using Mikrtoik with Freeradius for around 4 years, yeah it
>works good :)
>
>Btw I forgot to mention I'm using 1.1.7.
>
>Thanks,
>Russell Tester
>
>-----Original Message-----
>From:
>freeradius-users-bounces+russell=serverworks.co.nz at lists.freeradius.org
>[mailto:freeradius-users-bounces+russell=serverworks.co.nz at lists.freerad
>ius.org] On Behalf Of CoMeC
>Sent: Thursday, 13 December 2007 7:12 p.m.
>To: FreeRadius users mailing list
>Subject: Re: sqlcounter, counting data and large amounts of it
>
>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_Ac
>ct-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
>
>-
>List info/subscribe/unsubscribe? See
>http://www.freeradius.org/list/users.html
>
>-
>List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
>