sqlcounter does not work
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
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@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
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
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@lists.freeradius.org] De la part de Uchenna Nebedum Envoyé : mardi 8 janvier 2019 09:44 À : FreeRadius users mailing list <Freeradius-Users@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@gmail.com wrote: - 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
It will work for Coova-Chilli
Uchenna Nebedum
On Tue, Jan 8, 2019, 09:48 Philemon Jaomalaza <philemon.jaomalaza@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@lists.freeradius.org] De la part de Uchenna Nebedum
> Envoyé : mardi 8 janvier 2019 09:44
> À : FreeRadius users mailing list <Freeradius-Users@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@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
Hello,
I still have issues on sqlcounter both time counter or data counter using sql , when a user with Max-Daily-Session login, after the time allowed, the user was disconnected but it can reconnect again on the same day.
I see this on debug mode on first login and when the user re-login again :
..............
........................
(0) dailycounter: No integer found in result string "". May be first session, setting counter to 0
(0) dailycounter: Allowing user, &control: Max-Daily-Session value (1048576) is greater than counter value (0)
(0) dailycounter: Setting &reply: Session-Timeout value to 600
(0) [dailycounter] = ok
...................
.....................
It does not find the time already used and it set the counter to 0 then the user has again the alowed time. Where is my issues ?
Someone can help me? Say me if I need to post here the full debug
JMLZ
-----Message d'origine-----
De : Freeradius-Users [mailto:freeradius-users-bounces+philemon.jaomalaza=gmail.com@lists.freeradius.org] De la part de Uchenna Nebedum
Envoyé : mardi 8 janvier 2019 11:55
À : FreeRadius users mailing list <Freeradius-Users@lists.freeradius.org>
Objet : Re: sqlcounter does not work
It will work for Coova-Chilli
Uchenna Nebedum
On Tue, Jan 8, 2019, 09:48 Philemon Jaomalaza <philemon.jaomalaza@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@lists.freeradius.org] De la part de Uchenna Nebedum Envoyé :
> mardi 8 janvier 2019 09:44 À : FreeRadius users mailing list
> <Freeradius-Users@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@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
-
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
On Jan 10, 2019, at 12:36 AM, Philemon Jaomalaza <philemon.jaomalaza@gmail.com> wrote:
I still have issues on sqlcounter both time counter or data counter using sql , when a user with Max-Daily-Session login, after the time allowed, the user was disconnected but it can reconnect again on the same day.
I see this on debug mode on first login and when the user re-login again : .............. ........................ (0) dailycounter: No integer found in result string "". May be first session, setting counter to 0 (0) dailycounter: Allowing user, &control: Max-Daily-Session value (1048576) is greater than counter value (0) (0) dailycounter: Setting &reply: Session-Timeout value to 600 (0) [dailycounter] = ok ................... .....................
If only you could post the *full* debug log, so we could see what's going wrong...
It does not find the time already used and it set the counter to 0 then the user has again the alowed time. Where is my issues ?
Since you helpfully didn't post any useful information, we don't really know. Is the server receiving accounting packets? If not, that would explain it. The server has *no idea* how long a user is online, unless the NAS *tells it*. This isn't a magic way for the server to know what's going on at the NAS. The NAS has to tell it.
Someone can help me? Say me if I need to post here the full debug
ALL of the documentation says to post this. Just... follow the documentation. Alan DeKok.
Hello, This is the debug output when I try to loggin the user abcd with Max-Daily-Session value (120), the user is disconnected after 120s but can reconnecte again on the same day (1) Received Access-Request Id 74 from 154.126.XX.XXX:40633 to 154.126.XX.XXX:1812 length 238 (1) ChilliSpot-Version = "1.3.1.4" (1) User-Name = "abcd" (1) CHAP-Challenge = 0x0a56eeec203ad7528b129d470e4eea6d (1) CHAP-Password = 0x0004dfa56cbffb8ca68189acdf0374e0e1 (1) Service-Type = Login-User (1) Acct-Session-Id = "5c37444000000003" (1) Framed-IP-Address = 192.168.5.2 (1) NAS-Port-Type = Wireless-802.11 (1) NAS-Port = 3 (1) NAS-Port-Id = "00000003" (1) Calling-Station-Id = "D0-FF-98-95-F2-93" (1) Called-Station-Id = "00-19-3B-13-C7-7E" (1) NAS-IP-Address = 10.0.0.0 (1) NAS-Identifier = "YYYYYYYY" (1) WISPr-Logoff-URL = "http://10.0.0.0:3990/logoff" (1) Message-Authenticator = 0x4bad6a09b5afe64bde2447cb7659c04e (1) # Executing section authorize from file /etc/freeradius/3.0/sites-enabled/default (1) authorize { (1) policy filter_username { (1) if (&User-Name) { (1) if (&User-Name) -> TRUE (1) if (&User-Name) { (1) if (&User-Name =~ / /) { (1) if (&User-Name =~ / /) -> FALSE (1) if (&User-Name =~ /@[^@]*@/ ) { (1) if (&User-Name =~ /@[^@]*@/ ) -> FALSE (1) if (&User-Name =~ /\.\./ ) { (1) if (&User-Name =~ /\.\./ ) -> FALSE (1) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) { (1) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE (1) if (&User-Name =~ /\.$/) { (1) if (&User-Name =~ /\.$/) -> FALSE (1) if (&User-Name =~ /@\./) { (1) if (&User-Name =~ /@\./) -> FALSE (1) } # if (&User-Name) = notfound (1) } # policy filter_username = notfound (1) [preprocess] = ok (1) chap: &control:Auth-Type := CHAP (1) [chap] = ok (1) [mschap] = noop (1) [digest] = noop (1) suffix: Checking for suffix after "@" (1) suffix: No '@' in User-Name = "abcd", looking up realm NULL (1) suffix: No such realm "NULL" (1) [suffix] = noop (1) eap: No EAP-Message, not doing EAP (1) [eap] = noop (1) [files] = noop (1) sql: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (1) sql: --> abcd (1) sql: SQL-User-Name set to 'abcd' rlm_sql (sql): Reserved connection (1) (1) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id (1) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'abcd' ORDER BY id (1) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'abcd' ORDER BY id (1) sql: User found in radcheck table (1) sql: Conditional check items matched, merging assignment check items (1) sql: Cleartext-Password := "1234" (1) sql: Max-Daily-Session := 120 (1) sql: EXPAND SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id (1) sql: --> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'abcd' ORDER BY id (1) sql: Executing select query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'abcd' ORDER BY id (1) sql: User found in radreply table, merging reply items (1) sql: Acct-Interim-Interval := 60 (1) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority (1) sql: --> SELECT groupname FROM radusergroup WHERE username = 'abcd' ORDER BY priority (1) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'abcd' ORDER BY priority (1) sql: User not found in any groups rlm_sql (sql): Released connection (1) rlm_sql (sql): Need 4 more connections to reach 10 spares rlm_sql (sql): Opening additional connection (6), 1 of 26 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'radius' on Localhost via UNIX socket, server version 10.1.37-MariaDB-0+deb9u1, protocol version 10 (1) [sql] = ok sqlcounter_expand: 'SELECT SUM(acctsessiontime - GREATEST((1547067600 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = '%{User-Name}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1547067600'' (1) dailycounter: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (1) dailycounter: --> abcd (1) dailycounter: SQL-User-Name set to 'abcd' rlm_sql (sql): Reserved connection (2) (1) dailycounter: Executing select query: SELECT SUM(acctsessiontime - GREATEST((1547067600 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = 'abcd' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1547067600' rlm_sql (sql): Released connection (2) (1) dailycounter: EXPAND %{sql:SELECT SUM(acctsessiontime - GREATEST((1547067600 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = '%{User-Name}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1547067600'} (1) dailycounter: --> 37 (1) dailycounter: Allowing user, &control:Max-Daily-Session value (120) is greater than counter value (37) (1) dailycounter: Setting &reply:Session-Timeout value to 83 (1) [dailycounter] = ok (1) monthlytrafficcounter: WARNING: Couldn't find check attribute, control:Max-Monthly-Traffic, doing nothing... (1) [monthlytrafficcounter] = noop (1) [expiration] = noop (1) [logintime] = noop (1) pap: WARNING: Auth-Type already set. Not setting to PAP (1) [pap] = noop (1) } # authorize = ok (1) Found Auth-Type = CHAP (1) # Executing group from file /etc/freeradius/3.0/sites-enabled/default (1) Auth-Type CHAP { (1) chap: Comparing with "known good" Cleartext-Password (1) chap: CHAP user "abcd" authenticated successfully (1) [chap] = ok (1) } # Auth-Type CHAP = ok (1) # Executing section post-auth from file /etc/freeradius/3.0/sites-enabled/default (1) post-auth { (1) update { (1) No attributes updated (1) } # update = noop (1) sql: EXPAND .query (1) sql: --> .query (1) sql: Using query template 'query' rlm_sql (sql): Reserved connection (3) (1) sql: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (1) sql: --> abcd (1) sql: SQL-User-Name set to 'abcd' (1) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') (1) sql: --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'abcd', '0x0004dfa56cbffb8ca68189acdf0374e0e1', 'Access-Accept', '2019-01-10 16:10:48') (1) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'abcd', '0x0004dfa56cbffb8ca68189acdf0374e0e1', 'Access-Accept', '2019-01-10 16:10:48') (1) sql: SQL query returned: success (1) sql: 1 record(s) updated rlm_sql (sql): Released connection (3) (1) [sql] = ok (1) [exec] = noop (1) policy remove_reply_message_if_eap { (1) if (&reply:EAP-Message && &reply:Reply-Message) { (1) if (&reply:EAP-Message && &reply:Reply-Message) -> FALSE (1) else { (1) [noop] = noop (1) } # else = noop (1) } # policy remove_reply_message_if_eap = noop (1) } # post-auth = ok (1) Sent Access-Accept Id 74 from 154.126.XX.XXX:1812 to 154.126.XX.XXX:40633 length 0 (1) Acct-Interim-Interval = 60 (1) Session-Timeout = 83 (1) Finished request Waking up in 4.9 seconds. (2) Received Accounting-Request Id 9 from 154.126.XX.XXX:57186 to 154.126.XX.XXX:1813 length 166 (2) ChilliSpot-Version = "1.3.1.4" (2) Attr-26.14559.10 = 0x00000002 (2) Event-Timestamp = "Jan 10 2019 16:10:48 EAT" (2) User-Name = "abcd" (2) Acct-Status-Type = Start (2) Acct-Session-Id = "5c37444000000003" (2) Framed-IP-Address = 192.168.5.2 (2) NAS-Port-Type = Wireless-802.11 (2) NAS-Port = 3 (2) NAS-Port-Id = "00000003" (2) Calling-Station-Id = "D0-FF-98-95-F2-93" (2) Called-Station-Id = "00-19-3B-13-C7-7E" (2) NAS-IP-Address = 10.0.0.0 (2) NAS-Identifier = "MGHotspot" (2) # Executing section preacct from file /etc/freeradius/3.0/sites-enabled/default (2) preacct { (2) [preprocess] = ok (2) policy acct_unique { (2) update request { (2) Tmp-String-9 := "ai:" (2) } # update request = noop (2) if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) && ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) { (2) EXPAND %{hex:&Class} (2) --> (2) EXPAND ^%{hex:&Tmp-String-9} (2) --> ^61693a (2) if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) && ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) -> FALSE (2) else { (2) update request { (2) EXPAND %{md5:%{User-Name},%{Acct-Session-ID},%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}},%{NAS-Identifier},%{NAS-Port-ID},%{NAS-Port}} (2) --> 7a4ab5beca09585ad042a709184aab98 (2) &Acct-Unique-Session-Id := 7a4ab5beca09585ad042a709184aab98 (2) } # update request = noop (2) } # else = noop (2) } # policy acct_unique = noop (2) suffix: Checking for suffix after "@" (2) suffix: No '@' in User-Name = "abcd", looking up realm NULL (2) suffix: No such realm "NULL" (2) [suffix] = noop (2) [files] = noop (2) } # preacct = ok (2) # Executing section accounting from file /etc/freeradius/3.0/sites-enabled/default (2) accounting { (2) detail: EXPAND /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d (2) detail: --> /var/log/freeradius/radacct/154.126.XX.XXX/detail-20190110 (2) detail: /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/freeradius/radacct/154.126.XX.XXX/detail-20190110 (2) detail: EXPAND %t (2) detail: --> Thu Jan 10 16:10:48 2019 (2) [detail] = ok (2) [unix] = ok (2) sql: EXPAND %{tolower:type.%{Acct-Status-Type}.query} (2) sql: --> type.start.query (2) sql: Using query template 'query' rlm_sql (sql): Reserved connection (4) (2) sql: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (2) sql: --> abcd (2) sql: SQL-User-Name set to 'abcd' (2) sql: EXPAND INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress) VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{%{NAS-Port-ID}:-%{NAS-Port}}', '%{NAS-Port-Type}', FROM_UNIXTIME(%{integer:Event-Timestamp}), FROM_UNIXTIME(%{integer:Event-Timestamp}), NULL, '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}') (2) sql: --> INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress) VALUES ('5c37444000000003', '7a4ab5beca09585ad042a709184aab98', 'abcd', '', '10.0.0.0', '00000003', 'Wireless-802.11', FROM_UNIXTIME(1547125848), FROM_UNIXTIME(1547125848), NULL, '0', '', '', '', '0', '0', '00-19-3B-13-C7-7E', 'D0-FF-98-95-F2-93', '', '', '', '192.168.5.2') (2) sql: Executing query: INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress) VALUES ('5c37444000000003', '7a4ab5beca09585ad042a709184aab98', 'abcd', '', '10.0.0.0', '00000003', 'Wireless-802.11', FROM_UNIXTIME(1547125848), FROM_UNIXTIME(1547125848), NULL, '0', '', '', '', '0', '0', '00-19-3B-13-C7-7E', 'D0-FF-98-95-F2-93', '', '', '', '192.168.5.2') (2) sql: SQL query returned: success (2) sql: 1 record(s) updated rlm_sql (sql): Released connection (4) (2) [sql] = ok (2) [exec] = noop (2) attr_filter.accounting_response: EXPAND %{User-Name} (2) attr_filter.accounting_response: --> abcd (2) attr_filter.accounting_response: Matched entry DEFAULT at line 12 (2) [attr_filter.accounting_response] = updated (2) } # accounting = updated (2) Sent Accounting-Response Id 9 from 154.126.XX.XXX:1813 to 154.126.XX.XXX:57186 length 0 (2) Finished request (2) Cleaning up request packet ID 9 with timestamp +43 Waking up in 4.9 seconds. (1) Cleaning up request packet ID 74 with timestamp +43 Ready to process requests (3) Received Accounting-Request Id 10 from 154.126.XX.XXX:57186 to 154.126.XX.XXX:1813 length 214 (3) ChilliSpot-Version = "1.3.1.4" (3) Attr-26.14559.10 = 0x00000002 (3) Event-Timestamp = "Jan 10 2019 16:10:53 EAT" (3) User-Name = "abcd" (3) Acct-Input-Octets = 14548 (3) Acct-Output-Octets = 3737 (3) Acct-Input-Gigawords = 0 (3) Acct-Output-Gigawords = 0 (3) Acct-Input-Packets = 60 (3) Acct-Output-Packets = 27 (3) Acct-Session-Time = 5 (3) Acct-Terminate-Cause = User-Request (3) Acct-Status-Type = Stop (3) Acct-Session-Id = "5c37444000000003" (3) Framed-IP-Address = 192.168.5.2 (3) NAS-Port-Type = Wireless-802.11 (3) NAS-Port = 3 (3) NAS-Port-Id = "00000003" (3) Calling-Station-Id = "D0-FF-98-95-F2-93" (3) Called-Station-Id = "00-19-3B-13-C7-7E" (3) NAS-IP-Address = 10.0.0.0 (3) NAS-Identifier = "MGHotspot" (3) # Executing section preacct from file /etc/freeradius/3.0/sites-enabled/default (3) preacct { (3) [preprocess] = ok (3) policy acct_unique { (3) update request { (3) Tmp-String-9 := "ai:" (3) } # update request = noop (3) if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) && ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) { (3) EXPAND %{hex:&Class} (3) --> (3) EXPAND ^%{hex:&Tmp-String-9} (3) --> ^61693a (3) if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) && ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) -> FALSE (3) else { (3) update request { (3) EXPAND %{md5:%{User-Name},%{Acct-Session-ID},%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}},%{NAS-Identifier},%{NAS-Port-ID},%{NAS-Port}} (3) --> 7a4ab5beca09585ad042a709184aab98 (3) &Acct-Unique-Session-Id := 7a4ab5beca09585ad042a709184aab98 (3) } # update request = noop (3) } # else = noop (3) } # policy acct_unique = noop (3) suffix: Checking for suffix after "@" (3) suffix: No '@' in User-Name = "abcd", looking up realm NULL (3) suffix: No such realm "NULL" (3) [suffix] = noop (3) [files] = noop (3) } # preacct = ok (3) # Executing section accounting from file /etc/freeradius/3.0/sites-enabled/default (3) accounting { (3) detail: EXPAND /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d (3) detail: --> /var/log/freeradius/radacct/154.126.XX.XXX/detail-20190110 (3) detail: /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/freeradius/radacct/154.126.XX.XXX/detail-20190110 (3) detail: EXPAND %t (3) detail: --> Thu Jan 10 16:10:54 2019 (3) [detail] = ok (3) [unix] = ok (3) sql: EXPAND %{tolower:type.%{Acct-Status-Type}.query} (3) sql: --> type.stop.query (3) sql: Using query template 'query' rlm_sql (sql): Reserved connection (0) (3) sql: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (3) sql: --> abcd (3) sql: SQL-User-Name set to 'abcd' (3) sql: EXPAND UPDATE radacct SET acctstoptime = FROM_UNIXTIME(%{integer:Event-Timestamp}), acctsessiontime = %{%{Acct-Session-Time}:-NULL}, acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', acctterminatecause = '%{Acct-Terminate-Cause}', connectinfo_stop = '%{Connect-Info}' WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' (3) sql: --> UPDATE radacct SET acctstoptime = FROM_UNIXTIME(1547125853), acctsessiontime = 5, acctinputoctets = '0' << 32 | '14548', acctoutputoctets = '0' << 32 | '3737', acctterminatecause = 'User-Request', connectinfo_stop = '' WHERE AcctUniqueId = '7a4ab5beca09585ad042a709184aab98' (3) sql: Executing query: UPDATE radacct SET acctstoptime = FROM_UNIXTIME(1547125853), acctsessiontime = 5, acctinputoctets = '0' << 32 | '14548', acctoutputoctets = '0' << 32 | '3737', acctterminatecause = 'User-Request', connectinfo_stop = '' WHERE AcctUniqueId = '7a4ab5beca09585ad042a709184aab98' rlm_sql_mysql: Rows matched: 1 Changed: 1 Warnings: 0 (3) sql: SQL query returned: success (3) sql: 1 record(s) updated rlm_sql (sql): Released connection (0) rlm_sql (sql): Need 3 more connections to reach 10 spares rlm_sql (sql): Opening additional connection (7), 1 of 25 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'radius' on Localhost via UNIX socket, server version 10.1.37-MariaDB-0+deb9u1, protocol version 10 (3) [sql] = ok (3) [exec] = noop (3) attr_filter.accounting_response: EXPAND %{User-Name} (3) attr_filter.accounting_response: --> abcd (3) attr_filter.accounting_response: Matched entry DEFAULT at line 12 (3) [attr_filter.accounting_response] = updated (3) } # accounting = updated (3) Sent Accounting-Response Id 10 from 154.126.XX.XXX:1813 to 154.126.XX.XXX:57186 length 0 (3) Finished request (3) Cleaning up request packet ID 10 with timestamp +49 Ready to process requests Need your help please, JMLZ -----Message d'origine----- De : Freeradius-Users [mailto:freeradius-users-bounces+philemon.jaomalaza=gmail.com@lists.freeradius.org] De la part de Alan DeKok Envoyé : jeudi 10 janvier 2019 15:03 À : FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Objet : Re: sqlcounter does not work On Jan 10, 2019, at 12:36 AM, Philemon Jaomalaza <philemon.jaomalaza@gmail.com> wrote:
I still have issues on sqlcounter both time counter or data counter using sql , when a user with Max-Daily-Session login, after the time allowed, the user was disconnected but it can reconnect again on the same day.
I see this on debug mode on first login and when the user re-login again : .............. ........................ (0) dailycounter: No integer found in result string "". May be first session, setting counter to 0 (0) dailycounter: Allowing user, &control: Max-Daily-Session value (1048576) is greater than counter value (0) (0) dailycounter: Setting &reply: Session-Timeout value to 600 (0) [dailycounter] = ok ................... .....................
If only you could post the *full* debug log, so we could see what's going wrong...
It does not find the time already used and it set the counter to 0 then the user has again the alowed time. Where is my issues ?
Since you helpfully didn't post any useful information, we don't really know. Is the server receiving accounting packets? If not, that would explain it. The server has *no idea* how long a user is online, unless the NAS *tells it*. This isn't a magic way for the server to know what's going on at the NAS. The NAS has to tell it.
Someone can help me? Say me if I need to post here the full debug
ALL of the documentation says to post this. Just... follow the documentation. Alan DeKok. - 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
On Jan 10, 2019, at 8:27 AM, Philemon Jaomalaza <philemon.jaomalaza@gmail.com> wrote:
This is the debug output when I try to loggin the user abcd with Max-Daily-Session value (120), the user is disconnected after 120s
Good...
but can reconnecte again on the same day
<sigh> Where is the debug log for THAT? How do you expect me to debug a problem with no debug output?
Need your help please,
I'm trying, but I'm about ready to give up. You're making it as difficult as possible for me to help you. I have no idea why. Alan DeKok.
but can reconnecte again on the same day
<sigh> Where is the debug log for THAT? Because the debug is the same as a first login when I reconnecta again and again and again, this is bellow : (0) Received Access-Request Id 193 from 154.126.11.199:60760 to 154.126.32.122:1812 length 238 (0) ChilliSpot-Version = "1.3.1.4" (0) User-Name = "abcd" (0) CHAP-Challenge = 0x33d758aefb30edc95aafff31281e97cd (0) CHAP-Password = 0x00d897a25804e201dde711e3a495395aec (0) Service-Type = Login-User (0) Acct-Session-Id = "5c373fdc00000003" (0) Framed-IP-Address = 192.168.5.2 (0) NAS-Port-Type = Wireless-802.11 (0) NAS-Port = 3 (0) NAS-Port-Id = "00000003" (0) Calling-Station-Id = "D0-FF-98-95-F2-93" (0) Called-Station-Id = "00-19-3B-13-C7-7E" (0) NAS-IP-Address = 10.0.0.0 (0) NAS-Identifier = "MGHotspot" (0) WISPr-Logoff-URL = "http://10.0.0.0:3990/logoff" (0) Message-Authenticator = 0x04aab2027637d1cda9963452ed1747fd (0) # Executing section authorize from file /etc/freeradius/3.0/sites-enabled/default (0) authorize { (0) policy filter_username { (0) if (&User-Name) { (0) if (&User-Name) -> TRUE (0) if (&User-Name) { (0) if (&User-Name =~ / /) { (0) if (&User-Name =~ / /) -> FALSE (0) if (&User-Name =~ /@[^@]*@/ ) { (0) if (&User-Name =~ /@[^@]*@/ ) -> FALSE (0) if (&User-Name =~ /\.\./ ) { (0) if (&User-Name =~ /\.\./ ) -> FALSE (0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) { (0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE (0) if (&User-Name =~ /\.$/) { (0) if (&User-Name =~ /\.$/) -> FALSE (0) if (&User-Name =~ /@\./) { (0) if (&User-Name =~ /@\./) -> FALSE (0) } # if (&User-Name) = notfound (0) } # policy filter_username = notfound (0) [preprocess] = ok (0) chap: &control:Auth-Type := CHAP (0) [chap] = ok (0) [mschap] = noop (0) [digest] = noop (0) suffix: Checking for suffix after "@" (0) suffix: No '@' in User-Name = "abcd", looking up realm NULL (0) suffix: No such realm "NULL" (0) [suffix] = noop (0) eap: No EAP-Message, not doing EAP (0) [eap] = noop (0) [files] = noop (0) sql: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (0) sql: --> abcd (0) sql: SQL-User-Name set to 'abcd' rlm_sql (sql): Reserved connection (0) (0) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id (0) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'abcd' ORDER BY id (0) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'abcd' ORDER BY id (0) sql: User found in radcheck table (0) sql: Conditional check items matched, merging assignment check items (0) sql: Cleartext-Password := "1234" (0) sql: Max-Daily-Session := 120 (0) sql: EXPAND SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id (0) sql: --> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'abcd' ORDER BY id (0) sql: Executing select query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'abcd' ORDER BY id (0) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority (0) sql: --> SELECT groupname FROM radusergroup WHERE username = 'abcd' ORDER BY priority (0) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'abcd' ORDER BY priority (0) sql: User not found in any groups rlm_sql (sql): Released connection (0) rlm_sql (sql): Need 5 more connections to reach 10 spares rlm_sql (sql): Opening additional connection (5), 1 of 27 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'radius' on Localhost via UNIX socket, server version 10.1.37-MariaDB-0+deb9u1, protocol version 10 (0) [sql] = ok sqlcounter_expand: 'SELECT SUM(acctsessiontime - GREATEST((1547067600 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = '%{User-Name}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1547067600'' (0) dailycounter: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (0) dailycounter: --> abcd (0) dailycounter: SQL-User-Name set to 'abcd' rlm_sql (sql): Reserved connection (1) (0) dailycounter: Executing select query: SELECT SUM(acctsessiontime - GREATEST((1547067600 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = 'abcd' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1547067600' (0) dailycounter: NULL value in first column of result rlm_sql (sql): Released connection (1) (0) dailycounter: EXPAND %{sql:SELECT SUM(acctsessiontime - GREATEST((1547067600 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = '%{User-Name}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1547067600'} (0) dailycounter: --> (0) dailycounter: No integer found in result string "". May be first session, setting counter to 0 (0) dailycounter: Allowing user, &control:Max-Daily-Session value (120) is greater than counter value (0) (0) dailycounter: Setting &reply:Session-Timeout value to 120 (0) [dailycounter] = ok (0) monthlytrafficcounter: WARNING: Couldn't find check attribute, control:Max-Monthly-Traffic, doing nothing... (0) [monthlytrafficcounter] = noop (0) [expiration] = noop (0) [logintime] = noop (0) pap: WARNING: Auth-Type already set. Not setting to PAP (0) [pap] = noop (0) } # authorize = ok (0) Found Auth-Type = CHAP (0) # Executing group from file /etc/freeradius/3.0/sites-enabled/default (0) Auth-Type CHAP { (0) chap: Comparing with "known good" Cleartext-Password (0) chap: CHAP user "abcd" authenticated successfully (0) [chap] = ok (0) } # Auth-Type CHAP = ok (0) # Executing section post-auth from file /etc/freeradius/3.0/sites-enabled/default (0) post-auth { (0) update { (0) No attributes updated (0) } # update = noop (0) sql: EXPAND .query (0) sql: --> .query (0) sql: Using query template 'query' rlm_sql (sql): Reserved connection (2) (0) sql: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (0) sql: --> abcd (0) sql: SQL-User-Name set to 'abcd' (0) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') (0) sql: --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'abcd', '0x00d897a25804e201dde711e3a495395aec', 'Access-Accept', '2019-01-10 15:55:39') (0) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'abcd', '0x00d897a25804e201dde711e3a495395aec', 'Access-Accept', '2019-01-10 15:55:39') (0) sql: SQL query returned: success (0) sql: 1 record(s) updated rlm_sql (sql): Released connection (2) (0) [sql] = ok (0) [exec] = noop (0) policy remove_reply_message_if_eap { (0) if (&reply:EAP-Message && &reply:Reply-Message) { (0) if (&reply:EAP-Message && &reply:Reply-Message) -> FALSE (0) else { (0) [noop] = noop (0) } # else = noop (0) } # policy remove_reply_message_if_eap = noop (0) } # post-auth = ok (0) Sent Access-Accept Id 193 from 154.126.32.122:1812 to 154.126.11.199:60760 length 0 (0) Session-Timeout = 120 (0) Finished request Waking up in 4.9 seconds. (1) Received Accounting-Request Id 5 from 154.126.11.199:57186 to 154.126.32.122:1813 length 166 (1) ChilliSpot-Version = "1.3.1.4" (1) Attr-26.14559.10 = 0x00000002 (1) Event-Timestamp = "Jan 10 2019 15:55:39 EAT" (1) User-Name = "abcd" (1) Acct-Status-Type = Start (1) Acct-Session-Id = "5c373fdc00000003" (1) Framed-IP-Address = 192.168.5.2 (1) NAS-Port-Type = Wireless-802.11 (1) NAS-Port = 3 (1) NAS-Port-Id = "00000003" (1) Calling-Station-Id = "D0-FF-98-95-F2-93" (1) Called-Station-Id = "00-19-3B-13-C7-7E" (1) NAS-IP-Address = 10.0.0.0 (1) NAS-Identifier = "MGHotspot" (1) # Executing section preacct from file /etc/freeradius/3.0/sites-enabled/default (1) preacct { (1) [preprocess] = ok (1) policy acct_unique { (1) update request { (1) Tmp-String-9 := "ai:" (1) } # update request = noop (1) if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) && ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) { (1) EXPAND %{hex:&Class} (1) --> (1) EXPAND ^%{hex:&Tmp-String-9} (1) --> ^61693a (1) if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) && ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) -> FALSE (1) else { (1) update request { (1) EXPAND %{md5:%{User-Name},%{Acct-Session-ID},%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}},%{NAS-Identifier},%{NAS-Port-ID},%{NAS-Port}} (1) --> 41d3a708c2bf94512685619a6457a6ac (1) &Acct-Unique-Session-Id := 41d3a708c2bf94512685619a6457a6ac (1) } # update request = noop (1) } # else = noop (1) } # policy acct_unique = noop (1) suffix: Checking for suffix after "@" (1) suffix: No '@' in User-Name = "abcd", looking up realm NULL (1) suffix: No such realm "NULL" (1) [suffix] = noop (1) [files] = noop (1) } # preacct = ok (1) # Executing section accounting from file /etc/freeradius/3.0/sites-enabled/default (1) accounting { (1) detail: EXPAND /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d (1) detail: --> /var/log/freeradius/radacct/154.126.11.199/detail-20190110 (1) detail: /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/freeradius/radacct/154.126.11.199/detail-20190110 (1) detail: EXPAND %t (1) detail: --> Thu Jan 10 15:55:39 2019 (1) [detail] = ok (1) [unix] = ok (1) sql: EXPAND %{tolower:type.%{Acct-Status-Type}.query} (1) sql: --> type.start.query (1) sql: Using query template 'query' rlm_sql (sql): Reserved connection (3) (1) sql: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (1) sql: --> abcd (1) sql: SQL-User-Name set to 'abcd' (1) sql: EXPAND INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress) VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{%{NAS-Port-ID}:-%{NAS-Port}}', '%{NAS-Port-Type}', FROM_UNIXTIME(%{integer:Event-Timestamp}), FROM_UNIXTIME(%{integer:Event-Timestamp}), NULL, '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}') (1) sql: --> INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress) VALUES ('5c373fdc00000003', '41d3a708c2bf94512685619a6457a6ac', 'abcd', '', '10.0.0.0', '00000003', 'Wireless-802.11', FROM_UNIXTIME(1547124939), FROM_UNIXTIME(1547124939), NULL, '0', '', '', '', '0', '0', '00-19-3B-13-C7-7E', 'D0-FF-98-95-F2-93', '', '', '', '192.168.5.2') (1) sql: Executing query: INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctupdatetime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress) VALUES ('5c373fdc00000003', '41d3a708c2bf94512685619a6457a6ac', 'abcd', '', '10.0.0.0', '00000003', 'Wireless-802.11', FROM_UNIXTIME(1547124939), FROM_UNIXTIME(1547124939), NULL, '0', '', '', '', '0', '0', '00-19-3B-13-C7-7E', 'D0-FF-98-95-F2-93', '', '', '', '192.168.5.2') (1) sql: SQL query returned: success (1) sql: 1 record(s) updated rlm_sql (sql): Released connection (3) (1) [sql] = ok (1) [exec] = noop (1) attr_filter.accounting_response: EXPAND %{User-Name} (1) attr_filter.accounting_response: --> abcd (1) attr_filter.accounting_response: Matched entry DEFAULT at line 12 (1) [attr_filter.accounting_response] = updated (1) } # accounting = updated (1) Sent Accounting-Response Id 5 from 154.126.32.122:1813 to 154.126.11.199:57186 length 0 (1) Finished request (1) Cleaning up request packet ID 5 with timestamp +22 Waking up in 4.9 seconds. (0) Cleaning up request packet ID 193 with timestamp +22 Ready to process requests JMLZ -----Message d'origine----- De : Freeradius-Users [mailto:freeradius-users-bounces+philemon.jaomalaza=gmail.com@lists.freeradius.org] De la part de Alan DeKok Envoyé : jeudi 10 janvier 2019 17:28 À : FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Objet : Re: sqlcounter does not work On Jan 10, 2019, at 8:27 AM, Philemon Jaomalaza <philemon.jaomalaza@gmail.com> wrote:
This is the debug output when I try to loggin the user abcd with Max-Daily-Session value (120), the user is disconnected after 120s
Good...
but can reconnecte again on the same day
<sigh> Where is the debug log for THAT? How do you expect me to debug a problem with no debug output?
Need your help please,
I'm trying, but I'm about ready to give up. You're making it as difficult as possible for me to help you. I have no idea why. Alan DeKok. - 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
On Jan 10, 2019, at 10:52 AM, Philemon Jaomalaza <philemon.jaomalaza@gmail.com> wrote:
but can reconnecte again on the same day
<sigh> Where is the debug log for THAT?
Because the debug is the same as a first login when I reconnecta again and again and again, this is bellow :
It shouldn't be the *same*. And if you read the debug output, it's not. That's why we keep telling people to *read* the debug output. The first session says: (1) Sent Access-Accept Id 74 from 154.126.XX.XXX:1812 to 154.126.XX.XXX:40633 length 0 (1) Acct-Interim-Interval = 60 (1) Session-Timeout = 83 (1) Finished request Which means that the user should be disconnected after 83 seconds. Is the user disconnected? And the Accounting packet then says: (3) Acct-Session-Time = 5 Which means that the next time the user logs in, he will be given 78 seconds. It *should* be that simple. Instead, we get:
sqlcounter_expand: 'SELECT SUM(acctsessiontime - GREATEST((1547067600 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = '%{User-Name}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1547067600'' (0) dailycounter: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (0) dailycounter: --> abcd (0) dailycounter: SQL-User-Name set to 'abcd' rlm_sql (sql): Reserved connection (1) (0) dailycounter: Executing select query: SELECT SUM(acctsessiontime - GREATEST((1547067600 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = 'abcd' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1547067600' (0) dailycounter: NULL value in first column of result
Which would indicate that the SQL query isn't returning anything useful. So... what happens when you run that SQL query manually? That's why it's printed out in debug mode. So you can *read it*, and see for yourself what's going on.
rlm_sql (sql): Released connection (1) (0) dailycounter: EXPAND %{sql:SELECT SUM(acctsessiontime - GREATEST((1547067600 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = '%{User-Name}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1547067600'} (0) dailycounter: --> (0) dailycounter: No integer found in result string "". May be first session, setting counter to 0
So either the SQL queries are wrong, or the SQL database is returning bad values. Again, the debug log is there for people to *read*. Please, read it. It is very helpful, and will tell you exactly what's going on. Alan DeKok.
Like your suggestion, I read step by step the output, cleaning all content of radacct, increase the value of acct-interim-interval to 60, then try it : In the first loggin, the user was disconnect when the allowed time is used. Trying to reconnect the same user, it can't and say "Maximum daily usage time reached" It works now, Thank you, JMLZ -----Message d'origine----- De : Freeradius-Users [mailto:freeradius-users-bounces+philemon.jaomalaza=gmail.com@lists.freeradius.org] De la part de Alan DeKok Envoyé : jeudi 10 janvier 2019 19:24 À : FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Objet : Re: sqlcounter does not work On Jan 10, 2019, at 10:52 AM, Philemon Jaomalaza <philemon.jaomalaza@gmail.com> wrote:
but can reconnecte again on the same day
<sigh> Where is the debug log for THAT?
Because the debug is the same as a first login when I reconnecta again and again and again, this is bellow :
It shouldn't be the *same*. And if you read the debug output, it's not. That's why we keep telling people to *read* the debug output. The first session says: (1) Sent Access-Accept Id 74 from 154.126.XX.XXX:1812 to 154.126.XX.XXX:40633 length 0 (1) Acct-Interim-Interval = 60 (1) Session-Timeout = 83 (1) Finished request Which means that the user should be disconnected after 83 seconds. Is the user disconnected? And the Accounting packet then says: (3) Acct-Session-Time = 5 Which means that the next time the user logs in, he will be given 78 seconds. It *should* be that simple. Instead, we get:
sqlcounter_expand: 'SELECT SUM(acctsessiontime - GREATEST((1547067600 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = '%{User-Name}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1547067600'' (0) dailycounter: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} (0) dailycounter: --> abcd (0) dailycounter: SQL-User-Name set to 'abcd' rlm_sql (sql): Reserved connection (1) (0) dailycounter: Executing select query: SELECT SUM(acctsessiontime - GREATEST((1547067600 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = 'abcd' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1547067600' (0) dailycounter: NULL value in first column of result
Which would indicate that the SQL query isn't returning anything useful. So... what happens when you run that SQL query manually? That's why it's printed out in debug mode. So you can *read it*, and see for yourself what's going on.
rlm_sql (sql): Released connection (1) (0) dailycounter: EXPAND %{sql:SELECT SUM(acctsessiontime - GREATEST((1547067600 - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = '%{User-Name}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '1547067600'} (0) dailycounter: --> (0) dailycounter: No integer found in result string "". May be first session, setting counter to 0
So either the SQL queries are wrong, or the SQL database is returning bad values. Again, the debug log is there for people to *read*. Please, read it. It is very helpful, and will tell you exactly what's going on. Alan DeKok. - 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
On Jan 11, 2019, at 12:22 AM, Philemon Jaomalaza <philemon.jaomalaza@gmail.com> wrote:
Like your suggestion, I read step by step the output, cleaning all content of radacct, increase the value of acct-interim-interval to 60, then try it : In the first loggin, the user was disconnect when the allowed time is used. Trying to reconnect the same user, it can't and say "Maximum daily usage time reached"
It works now,
That's good to hear. As always, a careful step-by-step approach works the best. Alan DeKok.
participants (3)
-
Alan DeKok -
Philemon Jaomalaza -
Uchenna Nebedum