sqlcounter returning wrong value?

[tnt at kalik.net]

No, he wants a data not time counter.

Ivan Kalik
Kalik Informatika ISP


Dana 24/10/2008, "mulianto" <mulianto at cni.co.id> piše:

>hi..i think you should fix this one :
>
>                reply-name = ChilliSpot-Max-Total-Octets to :
>                reply-name = Session-Timeout
>
>try it..
>rgds,
>Mulianto
>
>http://www.indohotspot.net
>Your Hotspot solution
>  -----Original Message-----
>  From: freeradius-users-bounces+mulianto=cni.co.id at lists.freeradius.org
>[mailto:freeradius-users-bounces+mulianto=cni.co.id at lists.freeradius.org]On
>Behalf Of liran tal
>  Sent: Friday, October 24, 2008 1:07 AM
>  To: FreeRadius users mailing list
>  Subject: sqlcounter returning wrong value?
>
>
>
>  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.
>
>