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
