Hi Alan, Looks like i am getting some meaningful debugs in fr-2.1.10. Undermentioned are the Counter and Debug Messages. I used %% escape but in the query it's '%d %M %Y %H:%i:%s' . Seems like that's how escaping works. I assumed that i needed to add more % but adding more doesn't help . It seems that more than 3% and it starts truncating . If i give %%%%% , It's stripped to %%d %%M %%Y %%H:%%i:%%s Case 1: sqlcounter monthlycounter { counter-name = Monthly-Session-Time check-name = Max-Monthly-Session reply-name = Session-Timeout sqlmod-inst = sql key = User-Name reset = never query = "SELECT SUM(acctsessiontime) FROM radacct where \ username = '%{%k}' AND acctstarttime BETWEEN \ (SELECT STR_TO_DATE((SELECT value FROM radcheck \ WHERE username = '%{%k}' AND attribute = 'Activation'), '%%d %%M %%Y %%H:%%i:%%s')) \ AND (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = '%{%k}' \ AND attribute = 'Expiration'), '%%d %%M %%Y %%H:%%i:%%s'))" } DEBUG LOGS rlm_sqlcounter: Entering module authorize code rlm_sqlcounter: Could not find Check item value pair ++[noresetcounter] 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 sqlcounter_expand: 'SELECT SUM(acctsessiontime) FROM radacct where username = '%{User-Name}' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = '%{User-Name}' AND attribute = 'Activation'), '%d %M %Y %H:%i:%s')) AND (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = '%{User-Name}' AND attribute = 'Expiration'), '%d %M %Y %H:%i:%s'))' [monthlycounter] expand: SELECT SUM(acctsessiontime) FROM radacct where username = '%{User-Name}' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = '%{User-Name}' AND attribute = 'Activation'), '%d %M %Y %H:%i:%s')) AND (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = '%{User-Name}' AND attribute = 'Expiration'), '%d %M %Y %H:%i:%s')) -> SELECT SUM(acctsessiontime) FROM radacct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Activation'), '15 0 2011 18:001122334455:_')) AND (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Expiration'), '15 0 2011 18:001122334455:_')) sqlcounter_expand: '%{sql:SELECT SUM(acctsessiontime) FROM radacct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Activation'), '15 0 2011 18:001122334455:_')) AND (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Expiration'), '15 0 2011 18:001122334455:_'))}' [monthlycounter] sql_xlat [monthlycounter] expand: %{User-Name} -> suman [monthlycounter] sql_set_user escaped user --> 'suman' [monthlycounter] expand: SELECT SUM(acctsessiontime) FROM radacct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Activation'), '15 0 2011 18:001122334455:_')) AND (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Expiration'), '15 0 2011 18:001122334455:_')) -> SELECT SUM(acctsessiontime) FROM radacct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Activation'), '15 0 2011 18:001122334455:_')) AND (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Expiration'), '15 0 2011 18:001122334455:_')) rlm_sql (sql): Reserving sql socket id: 2 [monthlycounter] row[0] returned NULL rlm_sql (sql): Released sql socket id: 2 [monthlycounter] expand: %{sql:SELECT SUM(acctsessiontime) FROM radacct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Activation'), '15 0 2011 18:001122334455:_')) AND (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Expiration'), '15 0 2011 18:001122334455:_'))} -> rlm_sqlcounter: No integer found in string "" ++[monthlycounter] returns noop [expiration] Checking Expiration time: '5 Apr 2011 23:59:59' ++[expiration] returns ok ++[logintime] returns noop ++[pap] returns updated Found Auth-Type = PAP # Executing group from file /usr/local/etc/raddb/sites-enabled/default +- entering group PAP {...} [pap] login attempt with password "duman12" [pap] Using CRYPT password "AEgNJ3NmPjAKs" [pap] User authenticated successfully ++[pap] returns ok # Executing section post-auth from file /usr/local/etc/raddb/sites-enabled/default +- entering group post-auth {...} [sqlippool] No Pool-Name defined. [sqlippool] expand: No Pool-Name defined (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) -> No Pool-Name defined (did cli 001122334455 port user suman) No Pool-Name defined (did cli 001122334455 port user suman) ++[sqlippool] returns noop [sql] expand: %{User-Name} -> suman [sql] sql_set_user escaped user --> 'suman' [sql] expand: %{User-Password} -> duman12 [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 ( 'suman', 'duman12', 'Access-Accept', '2011-03-15 18:27:05') rlm_sql (sql) in sql_postauth: query is INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'suman', 'duman12', 'Access-Accept', '2011-03-15 18:27:05') 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 31 to 122.175.85.117 port 12279 Session-Timeout = 1834374 Finished request 0. Going to the next request Waking up in 4.9 seconds. Case 2 sqlcounter monthlycounter { counter-name = Monthly-Session-Time check-name = Max-Monthly-Session reply-name = Session-Timeout sqlmod-inst = sql key = User-Name reset = never query = "SELECT SUM(acctsessiontime) FROM radacct where \ username = '%{%k}' AND acctstarttime BETWEEN \ (SELECT STR_TO_DATE((SELECT value FROM radcheck \ WHERE username = '%{%k}' AND attribute = 'Activation'), '%%%%d %%%%M %%%%Y %%%%H:%%%%i:%%%%s')) \ AND (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = '%{%k}' \ AND attribute = 'Expiration'), '%%%%d %%%%M %%%%Y %%%%H:%%%%i:%%%%s'))" } DEBUG Logs Listening on authentication address * port 1812 Listening on accounting address * port 1813 Listening on command file /usr/local/var/run/radiusd/radiusd.sock Listening on authentication address 127.0.0.1 port 18120 as server inner-tunnel Listening on proxy address * port 1814 Ready to process requests. rad_recv: Access-Request packet from host 122.175.85.117 port 12893, id=35, length=59 User-Name = "suman" User-Password = "duman12" Calling-Station-Id = "001122334455" # Executing section authorize from file /usr/local/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 = "suman", looking up realm NULL [suffix] No such realm "NULL" ++[suffix] returns noop [eap] No EAP-Message, not doing EAP ++[eap] returns noop ++[files] returns noop [sql] expand: %{User-Name} -> suman [sql] sql_set_user escaped user --> 'suman' 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 = 'suman' 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 = 'suman' ORDER BY id [sql] expand: SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority -> SELECT groupname FROM radusergroup WHERE username = 'suman' ORDER BY priority 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 ++[noresetcounter] 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 sqlcounter_expand: 'SELECT SUM(acctsessiontime) FROM radacct where username = '%{User-Name}' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = '%{User-Name}' AND attribute = 'Activation'), '%%d %%M %%Y %%H:%%i:%%s')) AND (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = '%{User-Name}' AND attribute = 'Expiration'), '%%d %%M %%Y %%H:%%i:%%s'))' [monthlycounter] expand: SELECT SUM(acctsessiontime) FROM radacct where username = '%{User-Name}' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = '%{User-Name}' AND attribute = 'Activation'), '%%d %%M %%Y %%H:%%i:%%s')) AND (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = '%{User-Name}' AND attribute = 'Expiration'), '%%d %%M %%Y %%H:%%i:%%s')) -> SELECT SUM(acctsessiontime) FROM radacct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Activation'), '%d %M %Y %H:%i:%s')) AND (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Expiration'), '%d %M %Y %H:%i:%s')) sqlcounter_expand: '%{sql:SELECT SUM(acctsessiontime) FROM radacct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Activation'), '%d %M %Y %H:%i:%s')) AND (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Expiration'), '%d %M %Y %H:%i:%s'))}' [monthlycounter] sql_xlat [monthlycounter] expand: %{User-Name} -> suman [monthlycounter] sql_set_user escaped user --> 'suman' [monthlycounter] expand: SELECT SUM(acctsessiontime) FROM radacct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Activation'), '%d %M %Y %H:%i:%s')) AND (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Expiration'), '%d %M %Y %H:%i:%s')) -> SELECT SUM(acctsessiontime) FROM radacct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Activation'), '15 0 2011 18:001122334455:_')) AND (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Expiration'), '15 0 2011 18:001122334455:_')) rlm_sql (sql): Reserving sql socket id: 2 [monthlycounter] row[0] returned NULL rlm_sql (sql): Released sql socket id: 2 [monthlycounter] expand: %{sql:SELECT SUM(acctsessiontime) FROM radacct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Activation'), '%d %M %Y %H:%i:%s')) AND (SELECT STR_TO_DATE((SELECT value FROM radcheck WHERE username = 'suman' AND attribute = 'Expiration'), '%d %M %Y %H:%i:%s'))} -> rlm_sqlcounter: No integer found in string "" ++[monthlycounter] returns noop [expiration] Checking Expiration time: '5 Apr 2011 23:59:59' ++[expiration] returns ok ++[logintime] returns noop ++[pap] returns updated Found Auth-Type = PAP # Executing group from file /usr/local/etc/raddb/sites-enabled/default +- entering group PAP {...} [pap] login attempt with password "duman12" [pap] Using CRYPT password "AEgNJ3NmPjAKs" [pap] User authenticated successfully ++[pap] returns ok # Executing section post-auth from file /usr/local/etc/raddb/sites-enabled/default +- entering group post-auth {...} [sqlippool] No Pool-Name defined. [sqlippool] expand: No Pool-Name defined (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) -> No Pool-Name defined (did cli 001122334455 port user suman) No Pool-Name defined (did cli 001122334455 port user suman) ++[sqlippool] returns noop [sql] expand: %{User-Name} -> suman [sql] sql_set_user escaped user --> 'suman' [sql] expand: %{User-Password} -> duman12 [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 ( 'suman', 'duman12', 'Access-Accept', '2011-03-15 18:53:17') rlm_sql (sql) in sql_postauth: query is INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'suman', 'duman12', 'Access-Accept', '2011-03-15 18:53:17') 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 35 to 122.175.85.117 port 12893 Session-Timeout = 1832802 Finished request 0. Going to the next request Waking up in 4.9 seconds. On 3/15/2011 3:29 PM, Alan DeKok wrote:
Suman Dash wrote:
sqlcounter monthlycounter { ... WHERE username = '%{%k}' AND attribute = 'Activation'), '%%%%d %%%%M %%%%Y %%%%H:%%%%i:%%%%s')) \ The debug log doesn't show that this string is being used.
And *again* you delete large amounts of the debug log. Why? It just makes it harder to help you.
In short: you are editing a configuration file, BUT the server isn't using the configuration file you're editing. That is likely the *major* source of the problems you're seeing.
And don't CC me on messages to the list. I *do* read the list. And especially do NOT set "return receipt requested". It's rude and annoying. If it keeps up, I'll just delete the messages unread.
Alan DeKok.
__________ Information from ESET NOD32 Antivirus, version of virus signature database 5924 (20110303) __________
The message was checked by ESET NOD32 Antivirus.