I am trying to write a query to look at peoples quota and change their speed biased on these details. So far I have: 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));}"){ update reply { Reply-Message := "You have reached your transfer limit. Limited bandwitch" Cisco-AVPair := "lcp:interface-config#1=rate-limit output 524288 98304 196608 conform-action transmit exceed-action drop" Cisco-AVPair := "lcp:interface-config#2=rate-limit input 524288 98304 196608 conform-action transmit exceed-action drop" } } 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. -------------------------------------------------------------------------------------------------------------------------- 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.
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.
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@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@peerpointinternet.co.uk [sql] sql_set_user escaped user --> '02085000000@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@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@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@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@peerpointinternet.co.uk sql_set_user escaped user --> '02085000000@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@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@peerpointinternet.co.uk sql_set_user escaped user --> '02085000000@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@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@peerpointinternet.co.uk" with CHAP password [chap] Using clear text password "Sergy987" for user 02085000000@peerpointinternet.co.uk authentication. [chap] chap user 02085000000@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@peerpointinternet.co.uk) [sqlippool] Framed-IP-Address already exists Existing IP: 193.000.221.00 (did cli port 832 user 02085000000@peerpointinternet.co.uk) ++[sqlippool] returns noop [sql] expand: %{User-Name} -> 02085000000@peerpointinternet.co.uk [sql] sql_set_user escaped user --> '02085000000@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@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@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@lists.freeradius.org [mailto:freeradius-users-bounces+jonathan.bastin=peerpointinternet.co.uk@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.
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@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@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@peerpointinternet.co.uk [sql] sql_set_user escaped user --> '02085000000@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@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@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@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@peerpointinternet.co.uk sql_set_user escaped user --> '02085000000@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@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@peerpointinternet.co.uk sql_set_user escaped user --> '02085000000@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@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@peerpointinternet.co.uk" with CHAP password [chap] Using clear text password "Sergy987" for user 02085000000@peerpointinternet.co.uk authentication. [chap] chap user 02085000000@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@peerpointinternet.co.uk) [sqlippool] Framed-IP-Address already exists Existing IP: 193.000.221.00 (did cli port 832 user 02085000000@peerpointinternet.co.uk) ++[sqlippool] returns noop [sql] expand: %{User-Name} -> 02085000000@peerpointinternet.co.uk [sql] sql_set_user escaped user --> '02085000000@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@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@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@lists.freeradius.org [mailto:freeradius-users-bounces+jonathan.bastin=peerpointinternet.co.uk@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.
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.
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@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@peerpointinternet.co.uk [sql] sql_set_user escaped user --> '02080000000@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@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@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@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@peerpointinternet.co.uk sql_set_user escaped user --> '02080000000@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@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@peerpointinternet.co.uk sql_set_user escaped user --> '02080000000@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@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@peerpointinternet.co.uk [sql] sql_set_user escaped user --> '02080000000@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@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@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@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@lists.freer adius.org [mailto:freeradius-users-bounces+jonathan.bastin=peerpointinternet.co.uk@lis ts.freeradius.org] On Behalf Of Phil Mayers Sent: 10 October 2012 15:00 To: freeradius-users@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.
Dam. Is anyone able to assist me convert to perl as I am only a novice programmer at best. I am willing to make a donation to who that helps or a different location on confirmation of code working. -----Original Message----- From: freeradius-users-bounces+jonathan.bastin=peerpointinternet.co.uk@lists.freer adius.org [mailto:freeradius-users-bounces+jonathan.bastin=peerpointinternet.co.uk@lis ts.freeradius.org] On Behalf Of Alan DeKok Sent: 10 October 2012 15:36 To: FreeRadius users mailing list Subject: Re: Query help Jonathan Bastin wrote:
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.
Yes. All numbers in RADIUS are 32-bit. I think v3 will extend the internal code in the server to use 64-bit numbers. Alan DeKok. - 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.
On 10/10/12 15:25, Jonathan Bastin wrote:
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?
You could divide by some large factor inside the SQL database, which is likely using 64-bit or arbitrary precision internally. e.g. select sum() / 1000000 select quota / 1000000 ...to convert to megabytes, and then compare like that.
Issue with doing it that way is you would get decimal values returned which freeradius can't deal with. I am posting a bounty of $200 to someone that writes this in Perl and show instructions on how to install. It must update the replay with Cisco AV pair and be able to work with large values. -----Original Message----- From: freeradius-users-bounces+jonathan.bastin=peerpointinternet.co.uk@lists.freer adius.org [mailto:freeradius-users-bounces+jonathan.bastin=peerpointinternet.co.uk@lis ts.freeradius.org] On Behalf Of Phil Mayers Sent: 10 October 2012 16:04 To: freeradius-users@lists.freeradius.org Subject: Re: Query help On 10/10/12 15:25, Jonathan Bastin wrote:
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?
You could divide by some large factor inside the SQL database, which is likely using 64-bit or arbitrary precision internally. e.g. select sum() / 1000000 select quota / 1000000 ...to convert to megabytes, and then compare like that. - 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.
On 12 Oct 2012, at 09:16, Jonathan Bastin <jonathan.bastin@peerpointinternet.co.uk> wrote:
Issue with doing it that way is you would get decimal values returned which freeradius can't deal with.
So use round()... http://www.w3schools.com/sql/sql_func_round.asp -Arran
On Fri, Oct 12, 2012 at 3:16 PM, Jonathan Bastin <jonathan.bastin@peerpointinternet.co.uk> wrote:
Issue with doing it that way is you would get decimal values returned which freeradius can't deal with.
I am posting a bounty of $200
Good to hear that. Hopefuly someone will be able to help you.
to someone that writes this in Perl
Why perl?
and show instructions on how to install. It must update the replay with Cisco AV pair and be able to work with large values.
A quick glance shows you ONLY care whether the user is over limit or not. Is that correct? If yes, it should be MUCH quicker to simply do the comparison INSIDE the sql statement. Something like (untested, should work for mysql): 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' LIMIT 1) <= (SELECT SUM( AcctInputOctets + AcctOutputOctets) FROM radacct WHERE UserName='%{User-Name}' AND AcctStartTime > (DATE_SUB(CURDATE(),INTERVAL DAYOFMONTH(CURDATE())DAY))) ) as overlimit" } == 1 ){ update reply { Reply-Message := "You have reached your transfer limit. Limited bandwitch" Cisco-AVPair := "lcp:interface-config#1=rate-limit output 524288 98304 196608 conform-action transmit exceed-action drop" Cisco-AVPair := "lcp:interface-config#2=rate-limit input 524288 98304 196608 conform-action transmit exceed-action drop" } } Basically mysql supports subselect, and on a TRUE comparison test (e.g "select ((select 2) > (select 1)) as result") it will return "1". So you only need to test in FR whether the return value is 1 or not. Other db should have similar feature. -- Fajar
On Mon, Oct 15, 2012 at 7:35 AM, Fajar A. Nugraha <list@fajar.net> wrote:
If yes, it should be MUCH quicker to simply do the comparison INSIDE the sql statement. Something like (untested, should work for mysql):
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' LIMIT 1) <= (SELECT SUM( AcctInputOctets + AcctOutputOctets) FROM radacct WHERE UserName='%{User-Name}' AND AcctStartTime > (DATE_SUB(CURDATE(),INTERVAL DAYOFMONTH(CURDATE())DAY))) ) as overlimit" } == 1 ){
Sorry, it should be something like if ("%{sql: SELECT ( ( SELECT your_limit_query ) <= ( SELECT your_acct_query ) ) as overlimit;"} == 1 ) My first example is (obviously) missing the first select, but you get the idea :) -- Fajar
Magic thank you this has sorted the problem with excellent results. Please tell me where you would like some money to be sent as your help pointed me in the right direction. -----Original Message----- From: freeradius-users-bounces+jonathan.bastin=peerpointinternet.co.uk@lists.freer adius.org [mailto:freeradius-users-bounces+jonathan.bastin=peerpointinternet.co.uk@lis ts.freeradius.org] On Behalf Of Fajar A. Nugraha Sent: 15 October 2012 01:40 To: FreeRadius users mailing list Subject: Re: Query help On Mon, Oct 15, 2012 at 7:35 AM, Fajar A. Nugraha <list@fajar.net> wrote:
If yes, it should be MUCH quicker to simply do the comparison INSIDE the sql statement. Something like (untested, should work for mysql):
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' LIMIT 1) <= (SELECT SUM( AcctInputOctets + AcctOutputOctets) FROM radacct WHERE UserName='%{User-Name}' AND AcctStartTime > (DATE_SUB(CURDATE(),INTERVAL DAYOFMONTH(CURDATE())DAY))) ) as overlimit" } == 1 ){
Sorry, it should be something like if ("%{sql: SELECT ( ( SELECT your_limit_query ) <= ( SELECT your_acct_query ) ) as overlimit;"} == 1 ) My first example is (obviously) missing the first select, but you get the idea :) -- Fajar - 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.
On 10/12/2012 09:16 AM, Jonathan Bastin wrote:
Issue with doing it that way is you would get decimal values returned which freeradius can't deal with.
As others have pointed out - that's trivially dealt with. Hell, use right-shift if you want: select limit >> 10 select quota >> 10 ...it doesn't matter what you shift them by, or what units they're in, as long as they're consistent and all quota values are greater than then resulting minimum precision. Or, select the values as a string and trim the bottom X digits off using a regexp. Or... you get my point.
I am posting a bounty of $200 to someone that writes this in Perl and show instructions on how to install. It must update the replay with Cisco AV pair and be able to work with large values.
Obviously it's your money, but that $200 would be better spent asking for a 64-bit integer patch to FreeRADIUS IMO. FWIW I don't think an rlm_perl version would be hard, but I a) don't have the time the week and b) don't want your money anyway. If no-one has gotten to it by next week, I'll see what solutions spring to mind.
participants (5)
-
Alan DeKok -
Arran Cudbard-Bell -
Fajar A. Nugraha -
Jonathan Bastin -
Phil Mayers