Thanks for reply, I am using linksys WRT54GL router with DD-WRT and Chillispot. SQLCounter works only when radacct tables contains accounting information of logging user. On Fri, Dec 19, 2008 at 5:08 PM, Padam J Singh <padam.singh@inventum.cc> wrote:

The NAS has to implement session disconnection after the requisite time/byte limit.

What NAS are you using?

Padam

pushpraj nimbalkar wrote:

Hello All; I just installed freeradius on fedora 10. freeradius version is "freeradius-2.1.1-2.fc10.i386" i also installed radius mysql packages. After basic set up I configured radius with mysql. All thing are working fine but i am not able to resolve sqlcounter issue. I have defined counters in counter.conf file and included those counters in /etc/raddb/sites-enabled/default file. When i create user with 30 minutes session time(Max-All-Session). Then user should be able to login only for 30 minutes. But what is happening is when user connects he can be online as much time as he can. That 30 mintutes counter dosent wotks. But when same user first logins for 5 minutes and logs off and again logins then counter works perfectly means user can be online only for remaining time i.e. for 25 minutes. So i think sqlcounter works only when there is accounting records for loging user inside radacct table but when user first time logins then there will not be any records inside radacct table. Same thing happening with octate account.

Please Help.

Thanks, Pushpraj N.

________________________________ - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

-- PGP Id 9EED2E09

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Sir, when user first connects following reply sent to nas Sending Access-Accept of id 0 to 192.168.2.5 port 2051 Idle-Timeout := 600 Acct-Interim-Interval := 60 and when same user connects 2nd time following reply is sent Sending Access-Accept of id 0 to 192.168.2.5 port 2051 Idle-Timeout := 600 Acct-Interim-Interval := 60 Session-Timeout = 1731 And one more thing when 1st reply send then user can be online for maximum time but when user disconnects and connects once again then second reply is send and his connection gets disconnected as soon session-timeout value reach. i can post radius -X output if you need more information. On Fri, Dec 19, 2008 at 5:54 PM, Padam J Singh <padam.singh@inventum.cc> wrote:

pushpraj nimbalkar wrote:

Thanks for reply,

I am using linksys WRT54GL router with DD-WRT and Chillispot. SQLCounter works only when radacct tables contains accounting information of logging user.

Please also post the reply sent back to the NAS - run the radius server in debug mode and check if correct Session-Timeout values are getting passed or not.

On Fri, Dec 19, 2008 at 5:08 PM, Padam J Singh <padam.singh@inventum.cc> wrote:

The NAS has to implement session disconnection after the requisite time/byte limit.

What NAS are you using?

Padam

pushpraj nimbalkar wrote:

Hello All; I just installed freeradius on fedora 10. freeradius version is "freeradius-2.1.1-2.fc10.i386" i also installed radius mysql packages. After basic set up I configured radius with mysql. All thing are working fine but i am not able to resolve sqlcounter issue. I have defined counters in counter.conf file and included those counters in /etc/raddb/sites-enabled/default file. When i create user with 30 minutes session time(Max-All-Session). Then user should be able to login only for 30 minutes. But what is happening is when user connects he can be online as much time as he can. That 30 mintutes counter dosent wotks. But when same user first logins for 5 minutes and logs off and again logins then counter works perfectly means user can be online only for remaining time i.e. for 25 minutes. So i think sqlcounter works only when there is accounting records for loging user inside radacct table but when user first time logins then there will not be any records inside radacct tab!

le. Same thing happening with octate account.

Please Help.

Thanks, Pushpraj N.

________________________________ - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

-- PGP Id 9EED2E09

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

-- PGP Id 9EED2E09

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

On Fri, Dec 19, 2008 at 6:20 PM, <tnt@kalik.net> wrote:

...

I am using linksys WRT54GL router with DD-WRT and Chillispot. SQLCounter works only when radacct tables contains accounting information of logging user.

It should still limit to 30 minutes if user has no record. Post the debug of the request for a user that has no radacct entries.

Ivan Kalik Kalik Informatika ISP

Sir, below is the debugging output of radiusd -X when user jose connects. This user has access limit of 30 minutes. Ready to process requests. rad_recv: Access-Request packet from host 192.168.2.5 port 2051, id=0, length=193 User-Name = "jose" User-Password = "j" NAS-IP-Address = 0.0.0.0 Service-Type = Login-User Framed-IP-Address = 192.168.182.4 Calling-Station-Id = "00-0D-9D-C8-E9-3F" Called-Station-Id = "00-22-6B-54-07-21" NAS-Identifier = "AP1" Acct-Session-Id = "494be62700000000" NAS-Port-Type = Wireless-802.11 NAS-Port = 0 Message-Authenticator = 0x5835b4a66d23067a2e6977bce5e08904 WISPr-Logoff-URL = "http://192.168.182.1:3990/logoff" +- entering group authorize {...} ++[preprocess] returns ok ++[chap] returns noop ++[mschap] returns noop [suffix] No '@' in User-Name = "jose", looking up realm NULL [suffix] No such realm "NULL" ++[suffix] returns noop [sql] expand: %{User-Name} -> jose [sql] sql_set_user escaped user --> 'jose' rlm_sql (sql): Reserving sql socket id: 3 [sql] expand: SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = 'jose' ORDER BY id [sql] User found in radcheck table [sql] expand: SELECT id, UserName, Attribute, Value, op FROM radreply WHERE Username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, UserName, Attribute, Value, op FROM radreply WHERE Username = 'jose' ORDER BY id [sql] expand: SELECT GroupName FROM usergroup WHERE UserName='%{SQL-User-Name}' -> SELECT GroupName FROM usergroup WHERE UserName='jose' rlm_sql (sql): Released sql socket id: 3 ++[sql] returns ok rlm_sqlcounter: Entering module authorize code rlm_sqlcounter: Could not find Check item value pair ++[chillispot_max_bytes] returns noop rlm_sqlcounter: Entering module authorize code rlm_sqlcounter: Could not find Check item value pair ++[chillispot_max_bytes_output] returns noop rlm_sqlcounter: Entering module authorize code rlm_sqlcounter: Could not find Check item value pair ++[chillispot_max_bytes_input] returns noop rlm_sqlcounter: Entering module authorize code rlm_sqlcounter: Could not find Check item value pair ++[chillispot_max_bytes] returns noop rlm_sqlcounter: Entering module authorize code rlm_sqlcounter: Could not find Check item value pair ++[chillispot_max_bytes_output] returns noop rlm_sqlcounter: Entering module authorize code rlm_sqlcounter: Could not find Check item value pair ++[chillispot_max_bytes_input] returns noop rlm_sqlcounter: Entering module authorize code sqlcounter_expand: 'SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{User-Name}'' [noresetcounter] expand: SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{User-Name}' -> SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='jose' sqlcounter_expand: '%{sql:SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='jose'}' [noresetcounter] sql_xlat [noresetcounter] expand: %{User-Name} -> jose [noresetcounter] sql_set_user escaped user --> 'jose' [noresetcounter] expand: SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='jose' -> SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='jose' rlm_sql (sql): Reserving sql socket id: 2 [noresetcounter] row[0] returned NULL rlm_sql (sql): Released sql socket id: 2 [noresetcounter] expand: %{sql:SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='jose'} -> rlm_sqlcounter: No integer found in string "" ++[noresetcounter] returns noop rlm_sqlcounter: Entering module authorize code rlm_sqlcounter: Could not find Check item value pair ++[hourlycounter] returns noop rlm_sqlcounter: Entering module authorize code rlm_sqlcounter: Could not find Check item value pair ++[dailycounter] returns noop rlm_sqlcounter: Entering module authorize code rlm_sqlcounter: Could not find Check item value pair ++[monthlycounter] returns noop rlm_sqlcounter: Entering module authorize code rlm_sqlcounter: Could not find Check item value pair ++[yearlycounter] returns noop ++[expiration] returns noop ++[logintime] returns noop ++[pap] returns updated Found Auth-Type = PAP !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!! Replacing User-Password in config items with Cleartext-Password. !!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!! Please update your configuration so that the "known good" !!! !!! clear text password is in Cleartext-Password, and not in User-Password. !!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! +- entering group PAP {...} [pap] login attempt with password "j" [pap] Using clear text password "j" [pap] User authenticated successfully ++[pap] returns ok +- entering group post-auth {...} [sql] expand: %{User-Name} -> jose [sql] sql_set_user escaped user --> 'jose' [sql] expand: CALL TimeToFinish('%{SQL-User-Name}') -> CALL TimeToFinish('jose') rlm_sql (sql) in sql_postauth: query is CALL TimeToFinish('jose') rlm_sql (sql): Reserving sql socket id: 1 rlm_sql (sql): Released sql socket id: 1 ++[sql] returns ok ++[exec] returns noop Sending Access-Accept of id 0 to 192.168.2.5 port 2051 Idle-Timeout := 600 Acct-Interim-Interval := 60 Finished request 0. Going to the next request Waking up in 4.9 seconds. rad_recv: Accounting-Request packet from host 192.168.2.5 port 2049, id=233, length=127 Acct-Status-Type = Start User-Name = "jose" Calling-Station-Id = "00-0D-9D-C8-E9-3F" Called-Station-Id = "00-22-6B-54-07-21" NAS-Port-Type = Wireless-802.11 NAS-Port = 0 NAS-Port-Id = "00000000" NAS-IP-Address = 0.0.0.0 NAS-Identifier = "AP1" Framed-IP-Address = 192.168.182.4 Acct-Session-Id = "494be62700000000" +- entering group preacct {...} ++[preprocess] returns ok [acct_unique] Hashing 'NAS-Port = 0,Client-IP-Address = 192.168.2.5,NAS-IP-Address = 0.0.0.0,Acct-Session-Id = "494be62700000000",User-Name = "jose"' [acct_unique] Acct-Unique-Session-ID = "60ffdc637d6921ba". ++[acct_unique] returns ok [suffix] No '@' in User-Name = "jose", looking up realm NULL [suffix] No such realm "NULL" ++[suffix] returns noop ++[files] returns noop +- entering group accounting {...} [detail] expand: /var/log/radius/radacct/%{Client-IP-Address}/detail-%Y%m%d -> /var/log/radius/radacct/192.168.2.5/detail-20081219 [detail] /var/log/radius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands to /var/log/radius/radacct/192.168.2.5/detail-20081219 [detail] expand: %t -> Fri Dec 19 18:31:48 2008 ++[detail] returns ok ++[unix] returns ok [radutmp] expand: /var/log/radius/radutmp -> /var/log/radius/radutmp [radutmp] expand: %{User-Name} -> jose ++[radutmp] returns ok [sql] expand: %{User-Name} -> jose [sql] sql_set_user escaped user --> 'jose' [sql] expand: INSERT into radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', '%S', '0', '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0') -> INSERT into radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, A rlm_sql (sql): Reserving sql socket id: 0 rlm_sql (sql): Released sql socket id: 0 ++[sql] returns ok [attr_filter.accounting_response] expand: %{User-Name} -> jose attr_filter: Matched entry DEFAULT at line 12 ++[attr_filter.accounting_response] returns updated Sending Accounting-Response of id 233 to 192.168.2.5 port 2049 Finished request 1. Cleaning up request 1 ID 233 with timestamp +17 Going to the next request Waking up in 4.9 seconds. Cleaning up request 0 ID 0 with timestamp +17 Ready to process requests.

On Fri, Dec 19, 2008 at 8:28 PM, <tnt@kalik.net> wrote:

...

sqlcounter_expand: 'SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{User-Name}'' [noresetcounter] expand: SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{User-Name}' -> SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='jose' sqlcounter_expand: '%{sql:SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='jose'}' [noresetcounter] sql_xlat [noresetcounter] expand: %{User-Name} -> jose [noresetcounter] sql_set_user escaped user --> 'jose' [noresetcounter] expand: SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='jose' -> SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='jose' rlm_sql (sql): Reserving sql socket id: 2 [noresetcounter] row[0] returned NULL rlm_sql (sql): Released sql socket id: 2 [noresetcounter] expand: %{sql:SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='jose'} -> rlm_sqlcounter: No integer found in string "" ++[noresetcounter] returns noop

OK, I see the problem. As first aid change the query to:

SELECT COALESCE(SUM(AcctSessionTime) FROM radacct WHERE

...

UserName='%{User-Name},0)

For some reason code doesn't treat null as 0.

Ivan Kalik Kalik Informatika ISP

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Thank you sir, i tried your query but it's gives me syntax error but from your reply my senior person told me that try running below query which is working perfectly for me. query is below: "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='%{%k}'" but one thing i wanted to ask you why NAS-IP-Addres is always shown as 0.0.0.0 in my case nas ip address is 192.168.2.5. do i need to make any other configuration.