sqlcounter does not work

classic Classic list List threaded Threaded
12 messages Options
| Threaded
Open this post in threaded view
|

sqlcounter does not work

jmlz
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
| Threaded
Open this post in threaded view
|

Re: sqlcounter does not work

UcheNebed
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###### <<a href="http://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 <[hidden email]
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
| Threaded
Open this post in threaded view
|

RE: sqlcounter does not work

jmlz
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=[hidden email]] De la part de Uchenna Nebedum
Envoyé : mardi 8 janvier 2019 09:44
À : FreeRadius users mailing list <[hidden email]>
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###### <<a href="http://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 <[hidden email]
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
| Threaded
Open this post in threaded view
|

Re: sqlcounter does not work

UcheNebed
It will work for Coova-Chilli

Uchenna Nebedum

On Tue, Jan 8, 2019, 09:48 Philemon Jaomalaza <[hidden email]
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=
> [hidden email]] De la part de Uchenna Nebedum
> Envoyé : mardi 8 janvier 2019 09:44
> À : FreeRadius users mailing list <[hidden email]>
> 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> <
> <a href="http://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 <
> [hidden email]
> 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
| Threaded
Open this post in threaded view
|

RE: sqlcounter does not work

jmlz
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=[hidden email]] De la part de Uchenna Nebedum
Envoyé : mardi 8 janvier 2019 11:55
À : FreeRadius users mailing list <[hidden email]>
Objet : Re: sqlcounter does not work

It will work for Coova-Chilli

Uchenna Nebedum

On Tue, Jan 8, 2019, 09:48 Philemon Jaomalaza <[hidden email]
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=
> [hidden email]] De la part de Uchenna Nebedum Envoyé :
> mardi 8 janvier 2019 09:44 À : FreeRadius users mailing list
> <[hidden email]>
> 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> <
> <a href="http://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 <
> [hidden email]
> 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


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: sqlcounter does not work

Alan DeKok-2
On Jan 10, 2019, at 12:36 AM, Philemon Jaomalaza <[hidden email]> 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
| Threaded
Open this post in threaded view
|

RE: sqlcounter does not work

jmlz
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=[hidden email]] De la part de Alan DeKok
Envoyé : jeudi 10 janvier 2019 15:03
À : FreeRadius users mailing list <[hidden email]>
Objet : Re: sqlcounter does not work

On Jan 10, 2019, at 12:36 AM, Philemon Jaomalaza <[hidden email]> 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


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: sqlcounter does not work

Alan DeKok-2
On Jan 10, 2019, at 8:27 AM, Philemon Jaomalaza <[hidden email]> 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
| Threaded
Open this post in threaded view
|

RE: sqlcounter does not work

jmlz
> 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=[hidden email]] De la part de Alan DeKok
Envoyé : jeudi 10 janvier 2019 17:28
À : FreeRadius users mailing list <[hidden email]>
Objet : Re: sqlcounter does not work

On Jan 10, 2019, at 8:27 AM, Philemon Jaomalaza <[hidden email]> 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


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: sqlcounter does not work

Alan DeKok-2
On Jan 10, 2019, at 10:52 AM, Philemon Jaomalaza <[hidden email]> 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
| Threaded
Open this post in threaded view
|

RE: sqlcounter does not work

jmlz
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=[hidden email]] De la part de Alan DeKok
Envoyé : jeudi 10 janvier 2019 19:24
À : FreeRadius users mailing list <[hidden email]>
Objet : Re: sqlcounter does not work

On Jan 10, 2019, at 10:52 AM, Philemon Jaomalaza <[hidden email]> 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


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: sqlcounter does not work

Alan DeKok-2
On Jan 11, 2019, at 12:22 AM, Philemon Jaomalaza <[hidden email]> 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.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html