Query help
Jonathan Bastin
jonathan.bastin at peerpointinternet.co.uk
Wed Oct 10 16:25:32 CEST 2012
Thank you so much for the pointer. I am with you I couldn't understand the
last debug. Here is the new one.
rad_recv: Access-Request packet from host 193.000.221.000 port 1645, id=19,
length=141
Framed-Protocol = PPP
User-Name = "02080000000 at peerpointinternet.co.uk"
CHAP-Password = 0x048bf9799185d69af262db5d5c0e4c9ba2
Connect-Info = "11066368/1094656"
NAS-Port-Type = Virtual
NAS-Port = 903
NAS-Port-Id = "Uniq-Sess-ID903"
Service-Type = Framed-User
NAS-IP-Address = 193.000.221.000
# Executing section authorize from file
/etc/freeradius/sites-enabled/default
+- entering group authorize {...}
++[preprocess] returns ok
[chap] Setting 'Auth-Type := CHAP'
++[chap] returns ok
++[mschap] returns noop
[files] users: Matched entry DEFAULT at line 172
++[files] returns ok
[sql] expand: %{User-Name} -> 02080000000 at peerpointinternet.co.uk
[sql] sql_set_user escaped user --> '02080000000 at peerpointinternet.co.uk'
rlm_sql (sql): Reserving sql socket id: 2
[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 = '02080000000 at peerpointinternet.co.uk' 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 = '02080000000 at peerpointinternet.co.uk' ORDER BY id
[sql] expand: SELECT groupname FROM radusergroup WHERE
username = '%{SQL-User-Name}' ORDER BY priority -> SELECT
groupname FROM radusergroup WHERE username =
'02080000000 at peerpointinternet.co.uk' 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 = 'Serg_100GB' ORDER
BY id
[sql] User found in group Serg_100GB
[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 = 'Serg_100GB' ORDER
BY id
rlm_sql (sql): Released sql socket id: 2
++[sql] returns ok
sql_xlat
expand: %{User-Name} -> 02080000000 at peerpointinternet.co.uk
sql_set_user escaped user --> '02080000000 at peerpointinternet.co.uk'
expand: SELECT radgroupcheck.value FROM radusergroup Inner Join
radgroupcheck ON radusergroup.groupname = radgroupcheck.groupname WHERE
radusergroup.username = '%{User-Name}' AND radgroupcheck.attribute =
'CS-Total-Octets-Monthly'; -> SELECT radgroupcheck.value FROM radusergroup
Inner Join radgroupcheck ON radusergroup.groupname = radgroupcheck.groupname
WHERE radusergroup.username = '02080000000 at peerpointinternet.co.uk' AND
radgroupcheck.attribute = 'CS-Total-Octets-Monthly';
rlm_sql (sql): Reserving sql socket id: 1
sql_xlat finished
rlm_sql (sql): Released sql socket id: 1
expand: %{sql: SELECT radgroupcheck.value FROM radusergroup Inner
Join radgroupcheck ON radusergroup.groupname = radgroupcheck.groupname WHERE
radusergroup.username = '%{User-Name}' AND radgroupcheck.attribute =
'CS-Total-Octets-Monthly';} -> 107375000000
sql_xlat
expand: %{User-Name} -> 02080000000 at peerpointinternet.co.uk
sql_set_user escaped user --> '02080000000 at peerpointinternet.co.uk'
expand: SELECT SUM( AcctInputOctets + AcctOutputOctets) FROM radacct
WHERE UserName='%{User-Name}' AND AcctStartTime >
(DATE_SUB(CURDATE(),INTERVAL DAYOFMONTH(CURDATE())DAY)); -> SELECT SUM(
AcctInputOctets + AcctOutputOctets) FROM radacct WHERE
UserName='02080000000 at peerpointinternet.co.uk' AND AcctStartTime >
(DATE_SUB(CURDATE(),INTERVAL DAYOFMONTH(CURDATE())DAY));
rlm_sql (sql): Reserving sql socket id: 0
sql_xlat finished
rlm_sql (sql): Released sql socket id: 0
expand: %{sql:SELECT SUM( AcctInputOctets + AcctOutputOctets) FROM
radacct WHERE UserName='%{User-Name}' AND AcctStartTime >
(DATE_SUB(CURDATE(),INTERVAL DAYOFMONTH(CURDATE())DAY));} -> 64695817844
++[control] returns ok
++? if (control:Tmp-Integer-0 < "%{control:Tmp-Integer-1}")
expand: %{control:Tmp-Integer-1} -> 271308404
? Evaluating (control:Tmp-Integer-0 < "%{control:Tmp-Integer-1}") -> TRUE
++? if (control:Tmp-Integer-0 < "%{control:Tmp-Integer-1}") -> TRUE
++- entering if (control:Tmp-Integer-0 < "%{control:Tmp-Integer-1}") {...}
+++[reject] returns reject
++- if (control:Tmp-Integer-0 < "%{control:Tmp-Integer-1}") returns reject
Using Post-Auth-Type Reject
# Executing group from file /etc/freeradius/sites-enabled/default
+- entering group REJECT {...}
[sql] expand: %{User-Name} -> 02080000000 at peerpointinternet.co.uk
[sql] sql_set_user escaped user --> '02080000000 at peerpointinternet.co.uk'
[sql] expand: %{User-Password} ->
[sql] ... expanding second conditional
[sql] expand: %{Chap-Password} -> 0x048bf9799185d69af262db5d5c0e4c9ba2
[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 (
'02080000000 at peerpointinternet.co.uk',
'0x048bf9799185d69af262db5d5c0e4c9ba2',
'Access-Reject', '2012-10-10 15:17:40')
rlm_sql (sql) in sql_postauth: query is INSERT INTO radpostauth
(username, pass, reply, authdate) VALUES (
'02080000000 at peerpointinternet.co.uk',
'0x048bf9799185d69af262db5d5c0e4c9ba2',
'Access-Reject', '2012-10-10 15:17:40')
rlm_sql (sql): Reserving sql socket id: 4
rlm_sql (sql): Released sql socket id: 4
++[sql] returns ok
[attr_filter.access_reject] expand: %{User-Name} ->
02080000000 at peerpointinternet.co.uk
attr_filter: Matched entry DEFAULT at line 11
++[attr_filter.access_reject] returns updated
Delaying reject of request 3 for 1 seconds
Going to the next request
Waking up in 0.7 seconds.
Sending delayed reject for request 3
Sending Access-Reject of id 19 to 193.000.221.000 port 1645
Waking up in 4.9 seconds.
To me it looks like the value is wrapping. Is this due to that even the
interpreter in the site config file is 32-bit only. If this is the case I
presume my only resort it perl. If this is the case could someone help me
convert this?
-----Original Message-----
From:
freeradius-users-bounces+jonathan.bastin=peerpointinternet.co.uk at lists.freer
adius.org
[mailto:freeradius-users-bounces+jonathan.bastin=peerpointinternet.co.uk at lis
ts.freeradius.org] On Behalf Of Phil Mayers
Sent: 10 October 2012 15:00
To: freeradius-users at lists.freeradius.org
Subject: Re: Query help
On 10/10/12 14:23, Jonathan Bastin wrote:
> I have been looking at this further am I am having trouble finding the
answer. Is anyone able to point me into the right direction.
You might find it a bit easier to debug if you perform the two SQL queries
(for the quota, and the current limit) separately, then compare the values.
For example:
update control {
Tmp-Integer-0 := "%{sql:select quota_limit ...}"
Tmp-Integer-1 := "%{sql:select sum(...) from radacct where ...}"
}
if (control:Tmp-Integer-0 < "%{control:Tmp-Integer-1}") {
reject
}
This will at least make it more obvious what is going on. To be frank, I
can't really understand what's going on in that debug.
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
-------------------------------------------------------------------------------------------------------------------------- This email (including any attachments) is intended only for the recipient(s) named above. It may contain confidential or privileged information and should not be read, copied or otherwise used by any other person. If you are not the named recipient please contact the sender and delete the email from your system. The author's incumbent expressions, views and thoughts are their own and not necessarily representative of those of the Peer Point Internet Ltd or associated companies.
More information about the Freeradius-Users
mailing list