<div dir="ltr"><br><div class="gmail_quote"><br><div dir="ltr"><font face="verdana, sans-serif"><span style="font-size:12.727272033691406px">Dear Alan D. & List Users, </span><br></font><div><span style="font-size:12.727272033691406px"><font face="verdana, sans-serif"><br>
</font></span></div><div style><font face="verdana, sans-serif">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.</font></div>
<div style><font face="verdana, sans-serif"><br></font></div><div style><font face="verdana, sans-serif">---------------: Orignal Message : -----------</font></div><div style><font face="verdana, sans-serif"> </font></div>
<div><font face="verdana, sans-serif">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. </font></div>
<div><span style="font-size:12.727272033691406px"><font face="verdana, sans-serif"><br></font></span></div><div><font face="verdana, sans-serif">My rlm_sqlnoresetcounter module: </font></div><div><font face="verdana, sans-serif"><br>
</font></div><div><font face="verdana, sans-serif">sqlcounter noresetcounter {</font></div><div><font face="verdana, sans-serif"> driver = rlm_sqlcounter</font></div><div><font face="verdana, sans-serif"> counter-name = Max-All-Session-Time</font></div>
<div><font face="verdana, sans-serif"> check-name = Max-All-Session</font></div><div><font face="verdana, sans-serif"> sqlmod-inst = sql</font></div><div><font face="verdana, sans-serif"> key = User-Name</font></div>
<div><font face="verdana, sans-serif"> reset = never</font></div><div><font face="verdana, sans-serif"> query = "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='${key}'"</font></div>
<div><font face="verdana, sans-serif">}</font></div><div><div><font face="verdana, sans-serif"><br></font></div>
<div><font face="verdana, sans-serif">To Prove the point i would also need to send radtest results for comparison:</font></div><div><font face="verdana, sans-serif"><br></font></div><div><div><font face="verdana, sans-serif">rad_recv: Access-Request packet from host 127.0.0.1 port 47871, id=159, length=84</font></div>
<div><font face="verdana, sans-serif"> User-Name = "KV60Rp2vtrEe5B"</font></div><div><font face="verdana, sans-serif"> User-Password = "yuxNtwKbSH"</font></div><div><font face="verdana, sans-serif"> NAS-IP-Address = 31.241.205.211</font></div>
<div><font face="verdana, sans-serif"> NAS-Port = 1812</font></div><div><font face="verdana, sans-serif"> Message-Authenticator = 0x2e53acc3936310719d3dc335a640afd4</font></div><div><font face="verdana, sans-serif"># Executing section authorize from file /etc/raddb/sites-enabled/default</font></div>
<div><font face="verdana, sans-serif">+- entering group authorize {...}</font></div><div><font face="verdana, sans-serif">++[preprocess] returns ok</font></div><div><font face="verdana, sans-serif">++[chap] returns noop</font></div>
<div><font face="verdana, sans-serif">++[mschap] returns noop</font></div><div><font face="verdana, sans-serif">++[digest] returns noop</font></div><div><font face="verdana, sans-serif">[suffix] No '@' in User-Name = "KV60Rp2vtrEe5B", looking up realm NULL</font></div>
<div><font face="verdana, sans-serif">[suffix] No such realm "NULL"</font></div><div><font face="verdana, sans-serif">++[suffix] returns noop</font></div><div><font face="verdana, sans-serif">[eap] No EAP-Message, not doing EAP</font></div>
<div><font face="verdana, sans-serif">++[eap] returns noop</font></div><div><font face="verdana, sans-serif">[sql] expand: %{User-Name} -> KV60Rp2vtrEe5B</font></div><div><font face="verdana, sans-serif">[sql] sql_set_user escaped user --> 'KV60Rp2vtrEe5B'</font></div>
<div><font face="verdana, sans-serif">rlm_sql (sql): Reserving sql socket id: 3</font></div><div><font face="verdana, sans-serif">[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</font></div>
<div><font face="verdana, sans-serif">[sql] User found in radcheck table</font></div><div><font face="verdana, sans-serif">[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</font></div>
<div><font face="verdana, sans-serif">[sql] expand: SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority -> SELECT groupname FROM radusergroup WHERE username = 'KV60Rp2vtrEe5B' ORDER BY priority</font></div>
<div><font face="verdana, sans-serif">[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</font></div>
<div><font face="verdana, sans-serif">[sql] User found in group One_Hour_Access</font></div><div><font face="verdana, sans-serif">[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</font></div>
<div><font face="verdana, sans-serif">rlm_sql (sql): Released sql socket id: 3</font></div><div><font face="verdana, sans-serif">++[sql] returns ok</font></div><div><font face="verdana, sans-serif">++[pap] returns updated</font></div>
<div><font face="verdana, sans-serif">rlm_counter: Entering module authorize code</font></div><div><font face="verdana, sans-serif">rlm_counter: Could not find Check item value pair</font></div><div><font face="verdana, sans-serif">++[daily] returns noop</font></div>
<div><font face="verdana, sans-serif">rlm_sqlcounter: Entering module authorize code</font></div><div><font face="verdana, sans-serif">sqlcounter_expand: 'SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='User-Name''</font></div>
<div><font face="verdana, sans-serif">[noresetcounter] expand: SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='User-Name' -> SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='User-Name'</font></div>
<div><font face="verdana, sans-serif"><br></font></div><div><font face="verdana, sans-serif"><br></font></div><div><b><font color="#0000ff" face="verdana, sans-serif">WARNING: Please replace '%S' with '${sqlmod-inst}'</font></b></div>
<div><font face="verdana, sans-serif"><br></font></div><div><font face="verdana, sans-serif"><br></font></div><div><font face="verdana, sans-serif">sqlcounter_expand: '%{sql:SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='User-Name'}'</font></div>
<div><font face="verdana, sans-serif">[noresetcounter] sql_xlat</font></div><div><font face="verdana, sans-serif">[noresetcounter] expand: %{User-Name} -> KV60Rp2vtrEe5B</font></div><div><font face="verdana, sans-serif">[noresetcounter] sql_set_user escaped user --> 'KV60Rp2vtrEe5B'</font></div>
<div><font face="verdana, sans-serif">[noresetcounter] expand: SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='User-Name' -> SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='User-Name'</font></div>
<div><font face="verdana, sans-serif">rlm_sql (sql): Reserving sql socket id: 2</font></div><div><font face="verdana, sans-serif">[noresetcounter] sql_xlat finished</font></div><div><font face="verdana, sans-serif">rlm_sql (sql): Released sql socket id: 2</font></div>
<div><font face="verdana, sans-serif">[noresetcounter] expand: %{sql:SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='User-Name'} -> 0</font></div><div><font face="verdana, sans-serif">rlm_sqlcounter: Check item is greater than query result<br>
</font></div><div><font face="verdana, sans-serif"><br></font></div><div><font face="verdana, sans-serif"><br></font></div><div><font face="verdana, sans-serif"><br></font></div><div><font face="verdana, sans-serif"><br>
</font></div>
<div><font face="verdana, sans-serif"><span style="color:rgb(255,0,0)">rlm_sqlcounter: Authorized user KV60Rp2vtrEe5B, </span><span style="color:rgb(255,0,0)">check_item=3600, counter=0 <------- This Value</span><br>
</font></div><div><font face="verdana, sans-serif"><br></font></div><div><font face="verdana, sans-serif"><br></font></div><div><font face="verdana, sans-serif"><br></font></div><div><font face="verdana, sans-serif">rlm_sqlcounter: Sent Reply-Item for user KV60Rp2vtrEe5B, Type=Session-Timeout, value=3600<br>
</font></div><div><font face="verdana, sans-serif">++[noresetcounter] returns ok</font></div><div><span style="font-family:verdana,sans-serif">[expiration] Checking Expiration time: '10 Jan 2013'</span></div><div>
<font face="verdana, sans-serif">++[expiration] returns ok</font></div><div><font face="verdana, sans-serif">++[logintime] returns noop</font></div><div><font face="verdana, sans-serif">Found Auth-Type = PAP</font></div>
<div>
<font face="verdana, sans-serif">!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!</font></div><div><font face="verdana, sans-serif">!!! Replacing User-Password in config items with Cleartext-Password. !!!</font></div>
<div><font face="verdana, sans-serif">!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!</font></div><div><font face="verdana, sans-serif">!!! Please update your configuration so that the "known good" !!!</font></div>
<div><font face="verdana, sans-serif">!!! clear text password is in Cleartext-Password, and not in User-Password. !!!</font></div><div><font face="verdana, sans-serif">!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!</font></div>
<div><font face="verdana, sans-serif"># Executing group from file /etc/raddb/sites-enabled/default</font></div><div><font face="verdana, sans-serif">+- entering group PAP {...}</font></div><div><font face="verdana, sans-serif">[pap] login attempt with password "yuxNtwKbSH"</font></div>
<div><font face="verdana, sans-serif">[pap] Using clear text password "yuxNtwKbSH"</font></div><div><font face="verdana, sans-serif">[pap] User authenticated successfully</font></div><div><font face="verdana, sans-serif">++[pap] returns ok</font></div>
<div><font face="verdana, sans-serif"># Executing section session from file /etc/raddb/sites-enabled/default</font></div><div><font face="verdana, sans-serif">+- entering group session {...}</font></div><div><font face="verdana, sans-serif">[radutmp] expand: /var/log/radius/radutmp -> /var/log/radius/radutmp</font></div>
<div><font face="verdana, sans-serif">[radutmp] expand: %{User-Name} -> KV60Rp2vtrEe5B</font></div><div><font face="verdana, sans-serif">++[radutmp] returns ok</font></div><div><font face="verdana, sans-serif"># Executing section post-auth from file /etc/raddb/sites-enabled/default</font></div>
<div><font face="verdana, sans-serif">+- entering group post-auth {...}</font></div><div><font face="verdana, sans-serif">[sql] expand: %{User-Name} -> KV60Rp2vtrEe5B</font></div><div><font face="verdana, sans-serif">[sql] sql_set_user escaped user --> 'KV60Rp2vtrEe5B'</font></div>
<div><font face="verdana, sans-serif">[sql] expand: %{User-Password} -> yuxNtwKbSH</font></div><div><font face="verdana, sans-serif">[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')</font></div>
<div><font face="verdana, sans-serif">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')</font></div>
<div><font face="verdana, sans-serif">rlm_sql (sql): Reserving sql socket id: 1</font></div><div><font face="verdana, sans-serif">rlm_sql (sql): Released sql socket id: 1</font></div><div><font face="verdana, sans-serif">++[sql] returns ok</font></div>
<div><font face="verdana, sans-serif">++[exec] returns noop</font></div><div><font face="verdana, sans-serif">Sending Access-Accept of id 159 to 127.0.0.1 port 47871</font></div><div><font face="verdana, sans-serif"> Session-Timeout := 3600</font></div>
<div><font face="verdana, sans-serif"> Idle-Timeout := 60</font></div><div><font face="verdana, sans-serif"> Acct-Interim-Interval := 120</font></div><div><font face="verdana, sans-serif">Finished request 0.</font></div>
<div><font face="verdana, sans-serif">Going to the next request</font></div><div><font face="verdana, sans-serif">Waking up in 4.9 seconds.</font></div><div><font face="verdana, sans-serif">Cleaning up request 0 ID 159 with timestamp +311</font></div>
<div><font face="verdana, sans-serif">Ready to process requests.</font></div><div><font face="verdana, sans-serif"><br></font></div></div><div><font face="verdana, sans-serif"><br></font></div><div><font face="verdana, sans-serif"><br>
</font></div><div><font face="verdana, sans-serif">Now lets grep the accounting value from database for the same user (returned "0" earlier)</font></div><div>This user has "<font face="verdana, sans-serif">Max-All-Session" attribute applied and exist in "radgroupreply" table. </font><font face="verdana, sans-serif"><br>
</font></div><div><font face="verdana, sans-serif"><br></font></div><div><div><font face="verdana, sans-serif">mysql> SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='KV60Rp2vtrEe5B';</font></div>
<div><font face="verdana, sans-serif">+--------------------------------+</font></div><div><font face="verdana, sans-serif">| IFNULL(SUM(AcctSessionTime),0) |</font></div><div><font face="verdana, sans-serif">+--------------------------------+</font></div>
<div><font face="verdana, sans-serif">| 380802 |</font></div><div><font face="verdana, sans-serif">+--------------------------------+</font></div><div><font face="verdana, sans-serif">1 row in set (0.00 sec)</font></div>
</div><div><font face="verdana, sans-serif"><br></font></div><div style>Why rlm_counter returns the zero value. </div><div style><br></div><div style><br></div><div><font face="verdana, sans-serif">Thanks / Russell M.</font></div>
<div><font face="verdana, sans-serif"><br></font></div><div><br>
</div></div></div>
</div><br></div>