sqlcounter does not work
Uchenna Nebedum
nebeduch at gmail.com
Tue Jan 8 09:55:23 CET 2019
It will work for Coova-Chilli
Uchenna Nebedum
On Tue, Jan 8, 2019, 09:48 Philemon Jaomalaza <philemon.jaomalaza at gmail.com
wrote:
> Thank you,
>
> My NAS use Coova (chillispot), I will try it if this way will work for
> chillispot.
>
> But for now, I wish to continue to try it on freeradius using SQL beacause
> now I use Daloradius (Liran Tal) Web Management to manage it.
>
> I saw somewhere that that even freeradius 2.x requires a patch to make
> data counters Work but this is it right way for version 3?
>
> If it is necessary to patch, they made this like : diff -u
> rlm_sqlcounter.c.ori rlm_sqlcounter.c
>
> but I do not know where to find this file sqlcounter.c on the system (I
> use linux debian9)
>
> JMLZ
>
> -----Message d'origine-----
> De : Freeradius-Users [mailto:freeradius-users-bounces+philemon.jaomalaza=
> gmail.com at lists.freeradius.org] De la part de Uchenna Nebedum
> Envoyé : mardi 8 janvier 2019 09:44
> À : FreeRadius users mailing list <Freeradius-Users at lists.freeradius.org>
> Objet : Re: sqlcounter does not work
>
> There's a data usage limiting example in the FreeRADIUS beginners guide by
> Dirk Van Der Walt.
>
> It works if your NAS has a reply attribute similar to
> 'Mikrotik-Total-Limit' and it uses the perl module.
>
> Edit FreeRADiUS dictionary and add the following
>
> ATTRIBUTE FRBG-Reset-Type 3050 string
> ATTRIBUTE FRBG-Total-Bytes 3051 string
> ATTRIBUTE FRBG-Start-Time 3052 integer
> ATTRIBUTE FRBG-Used-Bytes 3053 string
> ATTRIBUTE FRBG-Avail-Bytes 3054 string
>
>
> Create two perl modules reset_time and check_usage in the modules directory
>
> ###reset_time#####
> perl reset_time { module = ${confdir}/reset_time.pl }
>
> ####check_usage####
> perl check_usage { module = ${confdir}/check_usage.pl }
>
> Create the scripts in the conf directory
>
> ######reset_time.pl#########
> <http://reset_time.pl#%23%23%23%23%23%23%23%23>
>
> *#! /usr/bin/perl -w use strict; use POSIX; # use ... # This is very
> important ! use vars qw(%RAD_CHECK); use constant RLM_MODULE_OK=>
> 2;# /* the module is OK, continue */ use constant RLM_MODULE_NOOP=>
> 7; use constant RLM_MODULE_UPDATED=> 8;# /* OK (pairs modified) */*
>
> *sub authorize { #Find out when the reset time should be
> if($RAD_CHECK{'FRBG-Reset-Type'} =~ /monthly/i){
> $RAD_CHECK{'FRBG-Start-Time'} = start_of_month() }
> if($RAD_CHECK{'FRBG-Reset-Type'} =~ /weekly/i){
> $RAD_CHECK{'FRBG-Start-Time'} = start_of_week() }
> if($RAD_CHECK{'FRBG-Reset-Type'} =~ /daily/i){
> $RAD_CHECK{'FRBG-Start-Time'} = start_of_day() }
> if(exists($RAD_CHECK{'FRBG-Start-Time'})){ return
> RLM_MODULE_UPDATED; }else{ return RLM_MODULE_NOOP;
> } } sub start_of_month { #Get the current timestamp; my
> $reset_on = 1; #you decide when the monthly CAP will reset my
> $unixtime; my
> ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtim e(time);
> if($mday < $reset_on ){ $unixtime = mktime (0, 0, 0, $reset_on,
> $mon-1, $year, 0, 0); #We use the previous month }else{
> $unixtime = mktime (0, 0, 0, $reset_on, $mon, $year, 0, 0); #We
> use this month } return $unixtime; } sub start_of_week { #Get
> the current timestamp; my
> ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtim e(time);
> #create a new timestamp: my $unixtime = mktime (0, 0, 0, $mday-$wday,
> $mon, $year, 0, 0); return $unixtime; } sub start_of_day { #Get the
> current timestamp;*
> * my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtim
> e(time); #create a new timestamp: my $unixtime = mktime (0, 0, 0,
> $mday, $mon, $year, 0, 0); return $unixtime; }*
>
> *#####check_usage.pl###### <http://check_usage.pl#%23%23%23%23%23> <
> http://check_usage.pl######>*
>
> *#! usr/bin/perl -w use strict; # use ... # This is very important! use
> vars qw(%RAD_CHECK %RAD_REPLY); use constant RLM_MODULE_OK=> 2;#
> /* the module is OK, continue */ use constant RLM_MODULE_UPDATED=>
> 8;# /* OK (pairs modified) */ use constant RLM_MODULE_REJECT=> 0;#
> /* immediately reject the request */ use constant RLM_MODULE_NOOP=>
> 7; my $int_max = 4294967296; sub authorize { #We will reply,
> depending on the usage #If FRBG-Total-Bytes is larger than the
> 32-bit limit we have to set a Gigaword attribute
> if(exists($RAD_CHECK{'FRBG-Total-Bytes'}) && exists($RAD_
> CHECK{'FRBG-Used-Bytes'})){ $RAD_CHECK{'FRBG-Avail-Bytes'}
> = $RAD_CHECK{'FRBGTotal-Bytes'} - $RAD_CHECK{'FRBG-Used-Bytes'};
> }else{ return RLM_MODULE_NOOP; }*
>
> * if($RAD_CHECK{'FRBG-Avail-Bytes'} <= $RAD_CHECK{'FRBG-UsedBytes'}){
> if($RAD_CHECK{'FRBG-Reset-Type'} ne 'never'){
> $RAD_REPLY{'Reply-Message'} = "Maximum $RAD_
> CHECK{'FRBG-Reset-Type'} usage exceeded"; }else{
> $RAD_REPLY{'Reply-Message'} = "Maximum usage exceeded";
> } } return RLM_MODULE_REJECT;
> if($RAD_CHECK{'FRBG-Avail-Bytes'} >= $int_max){ #Mikrotik's reply
> attributes $RAD_REPLY{'Mikrotik-Total-Limit'} =
> $RAD_CHECK{'FRBGAvail-Bytes'} %
> $int_max; $RAD_REPLY{'Mikrotik-Total-Limit-Gigawords'} =
> int($RAD_CHECK{'FRBG-Avail-Bytes'} / $int_max ); #Coova
> Chilli's reply attributes
> $RAD_REPLY{'ChilliSpot-Max-Total-Octets'} = $RAD_
> CHECK{'FRBG-Avail-Bytes'} % $int_max;
> $RAD_REPLY{'ChilliSpot-Max-Total-Gigawords'} =
> int($RAD_CHECK{'FRBG-Avail-Bytes'} / $int_max ); }else{
> Avail-Bytes'}; $RAD_REPLY{'Mikrotik-Total-Limit'} =
> $RAD_CHECK{'FRBG $RAD_REPLY{'ChilliSpot-Max-Total-Octets'} =
> $RAD_ CHECK{'FRBG-Avail-Bytes'}; } return
> RLM_MODULE_UPDATED; }*
>
> Add this unlang to the virtual server after the daily entry.
>
> *if((control:FRBG-Total-Bytes)&&(control:FRBG-Reset-Type)){ reset_time
> if(updated){ # Reset Time was updated, # we can
> now use it in a query update control { #Get the total
> usage up to now: FRBG-Used-Bytes := "%{sql:SELECT
> IFNULL(SUM(acctinputoctets - GREATEST((%{control:FRBGStart-Time} -
> UNIX_TIMESTAMP(acctstarttime)), 0))+ SUM(acctoutputoctets
> -GREATEST((%{control:FRBG-Start-Time} - UNIX_TIMESTAMP(acctstarttime)),
> 0)),0) FROM radacct WHERE username='%{request:User-Name}' AND
> UNIX_TIMESTAMP(acctstarttime) + acctsessiontime >
> '%{control:FRBG-Start-Time}'}" } } else{ #Asumes
> reset type = never #Get the total usage of the user update
> control { FRBG-Used-Bytes := "%{sql:SELECT IFNULL(SUM(ac
> ctinputoctets)+SUM(acctoutputoctets),0) FROM radacct WHERE
> username='%{request:User-Name}'}" } } #Now we know how much
> they are allowed to use and the usage. check_usage }*
>
> Then create users with the following check attributes
>
> *FRBG-Total-Bytes and FRBG-Reset-Type.*
>
> Please go through the perl code as there are a few typos in it. You can
> get the book and get a full understanding.
>
> Uchenna Nebedum
>
> On Tue, Jan 8, 2019, 06:51 Philemon Jaomalaza <
> philemon.jaomalaza at gmail.com
> wrote:
>
> > Hello Dear,
> >
> >
> >
> > I use time counters to limit session time without any problem.
> >
> > Now, on the same system, I must also configure the traffic limit.
> >
> >
> >
> > The problem is that if you use a traffic counter (daily or monthly),
> > the user stays connected even if the quota is exceeded.
> >
> >
> >
> > To do it, I proceeded like this:
> >
> >
> >
> > # nano /etc/freeradius/3.0/dictionary and add this:
> >
> >
> >
> > ATTRIBUTE Max-Monthly-Traffic 3003 integer
> >
> > ATTRIBUTE Monthly-Traffic-Limit 3004 integer
> >
> >
> >
> > #nano /mods-config/sql/counter/mysql/monthlytrafficcounter.conf and
> > add
> > this:
> >
> >
> >
> > query = "SELECT SUM (acctinputoctets + acctoutputoctets) FROM radacct
> > WHERE UserName = '% {$ {key}}' AND UNIX_TIMESTAMP (AcctStartTime)> '%%
> b'"
> >
> >
> >
> > Then, in / mods-available / sqlcounter, I added:
> >
> >
> >
> > sqlcounter monthlytrafficcounter {
> >
> > sql_module_instance = sql
> >
> > #dialect = $ {modules.sql.dialect}
> >
> > dialect = "mysql"
> >
> > counter_name = Monthly-Traffic
> >
> > check_name = Max-Monthly-Traffic
> >
> > reply_name = Monthly-Traffic-Limit
> >
> > key = User-Name
> >
> > reset = monthly
> >
> > $ INCLUDE $ {modconfdir} / sql / counter / $ {dialect} / $ {.:
> > instance} .conf
> >
> > }
> >
> >
> >
> > and enable it:
> >
> >
> >
> > # cd /etc/freeradius/3.0/mods-enabled
> >
> > ln -s ../mods-available/sqlcounter sqlcounter
> >
> >
> >
> > # nano /etc/freeradius/3.0/sites-enabled/default
> >
> > authorize {
> >
> > ...
> >
> > sql
> >
> > # check monthly usage limit
> >
> > monthlytrafficcounter
> >
> > ...
> >
> > }
> >
> >
> >
> > and restart freeradius
> >
> >
> >
> > Could you help me to find out why it does not work?
> >
> >
> >
> >
> >
> > JMLZ
> >
> >
> >
> >
> >
> > ---
> > L'absence de virus dans ce courrier électronique a été vérifiée par le
> > logiciel antivirus Avast.
> > https://www.avast.com/antivirus
> > -
> > List info/subscribe/unsubscribe? See
> > http://www.freeradius.org/list/users.html
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
>
>
> ---
> L'absence de virus dans ce courrier électronique a été vérifiée par le
> logiciel antivirus Avast.
> https://www.avast.com/antivirus
>
>
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
More information about the Freeradius-Users
mailing list