Changing radgroup with a sqlcounter ?

[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