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.