sqlcounter returning wrong value?

[liran tal]

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20081023/639b201c/attachment.html>