sqlcounter does not work

Uchenna Nebedum nebeduch at gmail.com
Tue Jan 8 07:43:44 CET 2019


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#########

*#! /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######>*

*#! 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


More information about the Freeradius-Users mailing list