Re: SQL Counter Escape String !
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 tbl_acct where \ username = '%{%k}' AND acctstarttime BETWEEN \ (SELECT STR_TO_DATE((SELECT value FROM tbl_check \ WHERE username = '%{%k}' AND attribute = 'Activation'), '%%%%d %%%%M %%%%Y %%%%H:%%%%i:%%%%s')) \ AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{%k}' \ AND attribute = 'Expiration'), '%%%%d %%%%M %%%%Y %%%%H:%%%%i:%%%%s'))" DEBUG rlm_sql (sql): Reserving sql socket id: 3 [sql] expand: SELECT id, username, attribute, value, op FROM tbl_check WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM tbl_check WHERE username = 'suman' ORDER BY id [sql] User found in radcheck table [sql] expand: SELECT id, username, attribute, value, op FROM tbl_reply WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM tbl_reply WHERE username = 'suman' ORDER BY id [sql] expand: SELECT groupname FROM tbl_usergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority -> SELECT groupname FROM tbl_usergroup WHERE username = 'suman' ORDER BY priority [sql] expand: SELECT id, groupname, attribute, Value, op FROM tbl_groupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname, attribute, Value, op FROM tbl_groupcheck WHERE groupname = 'Biz1Mbps-UL' ORDER BY id [sql] User found in group Biz1Mbps-UL [sql] expand: SELECT id, groupname, attribute, value, op FROM tbl_groupreply WHERE groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname, attribute, value, op FROM tbl_groupreply WHERE groupname = 'Biz1Mbps-UL' ORDER BY id 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 ++[dailycounter] returns noop rlm_sqlcounter: Entering module authorize code sqlcounter_expand: 'SELECT SUM(acctsessiontime) FROM tbl_acct where username = '%{User-Name}' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s'))' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] expand: SELECT SUM(acctsessiontime) FROM tbl_acct where username = '%{User-Name}' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) -> SELECT SUM(acctsessiontime) FROM tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) sqlcounter_expand: '%{sql:SELECT SUM(acctsessiontime) FROM tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s'))}' [monthlycounter] sql_xlat [monthlycounter] expand: %{User-Name} -> suman [monthlycounter] sql_set_user escaped user --> 'suman' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] expand: SELECT SUM(acctsessiontime) FROM tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) -> SELECT SUM(acctsessiontime) FROM tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) 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 tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s'))} -> rlm_sqlcounter: No integer found in string "" ++[monthlycounter] returns noop rlm_sqlcounter: Entering module authorize code rlm_sqlcounter: Could not find Check item value pair ++[noresetcounter] returns noop rlm_checkval: Item Name: Calling-Station-Id, Value: 001122334455 rlm_checkval: Value Name: Calling-Station-Id, Value: 001122334455 ++[checkval] returns ok [expiration] Checking Expiration time: '13 Mar 2012 21:37:23' ++[expiration] returns ok ++[logintime] returns noop ++[pap] returns updated Found Auth-Type = PAP +- entering group PAP {...} [pap] login attempt with password "duman12" [pap] Using CRYPT encryption. [pap] User authenticated successfully ++[pap] returns ok +- 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 tbl_postauth (username, pass, reply, authdate) VALUES ( '%{User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') -> INSERT INTO tbl_postauth (username, pass, reply, authdate) VALUES ( 'suman', 'duman12', 'Access-Accept', '2011-03-15 15:06:37') rlm_sql (sql) in sql_postauth: query is INSERT INTO tbl_postauth (username, pass, reply, authdate) VALUES ( 'suman', 'duman12', 'Access-Accept', '2011-03-15 15:06:37') 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 9 to 122.175.85.117 port 19549 Session-Timeout = 31473046 Finished request 0. Going to the next request Waking up in 4.9 seconds. Cleaning up request 0 ID 9 with timestamp +2 Ready to process requests. I don't know... try using 3 or 4 '%' in the string, instead of just 2.
Suman Dash wrote:
mysql> select * from tbl_check; +----+----------+---------------------+----+----------------------+ | id | username | attribute | op | value | +----+----------+---------------------+----+----------------------+ | 17 | suman | Crypt-Password | := | TeatgXaDQelbQ | | 18 | suman | Calling-Station-Id | := | 001122334455 | | 25 | suman | Expiration | := | 13 Mar 2012 21:37:23 | | 22 | suman | Activation | := | 13 Mar 2011 21:37:23 | | 24 | suman | Max-Monthly-Session | := | 30 | +----+----------+---------------------+----+----------------------+ 5 rows in set (0.00 sec)
mysql> SELECT SUM(acctsessiontime) FROM tbl_acct where username = 'suman' AND acctstarttime BETWEEN \ -> (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%d %M %Y %H:%i:%s')) \ -> AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%d %M %Y %H:%i:%s')); +----------------------+ | SUM(acctsessiontime) | +----------------------+ | 200 | +----------------------+ 1 row in set (0.00 sec)
mysql>
Counter.SQL
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 tbl_acct where \ username = '%{%k}' AND acctstarttime BETWEEN \ (SELECT STR_TO_DATE((SELECT value FROM tbl_check \ WHERE username = '%{%k}' AND attribute = 'Activation'), '%%d %%M %%Y %%H:%%i:%%s')) \ AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{%k}' \ AND attribute = 'Expiration'), '%%d %%M %%Y %%H:%%i:%%s'))"
DEBUG
Listening on authentication address * port 1812 Listening on accounting address * port 1813 Listening on proxy address * port 1814 Ready to process requests. rad_recv: Access-Request packet from host 122.175.85.117 port 19169, id=6, length=59 User-Name = "suman" User-Password = "duman12" Calling-Station-Id = "001122334455" +- entering group authorize {...} ++[preprocess] returns ok ++[chap] returns noop ++[mschap] 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 ++[unix] returns notfound ++[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 tbl_check WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM tbl_check WHERE username = 'suman' ORDER BY id [sql] User found in radcheck table [sql] expand: SELECT id, username, attribute, value, op FROM tbl_reply WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM tbl_reply WHERE username = 'suman' ORDER BY id [sql] expand: SELECT groupname FROM tbl_usergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority -> SELECT groupname FROM tbl_usergroup WHERE username = 'suman' ORDER BY priority [sql] expand: SELECT id, groupname, attribute, Value, op FROM tbl_groupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname, attribute, Value, op FROM tbl_groupcheck WHERE groupname = 'Biz1Mbps-UL' ORDER BY id [sql] User found in group Biz1Mbps-UL [sql] expand: SELECT id, groupname, attribute, value, op FROM tbl_groupreply WHERE groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname, attribute, value, op FROM tbl_groupreply WHERE groupname = 'Biz1Mbps-UL' ORDER BY id 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 ++[dailycounter] returns noop rlm_sqlcounter: Entering module authorize code sqlcounter_expand: 'SELECT SUM(acctsessiontime) FROM tbl_acct where username = '%{User-Name}' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}' AND attribute = 'Activation'), '%0d %0M %0Y %0H:%0i:%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}' AND attribute = 'Expiration'), '%0d %0M %0Y %0H:%0i:%0s'))' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] expand: SELECT SUM(acctsessiontime) FROM tbl_acct where username = '%{User-Name}' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}' AND attribute = 'Activation'), '%0d %0M %0Y %0H:%0i:%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}' AND attribute = 'Expiration'), '%0d %0M %0Y %0H:%0i:%0s')) -> SELECT SUM(acctsessiontime) FROM tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0d %0M %0Y %0H:%0i:%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0d %0M %0Y %0H:%0i:%0s')) sqlcounter_expand: '%{sql:SELECT SUM(acctsessiontime) FROM tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0d %0M %0Y %0H:%0i:%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0d %0M %0Y %0H:%0i:%0s'))}' [monthlycounter] sql_xlat [monthlycounter] expand: %{User-Name} -> suman [monthlycounter] sql_set_user escaped user --> 'suman' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] expand: SELECT SUM(acctsessiontime) FROM tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0d %0M %0Y %0H:%0i:%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0d %0M %0Y %0H:%0i:%0s')) -> SELECT SUM(acctsessiontime) FROM tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0d %0M %0Y %0H:%0i:%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0d %0M %0Y %0H:%0i:%0s')) 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 tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0d %0M %0Y %0H:%0i:%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0d %0M %0Y %0H:%0i:%0s'))} -> rlm_sqlcounter: No integer found in string "" ++[monthlycounter] returns noop rlm_sqlcounter: Entering module authorize code rlm_sqlcounter: Could not find Check item value pair ++[noresetcounter] returns noop rlm_checkval: Item Name: Calling-Station-Id, Value: 001122334455 rlm_checkval: Value Name: Calling-Station-Id, Value: 001122334455 ++[checkval] returns ok [expiration] Checking Expiration time: '13 Mar 2012 21:37:23' ++[expiration] returns ok ++[logintime] returns noop ++[pap] returns updated Found Auth-Type = PAP +- entering group PAP {...} [pap] login attempt with password "duman12" [pap] Using CRYPT encryption. [pap] User authenticated successfully ++[pap] returns ok +- 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 tbl_postauth (username, pass, reply, authdate) VALUES ( '%{User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') -> INSERT INTO tbl_postauth (username, pass, reply, authdate) VALUES ( 'suman', 'duman12', 'Access-Accept', '2011-03-15 14:36:34') rlm_sql (sql) in sql_postauth: query is INSERT INTO tbl_postauth (username, pass, reply, authdate) VALUES ( 'suman', 'duman12', 'Access-Accept', '2011-03-15 14:36:34') 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 6 to 122.175.85.117 port 19169 Session-Timeout = 31474849 Finished request 0. Going to the next request Waking up in 4.9 seconds. Cleaning up request 0 ID 6 with timestamp +3 Ready to process requests.
**********************************************************
When the Counter Reset Period is monthly , %%' becomes '%1298917800
When the Counter Reset Period is Never , New Problem Arises i.e %0d %0M %0Y %0H:%0i:%0s
Please let me know if you need any more debugs .
Best Regards Suman
On 3/15/2011 2:06 PM, Alan DeKok wrote:
Suman Dash wrote:
I have tried almost all sql escape but looks like none are working or maybe i am missing something. I am stuck in this issue for more than 3 days and now i don't have any clue due to which i am trying to reach for help on the mailing list. Hmm... the issue seems to be that the sqlcounter module does it's own string expansion, and gets it *horribly* wrong.
As for why '%%' becomes '%1298917800', I have no idea. Posting *more* debug output might help. What you did post was the final result of the expansion, and didn't include *how* that expansion came about.
Alan DeKok.
__________ Information from ESET NOD32 Antivirus, version of virus signature database 5924 (20110303) __________
The message was checked by ESET NOD32 Antivirus.
__________ Information from ESET NOD32 Antivirus, version of virus signature database 5924 (20110303) __________
The message was checked by ESET NOD32 Antivirus.
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.
Dear Alan, I have not removed any debug messages. I will try to put everything once again . I was not aware that i sent you a mail. I am having a nightmare and accidently i clicked Send All instead of selecting the mailing list. 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 tbl_acct where \ username = '%{%k}' AND acctstarttime BETWEEN \ (SELECT STR_TO_DATE((SELECT value FROM tbl_check \ WHERE username = '%{%k}' AND attribute = 'Activation'), '%%%%d %%%%M %%%%Y %%%%H:%%%%i:%%%%s')) \ AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{%k}' \ AND attribute = 'Expiration'), '%%%%d %%%%M %%%%Y %%%%H:%%%%i:%%%%s'))" } DEBUG Listening on authentication address * port 1812 Listening on accounting address * port 1813 Listening on proxy address * port 1814 Ready to process requests. rad_recv: Access-Request packet from host 122.175.85.117 port 21658, id=10, length=59 User-Name = "suman" User-Password = "duman12" Calling-Station-Id = "001122334455" +- entering group authorize {...} ++[preprocess] returns ok ++[chap] returns noop ++[mschap] 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 ++[unix] returns notfound ++[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 tbl_check WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM tbl_check WHERE username = 'suman' ORDER BY id [sql] User found in radcheck table [sql] expand: SELECT id, username, attribute, value, op FROM tbl_reply WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM tbl_reply WHERE username = 'suman' ORDER BY id [sql] expand: SELECT groupname FROM tbl_usergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority -> SELECT groupname FROM tbl_usergroup WHERE username = 'suman' ORDER BY priority [sql] expand: SELECT id, groupname, attribute, Value, op FROM tbl_groupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname, attribute, Value, op FROM tbl_groupcheck WHERE groupname = 'Biz1Mbps-UL' ORDER BY id [sql] User found in group Biz1Mbps-UL [sql] expand: SELECT id, groupname, attribute, value, op FROM tbl_groupreply WHERE groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname, attribute, value, op FROM tbl_groupreply WHERE groupname = 'Biz1Mbps-UL' ORDER BY id 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 ++[dailycounter] returns noop rlm_sqlcounter: Entering module authorize code sqlcounter_expand: 'SELECT SUM(acctsessiontime) FROM tbl_acct where username = '%{User-Name}' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s'))' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] expand: SELECT SUM(acctsessiontime) FROM tbl_acct where username = '%{User-Name}' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) -> SELECT SUM(acctsessiontime) FROM tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) sqlcounter_expand: '%{sql:SELECT SUM(acctsessiontime) FROM tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s'))}' [monthlycounter] sql_xlat [monthlycounter] expand: %{User-Name} -> suman [monthlycounter] sql_set_user escaped user --> 'suman' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] expand: SELECT SUM(acctsessiontime) FROM tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) -> SELECT SUM(acctsessiontime) FROM tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) 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 tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s'))} -> rlm_sqlcounter: No integer found in string "" ++[monthlycounter] returns noop rlm_sqlcounter: Entering module authorize code rlm_sqlcounter: Could not find Check item value pair ++[noresetcounter] returns noop rlm_checkval: Item Name: Calling-Station-Id, Value: 001122334455 rlm_checkval: Value Name: Calling-Station-Id, Value: 001122334455 ++[checkval] returns ok [expiration] Checking Expiration time: '13 Mar 2012 21:37:23' ++[expiration] returns ok ++[logintime] returns noop ++[pap] returns updated Found Auth-Type = PAP +- entering group PAP {...} [pap] login attempt with password "duman12" [pap] Using CRYPT encryption. [pap] User authenticated successfully ++[pap] returns ok +- 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 tbl_postauth (username, pass, reply, authdate) VALUES ( '%{User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') -> INSERT INTO tbl_postauth (username, pass, reply, authdate) VALUES ( 'suman', 'duman12', 'Access-Accept', '2011-03-15 15:57:53') rlm_sql (sql) in sql_postauth: query is INSERT INTO tbl_postauth (username, pass, reply, authdate) VALUES ( 'suman', 'duman12', 'Access-Accept', '2011-03-15 15:57:53') 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 10 to 122.175.85.117 port 21658 Session-Timeout = 31469970 Finished request 0. Going to the next request Waking up in 4.9 seconds. Cleaning up request 0 ID 10 with timestamp +3 Ready to process requests. ************************************************************************************ The above is the complete log , nothing removed . As you can see %0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s which is nothing but %%%%d %%%M %%%%Y %%%%H ............ I am using freeradius 2.1.8 and now i am in a process of checking the same in the latest release. As for the read receipt is concerned then i am sorry in case i have annoyed you in any way. Best Regards Suman Dash 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.
Hi Alan, Did you managed to look into the issue ? or maybe any hints on how to use DATETIME in Expiration instead of String ? Regads Suman On 3/15/2011 4:04 PM, Suman Dash wrote:
Dear Alan,
I have not removed any debug messages. I will try to put everything once again . I was not aware that i sent you a mail. I am having a nightmare and accidently i clicked Send All instead of selecting the mailing list.
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 tbl_acct where \ username = '%{%k}' AND acctstarttime BETWEEN \ (SELECT STR_TO_DATE((SELECT value FROM tbl_check \ WHERE username = '%{%k}' AND attribute = 'Activation'), '%%%%d %%%%M %%%%Y %%%%H:%%%%i:%%%%s')) \ AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{%k}' \ AND attribute = 'Expiration'), '%%%%d %%%%M %%%%Y %%%%H:%%%%i:%%%%s'))" }
DEBUG
Listening on authentication address * port 1812 Listening on accounting address * port 1813 Listening on proxy address * port 1814 Ready to process requests. rad_recv: Access-Request packet from host 122.175.85.117 port 21658, id=10, length=59 User-Name = "suman" User-Password = "duman12" Calling-Station-Id = "001122334455" +- entering group authorize {...} ++[preprocess] returns ok ++[chap] returns noop ++[mschap] 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 ++[unix] returns notfound ++[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 tbl_check WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM tbl_check WHERE username = 'suman' ORDER BY id [sql] User found in radcheck table [sql] expand: SELECT id, username, attribute, value, op FROM tbl_reply WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM tbl_reply WHERE username = 'suman' ORDER BY id [sql] expand: SELECT groupname FROM tbl_usergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority -> SELECT groupname FROM tbl_usergroup WHERE username = 'suman' ORDER BY priority [sql] expand: SELECT id, groupname, attribute, Value, op FROM tbl_groupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname, attribute, Value, op FROM tbl_groupcheck WHERE groupname = 'Biz1Mbps-UL' ORDER BY id [sql] User found in group Biz1Mbps-UL [sql] expand: SELECT id, groupname, attribute, value, op FROM tbl_groupreply WHERE groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname, attribute, value, op FROM tbl_groupreply WHERE groupname = 'Biz1Mbps-UL' ORDER BY id 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 ++[dailycounter] returns noop rlm_sqlcounter: Entering module authorize code sqlcounter_expand: 'SELECT SUM(acctsessiontime) FROM tbl_acct where username = '%{User-Name}' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s'))' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] expand: SELECT SUM(acctsessiontime) FROM tbl_acct where username = '%{User-Name}' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) -> SELECT SUM(acctsessiontime) FROM tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) sqlcounter_expand: '%{sql:SELECT SUM(acctsessiontime) FROM tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s'))}' [monthlycounter] sql_xlat [monthlycounter] expand: %{User-Name} -> suman [monthlycounter] sql_set_user escaped user --> 'suman' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt' [monthlycounter] expand: SELECT SUM(acctsessiontime) FROM tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) -> SELECT SUM(acctsessiontime) FROM tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) 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 tbl_acct where username = 'suman' AND acctstarttime BETWEEN (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s'))} -> rlm_sqlcounter: No integer found in string "" ++[monthlycounter] returns noop rlm_sqlcounter: Entering module authorize code rlm_sqlcounter: Could not find Check item value pair ++[noresetcounter] returns noop rlm_checkval: Item Name: Calling-Station-Id, Value: 001122334455 rlm_checkval: Value Name: Calling-Station-Id, Value: 001122334455 ++[checkval] returns ok [expiration] Checking Expiration time: '13 Mar 2012 21:37:23' ++[expiration] returns ok ++[logintime] returns noop ++[pap] returns updated Found Auth-Type = PAP +- entering group PAP {...} [pap] login attempt with password "duman12" [pap] Using CRYPT encryption. [pap] User authenticated successfully ++[pap] returns ok +- 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 tbl_postauth (username, pass, reply, authdate) VALUES ( '%{User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') -> INSERT INTO tbl_postauth (username, pass, reply, authdate) VALUES ( 'suman', 'duman12', 'Access-Accept', '2011-03-15 15:57:53') rlm_sql (sql) in sql_postauth: query is INSERT INTO tbl_postauth (username, pass, reply, authdate) VALUES ( 'suman', 'duman12', 'Access-Accept', '2011-03-15 15:57:53') 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 10 to 122.175.85.117 port 21658 Session-Timeout = 31469970 Finished request 0. Going to the next request Waking up in 4.9 seconds. Cleaning up request 0 ID 10 with timestamp +3 Ready to process requests.
************************************************************************************
The above is the complete log , nothing removed . As you can see %0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s which is nothing but %%%%d %%%M %%%%Y %%%%H ............ I am using freeradius 2.1.8 and now i am in a process of checking the same in the latest release.
As for the read receipt is concerned then i am sorry in case i have annoyed you in any way.
Best Regards Suman Dash
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.
__________ Information from ESET NOD32 Antivirus, version of virus signature database 5924 (20110303) __________
The message was checked by ESET NOD32 Antivirus.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
__________ Information from ESET NOD32 Antivirus, version of virus signature database 5924 (20110303) __________
The message was checked by ESET NOD32 Antivirus.
Suman Dash wrote:
Hi Alan,
Did you managed to look into the issue ?
No.
or maybe any hints on how to use DATETIME in Expiration instead of String ?
Honestly, in 2.1.10, you can just write SELECT statements directly in "unlang". update reply { Session-Timeout := "%{sql: SELECT ...}" } Couple that with a few other things, and you should be able to replace the sqlcounter module entirely. i.e. I don't use that module, and I know little or nothing about it. I have little time to do anything with it. Alan DeKok.
Much thanks Alan, That was some really good advice on how to make the thing work. So now i have to write unlang statement in preprocess so that it directly gives the Session-Timeout . Please correct me if i am wrong. Thanks Again On 3/16/2011 4:09 PM, Alan DeKok wrote:
Suman Dash wrote:
Hi Alan,
Did you managed to look into the issue ? No.
or maybe any hints on how to use DATETIME in Expiration instead of String ? Honestly, in 2.1.10, you can just write SELECT statements directly in "unlang".
update reply { Session-Timeout := "%{sql: SELECT ...}" }
Couple that with a few other things, and you should be able to replace the sqlcounter module entirely.
i.e. I don't use that module, and I know little or nothing about it. I have little time to do anything with it.
Alan DeKok.
__________ Information from ESET NOD32 Antivirus, version of virus signature database 5924 (20110303) __________
The message was checked by ESET NOD32 Antivirus.
I am looking forward for a short example on how to store a SQL query to a variable which can be used in next condition in UNLANG. I have no knowledge of unlang but i got a fair amount of idea with the condition checks , just need a little insight on the result stores . For Ex. result1 = {some sql query} result2 = {some sql query} update control Session-Timeout := Result1 - Result 2 Thanks in advance Suman On 3/16/2011 4:09 PM, Alan DeKok wrote:
Suman Dash wrote:
Hi Alan,
Did you managed to look into the issue ? No.
or maybe any hints on how to use DATETIME in Expiration instead of String ? Honestly, in 2.1.10, you can just write SELECT statements directly in "unlang".
update reply { Session-Timeout := "%{sql: SELECT ...}" }
Couple that with a few other things, and you should be able to replace the sqlcounter module entirely.
i.e. I don't use that module, and I know little or nothing about it. I have little time to do anything with it.
Alan DeKok.
__________ Information from ESET NOD32 Antivirus, version of virus signature database 5924 (20110303) __________
The message was checked by ESET NOD32 Antivirus.
On 16/03/11 12:44, Suman Dash wrote:
I am looking forward for a short example on how to store a SQL query to a variable which can be used in next condition in UNLANG.
I have no knowledge of unlang but i got a fair amount of idea with the condition checks , just need a little insight on the result stores .
FreeRadius comes with several "Tmp-X" variables defined (see dictionary.freeradius.internal) or you can define your own. e.g. authorize { ... update request { Tmp-Integer-0 := "%{sql: ...}" } update request { Tmp-Integer-1 := "%{sql: ...}" } update reply { Session-Timeout := "%{expr:%{Tmp-Integer-0} - %{Tmp-Integer-1}}" } ... } Or do the maths in the SQL query itself.
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.
Is it possible to change the date of Expiration to DATETIME rather than String ? Expiration Attribute is unable to parse a DATETIME but is able to parse a String 10 Jan 2011 << Is Parsed 2011-01-10 << Unable to Parse Reason is that i want to run a counter which will pick the Expiration and Activation date from radcheck table and run a SUM(acctsession) on radacct table within the specific DATETIME. This workaround is needed as we may have multiple users whose expiration time does not essentially *is* the end day of the month. For Ex. User A - Account Activation - 10 Mar 2011 Expiry 9 Apr 2011 For Ex. User B - Account Activation - 15 Mar 2011 Expiry 14 Apr 2011 A Monthly SQLCounter will use Last Reset Date as 1 Mar 2011 and Next Reset Date as 1 Apr 2011. So in the above case the acctsessiontime for User A and User B will be wrong. Please let me know if my understanding of the SQLCounter is correct or is it the other way around. Best Regards Suman
Is it possible to change the date of Expiration to DATETIME rather than String ? Expiration Attribute is unable to parse a DATETIME but is able to parse a String 10 Jan 2011 << Is Parsed 2011-01-10 << Unable to Parse Reason is that i want to run a counter which will pick the Expiration and Activation date from radcheck table and run a SUM(acctsession) on radacct table within the specific DATETIME. This workaround is needed as we may have multiple users whose expiration time does not essentially *is* the end day of the month. For Ex. User A - Account Activation - 10 Mar 2011 Expiry 9 Apr 2011 For Ex. User B - Account Activation - 15 Mar 2011 Expiry 14 Apr 2011 A Monthly SQLCounter will use Last Reset Date as 1 Mar 2011 and Next Reset Date as 1 Apr 2011. So in the above case the acctsessiontime for User A and User B will be wrong. Please let me know if my understanding of the SQLCounter is correct or is it the other way around. Best Regards Suman
participants (4)
-
Alan DeKok -
Phil Mayers -
Suman Dash -
suman＠clydontech.com