Re: Changing radgroup with a sqlcounter ?

2010-11-21 Thread Arrgghh

Hello JDL-4,

I use Mysql and sorry the quota is indeed 100MB.

Thank you very much for this great explanation, I think that's pretty what I
want to do.

Could you let me know where this group_membership_query is called ? Do i
need to put it in the authorize section of freeradius ?

Thank you again, you are really helpful !! 
-- 
View this message in context: 
http://freeradius.1045715.n5.nabble.com/Changing-radgroup-with-a-sqlcounter-tp3270524p3274236.html
Sent from the FreeRadius - User mailing list archive at Nabble.com.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html


Re: Changing radgroup with a sqlcounter ?

2010-11-20 Thread Arrgghh

Anyone ?
-- 
View this message in context: 
http://freeradius.1045715.n5.nabble.com/Changing-radgroup-with-a-sqlcounter-tp3270524p3273562.html
Sent from the FreeRadius - User mailing list archive at Nabble.com.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html


Re: Changing radgroup with a sqlcounter ?

2010-11-20 Thread JDL
If I understand you correctly, instead of kicking the user with the 
sqlcounter, you simply want to move them into a different group.  It should 
pretty straight forward.  You need to set read_groups = yes in your sql 
module.  I would recommend that you put the check and reply attributes in the 
database as well.  You should be able to use the default tables and queries for 
this (i.e. authorize_group_check_query and authorize_group_reply_query).  See 
the FreeRADIUS schema and docs.

I am not sure what 100mo means.  Do you mean 100MB (Megabytes) per month?

The only custom part would be to write a proper group_membership_query that 
returns the correct group name.  This becomes more an SQL issue than a radius 
issue.  You also did not indicate what database type you are using.  If you are 
using MySQL, it should be something like the following.  You will have to tweak 
it to get the results you want.  Test the SQL query directly against your 
database (i.e. outside of FreeRADIUS).  Once you get the results you want, use 
that query as your group_membership_query.

group_membership_query = SELECT 
IF((SUM(acctinputoctets)+SUM(acctoutputoctets))104857600, '512Kgroup', 
'128Kgroup') FROM radacct WHERE username='%{%k}' AND 
acctstarttime=DATE_FORMAT(CURDATE(), '%%Y-%%m-01 00:00:00')

Note:  The double percent signs in the DATE_FORMAT command causes FreeRADIUS to 
escape the percent sign.  FreeRADIUS converts this to a single percent sign 
before sending the query to the database.  For testing the above query directly 
against MySQL, you would need to use something like the following.  Notice the 
single percent signs.

SELECT IF((SUM(acctinputoctets)+SUM(acctoutputoctets))104857600, '512Kgroup', 
'128Kgroup') FROM radacct WHERE username='testuser' AND 
acctstarttime=DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00')

See http://dev.mysql.com/doc/ if you need further assistance with the MySQL IF 
operator or any other part of the query.

Note that this does not use the sqlcounter at all.  It is simply changing the 
group membership based on the SQL query.

Hope this helps.  Good luck!

Jim L.


On Nov 18, 2010, at 5:23 AM, Arrgghh wrote:

 
 Hello,
 
 I am working on a radius config for a hotspot. I already configured a script
 that kick the user when the quota exceed thanks to some very useful posts in
 the coova forum.
 
 I used this counter :
 
 
 sqlcounter noresetBytecounter {
counter-name = Total-Max-Octets
check-name = Max-Octets
reply-name = ChilliSpot-Max-Total-Octets
sqlmod-inst = sql
key = User-Name
reset = never
query = SELECT (SUM(AcctInputOctets)+SUM(AcctOutputOctets))
 FROM radacct WHERE UserName='%{%k}'
  }
 
 
 My goal is that a user belongs to a standard group with a 512kbps bandwith
 and a quota of 100mo. Then when he exceeds his quota, he is switched to a
 128 kbps group.
 
 I know how to setup groups that limits bandwidth and volume. What I don't
 know is how to set up a counter that instead of kicking the user out of the
 network when he exceeds his quota switch him to another radgroup.
 
 Any clue how i can do it ?
 
 Thank you very much,
 -- 
 View this message in context: 
 http://freeradius.1045715.n5.nabble.com/Changing-radgroup-with-a-sqlcounter-tp3270524p3270524.html
 Sent from the FreeRadius - User mailing list archive at Nabble.com.
 -
 List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html