sqlcounter, counting data and large amounts of it

Russell Tester russell at serverworks.co.nz
Thu Dec 13 03:55:00 CET 2007

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
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.

Russell Tester

More information about the Freeradius-Users mailing list