<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.6000.20591" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=312154407-24102008><FONT face=Arial color=#0000ff size=2>hi..i
think you should fix this one :</FONT></SPAN></DIV>
<DIV><SPAN class=312154407-24102008><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN
class=312154407-24102008>
reply-name = ChilliSpot-Max-Total-Octets to :</SPAN></DIV>
<DIV><SPAN
class=312154407-24102008>
reply-name = Session-Timeout</SPAN></DIV>
<DIV><SPAN class=312154407-24102008></SPAN> </DIV>
<DIV><SPAN class=312154407-24102008><FONT face=Arial color=#0000ff size=2>try
it..</FONT></SPAN></DIV>
<DIV><SPAN class=312154407-24102008><FONT face=Arial color=#0000ff
size=2>rgds,</FONT></SPAN></DIV>
<DIV><SPAN class=312154407-24102008><FONT face=Arial color=#0000ff
size=2>Mulianto</FONT></SPAN></DIV>
<DIV><SPAN class=312154407-24102008><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=312154407-24102008><FONT face=Arial color=#0000ff size=2><A
href="http://www.indohotspot.net">http://www.indohotspot.net</A></FONT></SPAN></DIV>
<DIV><SPAN class=312154407-24102008><FONT face=Arial color=#0000ff size=2>Your
Hotspot solution</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
freeradius-users-bounces+mulianto=cni.co.id@lists.freeradius.org
[mailto:freeradius-users-bounces+mulianto=cni.co.id@lists.freeradius.org]<B>On
Behalf Of </B>liran tal<BR><B>Sent:</B> Friday, October 24, 2008 1:07
AM<BR><B>To:</B> FreeRadius users mailing list<BR><B>Subject:</B> sqlcounter
returning wrong value?<BR><BR></FONT></DIV>
<DIV dir=ltr>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV>Hey,</DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV>I'm experimenting with some sqlcounter directives in radiusd.conf and
chilli as the NAS.</DIV>
<DIV>I've defined the following sqlcounter stanza for a daily traffic
limit:</DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV class=de2>sqlcounter defined in radiusd.conf:</DIV>
<DIV class=de1>
<DIV class=de2>(the query was corrected as suggested by tnt on a previous
thread on the list, correct me if I got it wrong please)</DIV></DIV>
<DIV class=de1><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV class=de2> sqlcounter
counterChilliSpotMaxDailyOctets {</DIV>
<DIV class=de1>
counter-name = ChilliSpot-Max-Daily-Octets</DIV>
<DIV class=de2>
check-name = ChilliSpot-Max-Daily-Octets</DIV>
<DIV class=de1>
reply-name = ChilliSpot-Max-Total-Octets</DIV>
<DIV class=de2>
sqlmod-inst = sql</DIV>
<DIV class=de1> key =
User-Name</DIV>
<DIV class=de2> reset =
daily</DIV>
<DIV class=de1>
error-msg = "Sorry, your maximum traffic usage (download and upload) has
exceed the provided limit"</DIV>
<DIV class=de2> query =
"SELECT (SUM(AcctInputOctets + AcctOutputOctets)) FROM radacct WHERE
UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime >
'%b'"</DIV>
<DIV class=de1> }</DIV>
<DIV class=de2><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV class=de2><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV class=de2><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV class=de2>In the authorization phase, I'm seeing the following in debug
log:</DIV>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">
<DIV class=de2>rlm_sqlcounter: Entering module authorize code</DIV>
<DIV class=de2>sqlcounter_expand: 'SELECT
(SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
UserName='%{User-Name}''</DIV>
<DIV class=de2>radius_xlat: 'SELECT
(SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
UserName='tester1''</DIV>
<DIV class=de2>sqlcounter_expand: '%{sql:SELECT
(SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
UserName='tester1'}'</DIV>
<DIV class=de2>radius_xlat: Running registered xlat function of module sql
for string 'SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct
WHERE UserName='tester1''</DIV>
<DIV class=de2>rlm_sql (sql): - sql_xlat</DIV>
<DIV class=de2>radius_xlat: 'tester1'</DIV>
<DIV class=de2>rlm_sql (sql): sql_set_user escaped user -->
'tester1'</DIV>
<DIV class=de2>radius_xlat: 'SELECT
(SUM(AcctInputOctets)+SUM(AcctOutputOctets)) FROM radacct WHERE
UserName='tester1''</DIV>
<DIV class=de2>rlm_sql (sql): Reserving sql socket id: 2</DIV>
<DIV class=de2>rlm_sql (sql): - sql_xlat finished</DIV>
<DIV class=de2>rlm_sql (sql): Released sql socket id: 2</DIV>
<DIV class=de2>radius_xlat: '24004370'</DIV>
<DIV class=de2>rlm_sqlcounter: (Check item - counter) is greater than
zero</DIV>
<DIV class=de2>rlm_sqlcounter: Authorized user tester1, check_item=26214400,
counter=24004370</DIV>
<DIV class=de2>rlm_sqlcounter: Sent Reply-Item for user tester1,
Type=ChilliSpot-Max-Total-Octets, value=26239950</DIV>
<DIV class=de2> modcall[authorize]: module
"counterChilliSpotMaxDailyOctets" returns ok for request 0</DIV></BLOCKQUOTE>
<DIV class=de2>The entry in radcheck is as follows:</DIV>
<DIV class=de2> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=de2>| 346 | tester1 | ChilliSpot-Max-Daily-Octets | := | 26214400
|</DIV></BLOCKQUOTE>
<DIV class=de2>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,</DIV>
<DIV class=de2>the replied back attribute for chilli doesn't contain a
value which is the remainder of the traffic usage, but something else.</DIV>
<DIV class=de2> </DIV>
<DIV class=de2>According to the radius debug above, if check_item=26214400 and
counter=24004370, how come value=26239950?</DIV>
<DIV class=de2>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?)</DIV>
<DIV class=de2> </DIV>
<DIV class=de2>The FreeRADIUS version used is 1.1.7</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>Regards,</DIV>
<DIV>Liran.</DIV>
<DIV> </DIV></DIV></BLOCKQUOTE></BODY></HTML>