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