Dear Alan D. & List Users, I am reposting this message only because it bounced last due to size error. I tried to reduce the size but less information to see. Apologies if it was sent but i received bounce and no response from any member. ---------------: Orignal Message : ----------- I tried to search the mail archives for the similar topic but did not find matched results. Then read wiki again to crosscheck that if i have done it well before posting on the mailing list. I am sorry if similar post has been answered before. I have problem with "rlm_sqlcounter" module. That is "noresetcounter" it always return "0". But if i manually run the query to gerp the users accounting data from MySQL DB, i get accounting results. it seems that everything is in place but unable to know why sql counter still returns zero. whereas dailycounter works fine without any problem. My rlm_sqlnoresetcounter module: sqlcounter noresetcounter { driver = rlm_sqlcounter counter-name = Max-All-Session-Time check-name = Max-All-Session sqlmod-inst = sql key = User-Name reset = never query = "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='${key}'" } To Prove the point i would also need to send radtest results for comparison: rad_recv: Access-Request packet from host 127.0.0.1 port 47871, id=159, length=84 User-Name = "KV60Rp2vtrEe5B" User-Password = "yuxNtwKbSH" NAS-IP-Address = 31.241.205.211 NAS-Port = 1812 Message-Authenticator = 0x2e53acc3936310719d3dc335a640afd4 # Executing section authorize from file /etc/raddb/sites-enabled/default +- entering group authorize {...} ++[preprocess] returns ok ++[chap] returns noop ++[mschap] returns noop ++[digest] returns noop [suffix] No '@' in User-Name = "KV60Rp2vtrEe5B", looking up realm NULL [suffix] No such realm "NULL" ++[suffix] returns noop [eap] No EAP-Message, not doing EAP ++[eap] returns noop [sql] expand: %{User-Name} -> KV60Rp2vtrEe5B [sql] sql_set_user escaped user --> 'KV60Rp2vtrEe5B' 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 = 'KV60Rp2vtrEe5B' 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 = 'KV60Rp2vtrEe5B' ORDER BY id [sql] expand: SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority -> SELECT groupname FROM radusergroup WHERE username = 'KV60Rp2vtrEe5B' ORDER BY priority [sql] expand: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'One_Hour_Access' ORDER BY id [sql] User found in group One_Hour_Access [sql] expand: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = 'One_Hour_Access' ORDER BY id rlm_sql (sql): Released sql socket id: 3 ++[sql] returns ok ++[pap] returns updated rlm_counter: Entering module authorize code rlm_counter: Could not find Check item value pair ++[daily] returns noop rlm_sqlcounter: Entering module authorize code sqlcounter_expand: 'SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='User-Name'' [noresetcounter] expand: SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='User-Name' -> SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='User-Name' *WARNING: Please replace '%S' with '${sqlmod-inst}'* sqlcounter_expand: '%{sql:SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='User-Name'}' [noresetcounter] sql_xlat [noresetcounter] expand: %{User-Name} -> KV60Rp2vtrEe5B [noresetcounter] sql_set_user escaped user --> 'KV60Rp2vtrEe5B' [noresetcounter] expand: SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='User-Name' -> SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='User-Name' rlm_sql (sql): Reserving sql socket id: 2 [noresetcounter] sql_xlat finished rlm_sql (sql): Released sql socket id: 2 [noresetcounter] expand: %{sql:SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='User-Name'} -> 0 rlm_sqlcounter: Check item is greater than query result rlm_sqlcounter: Authorized user KV60Rp2vtrEe5B, check_item=3600, counter=0 <------- This Value rlm_sqlcounter: Sent Reply-Item for user KV60Rp2vtrEe5B, Type=Session-Timeout, value=3600 ++[noresetcounter] returns ok [expiration] Checking Expiration time: '10 Jan 2013' ++[expiration] returns ok ++[logintime] returns noop 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. !!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! # Executing group from file /etc/raddb/sites-enabled/default +- entering group PAP {...} [pap] login attempt with password "yuxNtwKbSH" [pap] Using clear text password "yuxNtwKbSH" [pap] User authenticated successfully ++[pap] returns ok # Executing section session from file /etc/raddb/sites-enabled/default +- entering group session {...} [radutmp] expand: /var/log/radius/radutmp -> /var/log/radius/radutmp [radutmp] expand: %{User-Name} -> KV60Rp2vtrEe5B ++[radutmp] returns ok # Executing section post-auth from file /etc/raddb/sites-enabled/default +- entering group post-auth {...} [sql] expand: %{User-Name} -> KV60Rp2vtrEe5B [sql] sql_set_user escaped user --> 'KV60Rp2vtrEe5B' [sql] expand: %{User-Password} -> yuxNtwKbSH [sql] expand: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') -> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'KV60Rp2vtrEe5B', 'yuxNtwKbSH', 'Access-Accept', '2013-01-07 08:41:59') rlm_sql (sql) in sql_postauth: query is INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'KV60Rp2vtrEe5B', 'yuxNtwKbSH', 'Access-Accept', '2013-01-07 08:41:59') 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 159 to 127.0.0.1 port 47871 Session-Timeout := 3600 Idle-Timeout := 60 Acct-Interim-Interval := 120 Finished request 0. Going to the next request Waking up in 4.9 seconds. Cleaning up request 0 ID 159 with timestamp +311 Ready to process requests. Now lets grep the accounting value from database for the same user (returned "0" earlier) This user has "Max-All-Session" attribute applied and exist in "radgroupreply" table. mysql> SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='KV60Rp2vtrEe5B'; +--------------------------------+ | IFNULL(SUM(AcctSessionTime),0) | +--------------------------------+ | 380802 | +--------------------------------+ 1 row in set (0.00 sec) Why rlm_counter returns the zero value. Thanks / Russell M.
Mike Russell wrote:
I tried to search the mail archives for the similar topic but did not find matched results. Then read wiki again to crosscheck that if i have done it well before posting on the mailing list. I am sorry if similar post has been answered before. I have problem with "rlm_sqlcounter" module. That is "noresetcounter" it always return "0". But if i manually run the query to gerp the users accounting data from MySQL DB, i get accounting results. it seems that everything is in place but unable to know why sql counter still returns zero. whereas dailycounter works fine without any problem.
The sqlcounter module should be pretty simple. Configure accounting data to go into SQL, and ensure that the server is receiving accounting packets. Then, configure the sqlcounter module.
My rlm_sqlnoresetcounter module:
sqlcounter noresetcounter { driver = rlm_sqlcounter
Don't invent configuration. This line does NOTHING. DELETE IT.
counter-name = Max-All-Session-Time check-name = Max-All-Session sqlmod-inst = sql key = User-Name reset = never query = "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='${key}'"
You edited the default configuration, and broke it. Don't do that. The default uses %{${key}}, not ${key}. They're different.
To Prove the point i would also need to send radtest results for comparison:
It would help to read the output.
sqlcounter_expand: 'SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='User-Name'' ... [noresetcounter] expand: SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='User-Name' -> SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='User-Name'
Is the UserName field in SQL "User-Name"? I know that there's a lot of debugging output. But the entire point if it is for you to *read it*. Look for inconsistencies.
Now lets grep the accounting value from database for the same user (returned "0" earlier) This user has "Max-All-Session" attribute applied and exist in "radgroupreply" table.
mysql> SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='KV60Rp2vtrEe5B';
You see how that's different from the query in the debug output? You're testing *different things*. The point of debugging output is for you to CUT & PASTE the SQL queries, to see what they do when run manually. You didn't do that. You ran a *different* query.
Why rlm_counter returns the zero value.
Don't break the server. Alan DeKok.
participants (2)
-
Alan DeKok -
Mike Russell