some mail about freeradius sqlcounter limit the octets

Hey,

I’m experimenting with some sqlcounter directives in radiusd.conf and chilli
as the NAS.
I’ve defined the following sqlcounter stanza for a daily traffic limit:

sqlcounter defined in radiusd.conf:
(the query was corrected as suggested by tnt on a previous thread on the
list, correct me if I got it wrong please)

sqlcounter counterChilliSpotMaxDailyOctets {
counter-name = ChilliSpot-Max-Daily-Octets
check-name = ChilliSpot-Max-Daily-Octets
reply-name = ChilliSpot-Max-Total-Octets
sqlmod-inst = sql
key = User-Name
reset = daily
error-msg = “Sorry, your maximum traffic usage (download and
upload) has exceed the provided limit”
query = “SELECT (SUM(AcctInputOctets + AcctOutputOctets))
FROM radacct WHERE UserName=’%{%k}’ AND UNIX_TIMESTAMP(AcctStartTime) +
AcctSessionTime > ‘%b’”
}

In the authorization phase, I’m seeing the following in debug log:

rlm_sqlcounter: Entering module authorize code
sqlcounter_expand: ‘SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets))
FROM radacct WHERE UserName=’%{User-Name}”
radius_xlat: ‘SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM
radacct WHERE UserName=’tester1”
sqlcounter_expand: ‘%{sql:SELECT
(SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
UserName=’tester1′}’
radius_xlat: Running registered xlat function of module sql for string
‘SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
UserName=’tester1”
rlm_sql (sql): – sql_xlat
radius_xlat: ‘tester1′
rlm_sql (sql): sql_set_user escaped user –> ‘tester1′
radius_xlat: ‘SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM
radacct WHERE UserName=’tester1”
rlm_sql (sql): Reserving sql socket id: 2
rlm_sql (sql): – sql_xlat finished
rlm_sql (sql): Released sql socket id: 2
radius_xlat: ‘24004370′
rlm_sqlcounter: (Check item – counter) is greater than zero
rlm_sqlcounter: Authorized user tester1, check_item=26214400,
counter=24004370
rlm_sqlcounter: Sent Reply-Item for user tester1,
Type=ChilliSpot-Max-Total-Octets, value=26239950
modcall[authorize]: module “counterChilliSpotMaxDailyOctets” returns ok
for request 0

The entry in radcheck is as follows:

| 346 | tester1 | ChilliSpot-Max-Daily-Octets | := | 26214400 |

What happens is that it seems the counter doesn’t work as expected. When a
user logs in, performs some traffic usage, logs out and logs in again,
the replied back attribute for chilli doesn’t contain a value which is the
remainder of the traffic usage, but something else.

According to the radius debug above, if check_item=26214400 and
counter=24004370, how come value=26239950?
So I’m guessing I’m missing something but I’m too obsessed with the
sqlcounter to notice it. (is the subtractation not a normal decimal action?)

The FreeRADIUS version used is 1.1.7

Regards,
Liran.