Sub-Total:
    0
    Total Item:
    0
    Total:
    0
    View Cart Favorite
    Checkout

    Enable SQLCounter FreeRadius

    QLCounter Is a module that is used to record user

    HomeBlogEnable SQLCounter FreeRadius
    Enable SQLCounter FreeRadius

    Enable SQLCounter FreeRadius

    • By: MangoSpot
    • 21 Apr, 2020

    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

    Tags: SqlCounter, PgSQL, MySQL, FreeRadius, Radius, PostgreSQL, MariaDB

    Related Post

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

    Comments

    There are 10 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
    • avatar
      Reply
      Dharmesh Chauhan
      09 Aug, 2023 - 08:41
      user not auto disconnect after accees-period time out
    • avatar
      Reply
      Dharmesh Chauhan
      15 Jul, 2023 - 16:45
      User has profile with 1 hour but after 1 hour user not disconnect. When I disconnect user and login it's not authorised.
    • avatar
      Reply
      Thilina Sandaruwan
      30 Aug, 2023 - 04:32
      Duplicate module "sqlcounter dailycounter { ... }", in file /etc/freeradius/3.0/mods-available/sqlcounter:47 and file /etc/freeradius/3.0/mods-enabled/sqlcounter:47 please help
      • avatar
        Reply
        Ikromul Umam
        12 Oct, 2023 - 14:05
        choose only one on the last to enable sqlcounter, before restarting freeradius above
    • avatar
      Reply
      Henrique Leandro Gobi
      04 Oct, 2024 - 13:36
      I have: Duplicate module "sqlcounter dailycounter", in file /etc/freeradius/3.0/mods-available/sqlcounter:49 and file /etc/freeradius/3.0/mods-enabled/sqlcounter:49 Same proplem as Thilina, someone help us, please
    • avatar
      Reply
      Austin Nzioka
      17 Apr, 2024 - 12:10
      Hey i am having some trouble when setting like a 10GB quota limit, when i put the value of Max-Data attribute 10,000,000,000 the user is only given like 2gb, is there a way i can use the Mikrotik-Total-Limit-Gigawords

    Leave A Comment