Fwd: rlm_sqlcounter always return 0
Mike Russell
sircoolhot at gmail.com
Mon Jan 7 16:45:50 CET 2013
Dear Alan D. & List Users,
I am reposting this message only because it bounced last due to size error.
I tried to reduce the size but less information to see. Apologies if it was
sent but i received bounce and no response from any member.
---------------: Orignal Message : -----------
I tried to search the mail archives for the similar topic but did not find
matched results. Then read wiki again to crosscheck that if i have done it
well before posting on the mailing list. I am sorry if similar post has
been answered before. I have problem with "rlm_sqlcounter" module. That is
"noresetcounter" it always return "0". But if i manually run the query to
gerp the users accounting data from MySQL DB, i get accounting results. it
seems that everything is in place but unable to know why sql counter still
returns zero. whereas dailycounter works fine without any problem.
My rlm_sqlnoresetcounter module:
sqlcounter noresetcounter {
driver = rlm_sqlcounter
counter-name = Max-All-Session-Time
check-name = Max-All-Session
sqlmod-inst = sql
key = User-Name
reset = never
query = "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct
WHERE UserName='${key}'"
}
To Prove the point i would also need to send radtest results for comparison:
rad_recv: Access-Request packet from host 127.0.0.1 port 47871, id=159,
length=84
User-Name = "KV60Rp2vtrEe5B"
User-Password = "yuxNtwKbSH"
NAS-IP-Address = 31.241.205.211
NAS-Port = 1812
Message-Authenticator = 0x2e53acc3936310719d3dc335a640afd4
# Executing section authorize from file /etc/raddb/sites-enabled/default
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
++[mschap] returns noop
++[digest] returns noop
[suffix] No '@' in User-Name = "KV60Rp2vtrEe5B", looking up realm NULL
[suffix] No such realm "NULL"
++[suffix] returns noop
[eap] No EAP-Message, not doing EAP
++[eap] returns noop
[sql] expand: %{User-Name} -> KV60Rp2vtrEe5B
[sql] sql_set_user escaped user --> 'KV60Rp2vtrEe5B'
rlm_sql (sql): Reserving sql socket id: 3
[sql] expand: SELECT id, username, attribute, value, op FROM
radcheck WHERE username = '%{SQL-User-Name}' ORDER BY
id -> SELECT id, username, attribute, value, op FROM radcheck
WHERE username = 'KV60Rp2vtrEe5B' ORDER BY id
[sql] User found in radcheck table
[sql] expand: SELECT id, username, attribute, value, op FROM
radreply WHERE username = '%{SQL-User-Name}' ORDER BY
id -> SELECT id, username, attribute, value, op FROM radreply
WHERE username = 'KV60Rp2vtrEe5B' ORDER BY id
[sql] expand: SELECT groupname FROM radusergroup
WHERE username = '%{SQL-User-Name}' ORDER BY priority -> SELECT
groupname FROM radusergroup WHERE username =
'KV60Rp2vtrEe5B' ORDER BY priority
[sql] expand: SELECT id, groupname, attribute, Value, op
FROM radgroupcheck WHERE groupname = '%{Sql-Group}'
ORDER BY id -> SELECT id, groupname, attribute, Value, op
FROM radgroupcheck WHERE groupname = 'One_Hour_Access'
ORDER BY id
[sql] User found in group One_Hour_Access
[sql] expand: SELECT id, groupname, attribute, value, op
FROM radgroupreply WHERE groupname = '%{Sql-Group}'
ORDER BY id -> SELECT id, groupname, attribute, value, op
FROM radgroupreply WHERE groupname = 'One_Hour_Access'
ORDER BY id
rlm_sql (sql): Released sql socket id: 3
++[sql] returns ok
++[pap] returns updated
rlm_counter: Entering module authorize code
rlm_counter: Could not find Check item value pair
++[daily] returns noop
rlm_sqlcounter: Entering module authorize code
sqlcounter_expand: 'SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct
WHERE UserName='User-Name''
[noresetcounter] expand: SELECT IFNULL(SUM(AcctSessionTime),0) FROM
radacct WHERE UserName='User-Name' -> SELECT IFNULL(SUM(AcctSessionTime),0)
FROM radacct WHERE UserName='User-Name'
*WARNING: Please replace '%S' with '${sqlmod-inst}'*
sqlcounter_expand: '%{sql:SELECT IFNULL(SUM(AcctSessionTime),0) FROM
radacct WHERE UserName='User-Name'}'
[noresetcounter] sql_xlat
[noresetcounter] expand: %{User-Name} -> KV60Rp2vtrEe5B
[noresetcounter] sql_set_user escaped user --> 'KV60Rp2vtrEe5B'
[noresetcounter] expand: SELECT IFNULL(SUM(AcctSessionTime),0) FROM
radacct WHERE UserName='User-Name' -> SELECT IFNULL(SUM(AcctSessionTime),0)
FROM radacct WHERE UserName='User-Name'
rlm_sql (sql): Reserving sql socket id: 2
[noresetcounter] sql_xlat finished
rlm_sql (sql): Released sql socket id: 2
[noresetcounter] expand: %{sql:SELECT IFNULL(SUM(AcctSessionTime),0)
FROM radacct WHERE UserName='User-Name'} -> 0
rlm_sqlcounter: Check item is greater than query result
rlm_sqlcounter: Authorized user KV60Rp2vtrEe5B, check_item=3600, counter=0
<------- This Value
rlm_sqlcounter: Sent Reply-Item for user KV60Rp2vtrEe5B,
Type=Session-Timeout, value=3600
++[noresetcounter] returns ok
[expiration] Checking Expiration time: '10 Jan 2013'
++[expiration] returns ok
++[logintime] returns noop
Found Auth-Type = PAP
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!! Replacing User-Password in config items with Cleartext-Password.
!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!! Please update your configuration so that the "known good"
!!!
!!! clear text password is in Cleartext-Password, and not in User-Password.
!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
# Executing group from file /etc/raddb/sites-enabled/default
+- entering group PAP {...}
[pap] login attempt with password "yuxNtwKbSH"
[pap] Using clear text password "yuxNtwKbSH"
[pap] User authenticated successfully
++[pap] returns ok
# Executing section session from file /etc/raddb/sites-enabled/default
+- entering group session {...}
[radutmp] expand: /var/log/radius/radutmp -> /var/log/radius/radutmp
[radutmp] expand: %{User-Name} -> KV60Rp2vtrEe5B
++[radutmp] returns ok
# Executing section post-auth from file /etc/raddb/sites-enabled/default
+- entering group post-auth {...}
[sql] expand: %{User-Name} -> KV60Rp2vtrEe5B
[sql] sql_set_user escaped user --> 'KV60Rp2vtrEe5B'
[sql] expand: %{User-Password} -> yuxNtwKbSH
[sql] expand: INSERT INTO radpostauth
(username, pass, reply, authdate) VALUES (
'%{User-Name}',
'%{%{User-Password}:-%{Chap-Password}}',
'%{reply:Packet-Type}', '%S') -> INSERT INTO radpostauth
(username, pass, reply, authdate) VALUES
( 'KV60Rp2vtrEe5B',
'yuxNtwKbSH', 'Access-Accept', '2013-01-07
08:41:59')
rlm_sql (sql) in sql_postauth: query is INSERT INTO radpostauth
(username, pass, reply, authdate)
VALUES ( 'KV60Rp2vtrEe5B',
'yuxNtwKbSH', 'Access-Accept', '2013-01-07
08:41:59')
rlm_sql (sql): Reserving sql socket id: 1
rlm_sql (sql): Released sql socket id: 1
++[sql] returns ok
++[exec] returns noop
Sending Access-Accept of id 159 to 127.0.0.1 port 47871
Session-Timeout := 3600
Idle-Timeout := 60
Acct-Interim-Interval := 120
Finished request 0.
Going to the next request
Waking up in 4.9 seconds.
Cleaning up request 0 ID 159 with timestamp +311
Ready to process requests.
Now lets grep the accounting value from database for the same user
(returned "0" earlier)
This user has "Max-All-Session" attribute applied and exist in
"radgroupreply" table.
mysql> SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE
UserName='KV60Rp2vtrEe5B';
+--------------------------------+
| IFNULL(SUM(AcctSessionTime),0) |
+--------------------------------+
| 380802 |
+--------------------------------+
1 row in set (0.00 sec)
Why rlm_counter returns the zero value.
Thanks / Russell M.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20130107/c912f008/attachment-0001.html>
More information about the Freeradius-Users
mailing list