Query help
Jonathan Bastin
jonathan.bastin at peerpointinternet.co.uk
Wed Oct 10 15:23:46 CEST 2012
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.
-----Original Message-----
From: Jonathan Bastin [mailto:jonathan.bastin at peerpointinternet.co.uk]
Sent: 09 October 2012 14:56
To: 'FreeRadius users mailing list'
Subject: RE: Query help
This is the full dump I get
rad_recv: Access-Request packet from host 193.000.221.00 port 1645, id=213, length=141
Framed-Protocol = PPP
User-Name = "02085000000 at peerpointinternet.co.uk"
CHAP-Password = 0x045f3e13da52acf8b9e784c0c125ed102f
Connect-Info = "11066368/1094656"
NAS-Port-Type = Virtual
NAS-Port = 832
NAS-Port-Id = "Uniq-Sess-ID832"
Service-Type = Framed-User
NAS-IP-Address = 193.000.221.00
# 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} -> 02085000000 at peerpointinternet.co.uk
[sql] sql_set_user escaped user --> '02085000000 at peerpointinternet.co.uk'
rlm_sql (sql): Reserving sql socket id: 4
[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 = '02085000000 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 = '02085000000 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 = '02085000000 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: 4
++[sql] returns ok
++? if ("%{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';}"<= "%{sql:
++SELECT SUM( AcctInputOctets + AcctOutputOctets) FROM radacct WHERE
++UserName='%{User-Name}' AND AcctStartTime >
++(DATE_SUB(CURDATE(),INTERVAL DAYOFMONTH(CURDATE())DAY));}")
sql_xlat
expand: %{User-Name} -> 02085000000 at peerpointinternet.co.uk
sql_set_user escaped user --> '02085000000 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 = '02085000000 at peerpointinternet.co.uk' AND radgroupcheck.attribute = 'CS-Total-Octets-Monthly'; rlm_sql (sql): Reserving sql socket id: 3 sql_xlat finished rlm_sql (sql): Released sql socket id: 3
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} -> 02085000000 at peerpointinternet.co.uk
sql_set_user escaped user --> '02085000000 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='02085000000 at peerpointinternet.co.uk' AND AcctStartTime > (DATE_SUB(CURDATE(),INTERVAL DAYOFMONTH(CURDATE())DAY)); rlm_sql (sql): Reserving sql socket id: 2 sql_xlat finished rlm_sql (sql): Released sql socket id: 2
expand: %{sql: SELECT SUM( AcctInputOctets + AcctOutputOctets) FROM radacct WHERE UserName='%{User-Name}' AND AcctStartTime > (DATE_SUB(CURDATE(),INTERVAL DAYOFMONTH(CURDATE())DAY));} -> 48827883953 ? Evaluating ("%{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';}"<= "%{sql: SELECT SUM( AcctInputOctets + AcctOutputOctets) FROM radacct WHERE UserName='%{User-Name}' AND AcctStartTime > (DATE_SUB(CURDATE(),INTERVAL DAYOFMONTH(CURDATE())DAY));}") -> TRUE
++? if ("%{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';}"<= "%{sql:
++SELECT SUM( AcctInputOctets + AcctOutputOctets) FROM radacct WHERE
++UserName='%{User-Name}' AND AcctStartTime >
++(DATE_SUB(CURDATE(),INTERVAL DAYOFMONTH(CURDATE())DAY));}") -> TRUE
++- entering if ("%{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';}"<= "%{sql:
++SELECT SUM( AcctInputOctets + AcctOutputOctets) FROM radacct WHERE
++UserName='%{User-Name}' AND AcctStartTime >
++(DATE_SUB(CURDATE(),INTERVAL DAYOFMONTH(CURDATE())DAY));}") {...}
+++[reply] returns ok
++- if ("%{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';}"<= "%{sql:
++SELECT SUM( AcctInputOctets + AcctOutputOctets) FROM radacct WHERE
++UserName='%{User-Name}' AND AcctStartTime >
++(DATE_SUB(CURDATE(),INTERVAL DAYOFMONTH(CURDATE())DAY));}") returns ok
++[expiration] returns noop [logintime] returns noop
[pap] WARNING: Auth-Type already set. Not setting to PAP
++[pap] returns noop
Found Auth-Type = CHAP
# Executing group from file /etc/freeradius/sites-enabled/default
+- entering group CHAP {...}
[chap] login attempt by "02085000000 at peerpointinternet.co.uk" with CHAP password [chap] Using clear text password "Sergy987" for user 02085000000 at peerpointinternet.co.uk authentication.
[chap] chap user 02085000000 at peerpointinternet.co.uk authenticated succesfully
++[chap] returns ok
# Executing section post-auth from file /etc/freeradius/sites-enabled/default
+- entering group post-auth {...}
[sqlippool] expand: Existing IP: %{reply:Framed-IP-Address} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) -> Existing IP: 193.000.221.00 (did cli port 832 user 02085000000 at peerpointinternet.co.uk)
[sqlippool] Framed-IP-Address already exists
Existing IP: 193.000.221.00 (did cli port 832 user 02085000000 at peerpointinternet.co.uk)
++[sqlippool] returns noop
[sql] expand: %{User-Name} -> 02085000000 at peerpointinternet.co.uk
[sql] sql_set_user escaped user --> '02085000000 at peerpointinternet.co.uk'
[sql] expand: %{User-Password} ->
[sql] ... expanding second conditional
[sql] expand: %{Chap-Password} -> 0x045f3e13da52acf8b9e784c0c125ed102f
[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 ( '02085000000 at peerpointinternet.co.uk', '0x045f3e13da52acf8b9e784c0c125ed102f', 'Access-Accept', '2012-10-09 14:53:29')
rlm_sql (sql) in sql_postauth: query is INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '02085000000 at peerpointinternet.co.uk', '0x045f3e13da52acf8b9e784c0c125ed102f', 'Access-Accept', '2012-10-09 14:53:29')
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 213 to 193.000.221.00 port 1645
Framed-Protocol = PPP
Framed-Compression = Van-Jacobson-TCP-IP
Framed-IP-Address = 193.000.221.00
Service-Type = Framed-User
Framed-MTU = 1500
Cisco-AVPair = "ip:dns-servers=208.67.222.222 8.8.8.8"
Framed-IP-Netmask = 255.255.255.255
Reply-Message = "You have reached your transfer limit. Limited bandwitch"
Finished request 17.
-----Original Message-----
From: freeradius-users-bounces+jonathan.bastin=peerpointinternet.co.uk at lists.freeradius.org [mailto:freeradius-users-bounces+jonathan.bastin=peerpointinternet.co.uk at lists.freeradius.org] On Behalf Of Alan DeKok
Sent: 09 October 2012 14:17
To: FreeRadius users mailing list
Subject: Re: Query help
Jonathan Bastin wrote:
> *Issue I get is that I always get *
>
> Tue Oct 9 13:43:17 2012 : Info: ++- if ("%{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';}"<= "%{sql: SELECT SUM( AcctInputOctets +
> AcctOutputOctets) FROM radacct WHERE UserName='%{User-Name}' AND
> AcctStartTime > (DATE_SUB(CURDATE(),INTERVAL
> DAYOFMONTH(CURDATE())DAY));}") returns ok
>
> *It doesn’t actualy update the reply biased on what the outcome is of
> the SQL query. Any help would be very much appreciated.*
a) you're using "radiusd -xX". That's not necessary. Just use "radiusd -X"
b) You're not looking at the debug lines BEFORE that one. They show why the "if" is taken, or not taken.
-
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