Enable SQLCounter FreeRadius

QLCounter Is a module that is used to record user

Enable SQLCounter FreeRadius

QLCounter Is a module that is used to record user statistics that are entered and written in the database, such as recording the time a user logs in, transfers data, and sessions.

For example activating the Access Period and Quota Limit

sudo nano /etc/freeradius/3.0/mods-available/sqlcounter

Add code

sqlcounter accessperiod {
sql_module_instance = sql
dialect = ${modules.sql.dialect}

counter_name = Max-Access-Period-Time
check_name = Access-Period
key = User-Name
reset = never

$INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf
}

sqlcounter quotalimit {
sql_module_instance = sql
dialect = ${modules.sql.dialect}

counter_name = Max-Volume
check_name = Max-Data
reply_name = Mikrotik-Total-Limit
key = User-Name
reset = never

$INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf
}

Create file accessperiod.conf

-- MySQL --
sudo nano /etc/freeradius/3.0/mods-config/sql/counter/mysql/accessperiod.conf

-- Add Query --
query = "\
SELECT UNIX_TIMESTAMP() - UNIX_TIMESTAMP(AcctStartTime) \
FROM radacct \
WHERE UserName='%{${key}}' \
ORDER BY AcctStartTime LIMIT 1"
-- PqSQL --
sudo nano /etc/freeradius/3.0/mods-config/sql/counter/postgresql/accessperiod.conf

-- Add Query --
query = "\
SELECT date_part('epoch',CURRENT_TIMESTAMP)::int - date_part('epoch', AcctStartTime)::int \
FROM radacct \
WHERE UserName='%{${key}}' \
ORDER BY AcctStartTime LIMIT 1"

Create file quotalimit.conf

-- MySQL --
sudo nano /etc/freeradius/3.0/mods-config/sql/counter/mysql/quotalimit.conf

-- Add Code --
query = "\
SELECT (SUM(acctinputoctets) + SUM(acctoutputoctets)) \
FROM radacct \
WHERE UserName='%{${key}}'"
-- PgSQL --
sudo nano /etc/freeradius/3.0/mods-config/sql/counter/postgresql/quotalimit.conf

-- Add Query --
query = "\
SELECT (SUM(acctinputoctets) + SUM(acctoutputoctets)) \
FROM radacct \
WHERE UserName='%{${key}}'"

Edit file default

sudo nano /etc/freeradius/3.0/sites-enabled/default

Add code in the authorize{} section

authorize {
expiration
logintime
quotalimit
dailycounter
accessperiod
monthlycounter
noresetcounter
}

Enable sqlcounter

cd /etc/freeradius/3.0/mods-enabled
sudo ln -s ../mods-available/sqlcounter sqlcounter

If the above command doesn't work

sudo nano /etc/freeradius/3.0/radiusd.conf
-- Add --
$INCLUDE mods-enabled/ 
$INCLUDE mods-available/sqlcounter 

Restart FreeRadius

sudo /etc/init.d/freeradius restart
or
sudo systemctl restart freeradius.service

Video Tutorial

Related Post

Install PostgreSQL on Debian or Ubuntu
Install Webserver on Debian or Ubuntu
Install MariaDB on Debian or Ubuntu
Install FreeRadius MySQL on Debian or Ubuntu
Install FreeRadius PgSQL on Debian or Ubuntu

Comments

There are 4 comments on this post

  • avatar
    Reply
    Miguel Sanchez
    01 Feb, 2021 - 13:32
    Waouh Great content!! Could you please share how to create a profile in daloradius with a daily quotalimit?
    • avatar
      Reply
      Basuki Rahmad
      04 Feb, 2021 - 20:59
      Make sure enable sqlcounter, and then insert attribute Max-Data on table radcheck or radgroupcheck, and insert attribute Mikrotik-Total-Limit on table radreply or radgroupreply.
  • avatar
    Reply
    MD MONARUL ISLAM
    27 Mar, 2021 - 05:35
    after config restart freeradius there showing this message " Job for freeradius.service failed because the control process exited with error code.See "systemctl status freeradius.service" and "journalctl -xe" for details. " What happen ?
  • avatar
    Reply
    Asamoah Boakye
    19 May, 2021 - 14:19
    Hello can you check mysql counter for me, the counter should reset monthly data quota limit sqlcounter counterQoutaLimitMonthly { counter_name = 'Max-Total-Bandwidth' #define an attribute name. we will add this in daloRadius Profile check_name = 'Monthly-Bandwidth' reply_name = ChilliSpot-Max-Total-Octets sqlmod-inst = sql key = User-Name reset = monthly query = "SELECT (SUM(AcctInputOctets + AcctOutputOctets)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'" } thank you

Leave A Comment