sqlcounter does not work
Philemon Jaomalaza
philemon.jaomalaza at gmail.com
Tue Jan 8 09:48:15 CET 2019
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#########
*#! /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
-
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
More information about the Freeradius-Users
mailing list