FreeRADIUS Group Policy Design: Service Assignment Using radcheck, radgroupreply, and radusergroup

 

---

• Author: Syed Jahanzaib ~A Humble Human being! nothing else 
• Platform: aacable.wordpress.com
• Category: ISP Network Architecture
• Audience: ISP Network Engineers & System Administrators

 Disclaimer & Note on Writing Style

Every network environment is unique. A solution that works effectively in one infrastructure may require modification in another. Readers are strongly encouraged to understand the underlying concepts and adapt the guidance according to their own architecture, operational policies, and risk tolerance.

Blind copy-paste implementation without proper validation, testing, and change management is never recommended , especially in production environments. Always ensure proper backups and risk assessment before applying any configuration.

The content shared here is based on hands-on experience from real-world deployments, ISP environments, lab testing, and continuous learning. While I strive for technical accuracy, no technical implementation is entirely free from the possibility of error. Constructive discussion and alternative approaches are always welcome.

Due to professional commitments, it is not always feasible to publish highly detailed or multi-part write-ups. The technical logic and implementation details are written based on my own practical experience. AI tools such as ChatGPT are used only to refine grammar, structure, and presentation , not to generate the core technical concepts.

This blog is not intended for client acquisition or follower growth. It exists solely to share practical knowledge and real-world experience with the community.

Thank you for your understanding and continued support.

---

Introduction

Intended Audience:
This guide is intended for:

• ISP network engineers managing PPPoE/IPoE subscribers
• Operators using NAS with FreeRADIUS
• System administrators responsible for subscriber policy control
• ISPs planning to scale beyond manual user-based configurations

It is especially useful for teams that:

• ✔ Want centralized package management
• ✔ Need scalable service provisioning
• ✔ Are integrating RADIUS with billing systems
• ✔ Plan to introduce time-based or promotional packages

Prerequisites
Before implementing this design, ensure the following are in place:

• FreeRADIUS installed with SQL module enabled
• MikroTik configured as NAS
• PPPoE authentication integrated with RADIUS
• SQL database connected to FreeRADIUS
• Accounting enabled

This guide assumes a working FreeRADIUS + MikroTik integration.

---

Table of Contents

1. Introduction
2. Who This Guide Is For
3. Prerequisites
4. Understanding the Core FreeRADIUS Tables
5. How FreeRADIUS Makes Decisions (Processing Order)
6. Step 1 – Creating the User (radcheck)
7. Step 2 – Creating a Service Package (radgroupreply)
8. Step 3 – Assigning Package to User (radusergroup)
9. Step 4 – Adding Package Restrictions (radgroupcheck)
10. How the Policy Flow Works in Real ISP Environment
11. Role of MikroTik in Service Enforcement
12. Testing the Configuration using radclient
13. Scaling the Design for Large Subscriber Base
14. Understanding radreply and Its Role
15. Why radreply Should Not Be Used for Standard ISP Packages
16. Group-Based Architecture vs Per-User Model
17. When radreply Should Be Used
18. Operational Benefits for ISPs
19. Best Practice Architecture Summary

---

Whether you’re running a small community network or a growing regional ISP, this approach helps build a structured and manageable subscriber policy framework. This becomes especially important when managing hundreds or thousands of subscribers.

In ISP environments, managing subscriber services efficiently is just as important as delivering bandwidth. As networks grow from a few dozen users to hundreds or thousands of subscribers, manually assigning policies per user quickly becomes unsustainable.

FreeRADIUS provides a scalable way to design and manage subscriber packages using group-based policy architecture. Instead of defining speed, limits, or access rules individually for each user, ISPs can:

• Create service packages
• Assign users to those packages
• Centrally control policy changes

This approach simplifies:

• ✔ Package upgrades
• ✔ Seasonal offers
• ✔ Bulk migrations
• ✔ Billing integration

In this post, we’ll walk through how FreeRADIUS group policy design works using:

• Radcheck
• Radusergroup
• Radgroupreply

But one of the most confusing parts for many operators is:

• 👉 Where do we create the user?
• 👉 Where do we define the package?
• 👉 Where do we assign the speed?

Many people mistakenly put everything inside one table… and later things become messy when customers increase. Today we’ll simplify this using a real-world ISP scenario.

Let’s say:

• You want to create a 10 Mbps internet package
• And assign it to a user named zaib

---

Understanding the Core FreeRADIUS Tables:

How FreeRADIUS Makes Decisions (Processing Order)

FreeRADIUS does not randomly apply policies. It processes SQL data in a defined order. Before creating users or packages, it’s important to understand how FreeRADIUS evaluates policies internally. FreeRADIUS processes SQL tables in the following order: To understand how packages work, first understand how FreeRADIUS evaluates users. FreeRADIUS SQL works like a proper ISP business model. Each table has a separate role, just like departments in a company.

How All Tables Work Together

In a typical authentication flow, these SQL tables operate as a layered policy engine rather than isolated components.

When a user attempts to log in, FreeRADIUS first evaluates radcheck for user-specific validation rules such as credentials, expiration settings, or account-level restrictions. If authentication succeeds, the system then checks radreply for any user-specific reply attributes that should be returned directly to the NAS (for example, a custom bandwidth override or a temporary service adjustment).

Next, FreeRADIUS consults radusergroup to determine which service group(s) the user belongs to. Based on that membership, radgroupcheck enforces group-level conditions such as Simultaneous-Use limits, time restrictions, or other policy constraints. If all validation checks pass, the corresponding service attributes from radgroupreply are loaded and returned to the NAS, typically defining bandwidth profiles, VLAN assignments, session parameters, or other package-level settings.

This layered structure separates authentication, user-level overrides, group-based validation, and service delivery into distinct logical stages. The result is a modular and scalable design where:

• radcheck → validates the user
• radreply → applies user-specific service overrides
• radusergroup → maps the user to a package
• radgroupcheck → enforces package restrictions
• radgroupreply → defines package services

This separation of concerns makes the system flexible and maintainable, especially in ISP environments where thousands of subscribers share common packages but still require granular per-user control when needed.

This order becomes critical when both user-level and group-level policies exist. This order is important because it explains why group-based design is scalable and predictable.

---

Step 1: Create the User (radcheck):

radcheck — “User-Specific Authentication & Control Rules”

The radcheck table stores per-user validation conditions that must be satisfied during authentication. Unlike radgroupcheck, which applies rules at the group level, radcheck applies checks directly to an individual username.
This table is commonly used to define authentication credentials (such as Cleartext-Password), account expiration dates, login time restrictions, or other user-specific control attributes.
In ISP deployments, radcheck typically contains the subscriber’s password and any individual-level overrides — for example, disabling a single account, applying a temporary restriction, or setting a custom limit that differs from the assigned package.

This table is only for login authentication.  No speed here. No service here.
Just:

✔ Username
✔ Password

Example:
User:
zaib / abc123

INSERT INTO radcheck (username, attribute, op, value)
VALUES ('zaib', 'Cleartext-Password', ':=', 'abc123');

Now user exists in system ✔

Step 2: Create the 10MB Package (radgroupreply):

radgroupreply — “Service Policy Definitions”

This table defines the service attributes that FreeRADIUS will return to the NAS when a user belongs to a particular group.
Think of radgroupreply as the place where you declare what services a group should receive — for example, rate limits, session times, VLAN attributes, or any other RADIUS reply attributes.
When a user authenticates and is mapped to a group, all matching entries in radgroupreply are sent as reply attributes. For ISPs, this is typically where you define customer package characteristics (e.g., upload/download speeds, bandwidth policy strings, or other NAS-specific flags).

Let’s create:

Package name = 10MB

INSERT INTO radgroupreply (groupname, attribute, op, value)
VALUES ('10MB', 'Mikrotik-Rate-Limit', ':=', '10M/10M');

Now:

Group 10MB = 10 Mbps service

Step 3: Assign Package to User (radusergroup):

radusergroup — “User ↔ Package Mapping”

This table links users to groups (packages) and controls which service profile applies to a subscriber.
Instead of assigning reply attributes individually per user, radusergroup lets you assign one or more groups (or packages) to a username. It also supports a priority field, which determines the order in which packages should be evaluated when multiple memberships exist.
In ISP context, this is where you map a subscriber (zaib, user123) to a tariff plan such as “10MB”, “Night-Unlimited”, or other service offerings. This mapping makes large-scale provisioning and upgrades far easier.

Now we “subscribe” user zaib to this package.

INSERT INTO radusergroup (username, groupname, priority)
VALUES ('zaib', '10MB', 1);

That’s it. No need to touch speed again.

Note on Priority:
The priority field controls which group is evaluated first. This becomes important when a user belongs to multiple groups
(for example: Base plan + Night package).
Lower number = higher priority.

Step 4: Optional Restrictions (radgroupcheck):

radgroupcheck — “Group-Based Conditions / Restrictions”

This table is used to define validation checks and conditions on a service group.
While radgroupreply defines what to give, radgroupcheck defines what to check before assigning the group’s services — for example, simultaneous use limits, time restrictions (e.g., only allow login between certain hours), expiration settings, or NAS filtering conditions.
For ISPs, this is where you enforce business logic like “only 1 session at a time” (Simultaneous-Use), “access allowed only at night,” or other policy limits that depend on subscriber behavior or timing.

Real ISP Example of radgroupcheck

In real ISP deployments, radgroupcheck is typically used for:

• Night packagesWeekend plans
• Ramadan offers
• Session limits
• Access control per NAS

Example: Prevent account sharing

INSERT INTO radgroupcheck (groupname, attribute, op, value)
VALUES ('10MB', 'Simultaneous-Use', ':=', '1');

Example: Night-only package

INSERT INTO radgroupcheck (groupname, attribute, op, value)
VALUES ('NIGHT-10MB', 'Login-Time', ':=', 'Al0100-0800');

---

How It Works in Real ISP Flow:

When zaib logs into PPPoE, this happens:

🔍Authentication Check

FreeRADIUS checks:

👉 radcheck
Is password correct?

✔ Yes → continue

📦 Package Lookup

FreeRADIUS checks:

👉 radusergroup
Which package?

→ 10MB

🚀 Service Applied

FreeRADIUS checks:

👉 radgroupreply

Finds:

→ 10M/10M

Sends to MikroTik.

MikroTik applies speed.

Done ✔

Role of MikroTik in This Design:

FreeRADIUS does not enforce speed. It only sends policy attributes.
When FreeRADIUS sends:

• Mikrotik-Rate-Limit = 10M/10M

MikroTik dynamically creates a simple queue for the PPP session based on the RADIUS reply.

So:

• FreeRADIUS = Policy Brain
• MikroTik = Enforcement Engine

---

Visual Flow:

Subscriber authentication and policy enforcement flow in a MikroTik + FreeRADIUS ISP deployment

---

Final Result:

All centrally managed.

---

Testing via RADCLIENT:

To verify group assignment during live login:

Run:

freeradius -X

You should see:

Found group 10MB
Mikrotik-Rate-Limit := 10M/10M

root@radius:~# echo "User-Name=zaib,User-Password=abc123" | radclient -x localhost:1812 auth testing123
Sent Access-Request Id 112 from 0.0.0.0:58903 to 127.0.0.1:1812 length 62
Message-Authenticator = 0x
User-Name = "zaib"
User-Password = "abc123"
Cleartext-Password = "abc123"
Received Access-Accept Id 112 from 127.0.0.1:1812 to 127.0.0.1:58903 length 53
Message-Authenticator = 0xb8ae569d527842c659e6be96831fccc6
Mikrotik-Rate-Limit = "10M/10M"

---

Scaling Example

Let’s assume:
500 users belong to the 10MB package.
To upgrade:
10MB → 15MB

You update only:
radgroupreply
Instead of modifying 500 individual user entries.

In large ISP environments, this enables bulk upgrades without touching individual subscriber records. This becomes critical during:

• Bandwidth revisions
• Promotional upgrades
• Seasonal packages

In real ISP environments, users often belong to multiple groups
(e.g. Base Plan + Night Plan).

Group priority determines which policy is applied first. This is the operational advantage of group-based design.

---

Understanding radreply and Why ISPs Should Avoid Using It for Services:

So far we covered:

But there is one more table that often creates confusion:

👉 radreply
(👉 radreply is processed before groups)

So…

• 👉 What is radreply
• 👉 When to use it
• 👉 Why NOT to use it for packages in large ISPs

What is radreply?

radreply is used to assign reply attributes directly to a user. These attributes are processed before group-level policies.

Meaning:

Instead of assigning service via group… You attach service directly to username.

Example:

INSERT INTO radreply (username, attribute, op, value)
VALUES ('zaib', 'Mikrotik-Rate-Limit', ':=', '10M/10M');

Now:

User zaib gets 10Mbps > directly. No group involved.

Important Behavior of radreply:

FreeRADIUS evaluates radreply before group policies. This means per-user attributes may override group policies. This means that if the same attribute exists in both radreply and radgroupreply, the user-level value will take precedence. This is a common cause of speed mismatch issues in production ISP deployments.

If the same attribute exists in both:

• radreply
• radgroupreply

Then radreply may override the group setting. This can result in:

• Inconsistent speeds
• Debugging complexity

Which is why ISPs avoid using radreply for standard packages.

Why This is Bad for ISPs at Scale:

This works fine when you have:

• ✔ 10 users
• ✔ 20 users

But becomes a disaster when you have:

• ❌ 500 users
• ❌ 2000 users

Because now:
Each user carries service logic.

Real ISP Problem:

Let’s say:
You want to upgrade:

All 10MB users → 15MB

If you used radreply:

You must update every user row individually.

Nightmare 😅

radreply = Per User Logic

This is:

Group-Based Model = Scalable:

Correct ISP design is:

User → radusergroup → Group → radgroupreply → Service

Instead of:

User → radreply → Service

radreply vs Group Model

Service logic location in RADIUS architecture

Real-World Analogy:

Think like a cable operator in Karachi.

radreply model:

Every customer has a custom package manually written.

Group model:

You create packages like:

• 10MB
• 20MB
• Night Unlimited

And assign users to them. Much cleaner ✔

Operational Impact:

Best Practice for Large ISPs:

Use:

Avoid putting:

• ❌ Speed
• ❌ Time
• ❌ FUP
• ❌ Suspension

inside radreply

When Should You Use radreply?

radreply is best suited for exceptions, not standard packages.
Valid use cases:

• Static IP assignment
• VIP customers
• Enterprise custom policies

Example:

• Framed-IP-Address := 203.x.x.x

Avoid using radreply for:

• Speed plans
• FUP policies
• Time-based packages

---

Final Recommendation

Operational Benefits for ISPs:

Group-based design enables:

✔ Bulk upgrades
✔ Seasonal packages (Ramadan / Night)
✔ Billing integration
✔ Faster provisioning

Without editing individual users.

For growing ISPs:

👉 radreply = individual customization
👉 radgroupreply = business packages

And scalable networks always use packages.

---

Real-World Benefit for ISPs:

This model helps when:

✔ Customers upgrade frequently
✔ Seasonal packages launched
✔ Night / Ramadan offers needed
✔ Integration with billing required

Instead of manually editing users (which becomes impossible after 500+ customers), you manage services professionally.

---

Best Practice Summary:

---

Coming Next:

In upcoming posts, we’ll cover:

👉 Night packages
👉 Ramadan offers
👉 FUP (data limit plans)
👉 Speed boost systems

All using MikroTik + FreeRADIUS.

Handling Stale PPPoE Sessions in MikroTik + FreeRADIUS

---

(Exact File Locations, unlag Placement, SQL Ownership, and Cron Responsibilities)

• Author: Syed Jahanzaib ~A Humble Human being! nothing else 
• Platform: aacable.wordpress.com
• Category: Corporate Offices / DHCP-DNS Engineering
• Audience: Systems Administrators, IT Support, NOC Teams, Network Architects

 Disclaimer & Note on Writing Style

Every network environment is unique. A solution that works effectively in one infrastructure may require modification in another. Readers are strongly encouraged to understand the underlying concepts and adapt the guidance according to their own architecture, operational policies, and risk tolerance.

Blind copy-paste implementation without proper validation, testing, and change management is never recommended , especially in production environments. Always ensure proper backups and risk assessment before applying any configuration.

The content shared here is based on hands-on experience from real-world deployments, ISP environments, lab testing, and continuous learning. While I strive for technical accuracy, no technical implementation is entirely free from the possibility of error. Constructive discussion and alternative approaches are always welcome.

Due to professional commitments, it is not always feasible to publish highly detailed or multi-part write-ups. The technical logic and implementation details are written based on my own practical experience. AI tools such as ChatGPT are used only to refine grammar, structure, and presentation — not to generate the core technical concepts.

This blog is not intended for client acquisition or follower growth. It exists solely to share practical knowledge and real-world experience with the community.

Thank you for your understanding and continued support.

---

Introduction

In PPPoE deployments built on MikroTik NAS and FreeRADIUS with an SQL backend, stale accounting sessions are an unavoidable operational reality. These sessions typically arise when accounting stop packets are never received due to NAS reboots, power failures, access link disruptions, or transport issues between the NAS and the RADIUS server.

When left unverified, such stale records directly impact Simultaneous-Use enforcement, resulting in legitimate users being blocked with “user already online” conditions despite having no active session. Addressing this problem requires more than periodic record deletion; it demands a controlled and verifiable method to determine whether a session is truly active.

This article presents a production-safe approach to PPPoE session verification by leveraging accounting activity, SQL-based validation, unlang logic, and scheduled cleanup processes. The objective is to ensure accurate session state, reliable Simultaneous-Use enforcement, and clean accounting data without compromising audit integrity or service availability.

Why this happens (root cause)

• MikroTik sends Accounting-Start
• Every 5 minutes it sends Interim-Update
• FreeRADIUS inserts a row in radacct with acctstoptime = NULL
• NAS reboots / loses connectivity
• Accounting-Stop is never sent
• FreeRADIUS still thinks the session is active
• Simultaneous-Use = 1 blocks re-login

👉 RADIUS cannot guess that the user is gone unless you tell it how.

What ISPs do in real production

There are three layers usually combined:

1️⃣ Interim-Update timeout validation (MOST IMPORTANT)

This is the primary verification mechanism.

Logic

If a session has not sent an Interim-Update for X minutes, it is considered dead, even if acctstoptime is NULL.

Why it works

• A live session must send interim updates
• If NAS rebooted, updates stop
• This is proof, not assumption

Typical ISP rule

If last interim update > 2 × Acct-Interim-Interval → session is dead

Example:

• Interim interval = 5 minutes
• Timeout = 10–15 minutes

SQL condition (verification)

SELECT username, nasipaddress, acctsessionid
FROM radacct
WHERE acctstoptime IS NULL
AND TIMESTAMPDIFF(MINUTE, acctupdatetime, NOW()) > 15;

If this condition is true → session is NOT alive.

Proper cleanup (controlled)

UPDATE radacct
SET acctstoptime = acctupdatetime,
    acctterminatecause = 'NAS-Reboot'
WHERE acctstoptime IS NULL
AND TIMESTAMPDIFF(MINUTE, acctupdatetime, NOW()) > 15;

✅ This is production-safe
❌ This is NOT blind deletion
✔ This is based on verifiable inactivity

2️⃣ NAS reachability check (secondary verification)

Many ISPs also verify NAS status before cleanup.

Typical checks

• Ping NAS IP
• SNMP availability
• RouterOS API availability

Logic

• IF NAS unreachable
• AND session has no interim updates
• THEN mark sessions as dead

This avoids killing sessions when:

• SQL delayed
• Temporary RADIUS lag

Example logic (pseudo)

if ping NAS == fail:

cleanup stale sessions for that NAS

This is often implemented in cron scripts.

3️⃣ MikroTik-side protections (VERY IMPORTANT)

1. a) Proper Acct-Interim-Interval

On MikroTik:

/radius
set accounting=yes interim-update=5m

Never leave it default or disabled.

1. b) Send Stop on reboot (best-effort)

RouterOS already tries, but you can improve reliability:

• Use stable storage
• Avoid power cuts without UPS

Still, RADIUS must assume stops can be lost.

4️⃣ What ISPs DO NOT do (bad practices)

❌ Delete all acctstoptime IS NULL blindly
❌ Disable Simultaneous-Use
❌ Allow multiple sessions to avoid complaints
❌ Manual cleanup by support staff

These cause:

• Abuse
• Multiple logins
• Incorrect billing
• Legal/audit issues

5️⃣ Recommended production-grade approach (summary)

Layer	Purpose	Mandatory
Interim timeout check	Session verification	✅ YES
NAS reachability check	Extra safety	⚠ Recommended
Controlled SQL cleanup	Session release	✅ YES
Simultaneous-Use=1	Abuse prevention	✅ YES

6️⃣ Industry-standard wording (how ISPs define it)

“A session is considered active only if periodic accounting updates are received within the defined timeout window. Absence of updates implies session termination due to NAS failure or connectivity loss.”

This is exactly how large ISPs justify cleanup during audits.

Final Answer to core question!

How do ISPs verify if user is actually not live on NAS?

✅ By absence of Interim-Update packets within a defined time window, optionally combined with NAS reachability checks.

There is no other reliable method in RADIUS.

This article documents a fully production-aligned design, explicitly mapping:

• Which logic runs inside FreeRADIUS
• Which logic runs via SQL
• Which logic runs via cron
• Exact file names and locations for every decision

No unnamed logic. No invisible automation.

Environment Assumptions

• FreeRADIUS 3.x
• MySQL / MariaDB backend
• MikroTik PPPoE NAS
• Interim-Update = 5 minutes
• Simultaneous-Use = 1

Session State Model (Conceptual)

State	Meaning	Where enforced
ACTIVE	Interim updates arriving	FreeRADIUS SQL
STALE	Interim missing, NAS alive	Cron
CLOSED	Verified termination	FreeRADIUS unlang / Cron

 

---

Database Layer (Schema Ownership)

Location

• Database: radiusdb
• Table   : radacct

---

Schema Extension (run once)

Concept/Logic/Purpose: Extends the radacct table to explicitly track session state and last known MikroTik session timers. This enables deterministic handling of stale, resumed, and terminated sessions without relying on assumptions.

ALTER TABLE radacct
ADD COLUMN session_state ENUM('ACTIVE','STALE','CLOSED') DEFAULT 'ACTIVE',
ADD COLUMN last_acct_session_time INT DEFAULT 0,
ADD INDEX idx_state_update (session_state, acctupdatetime),
ADD INDEX idx_nas_user (nasipaddress, username);

Responsibility:
✔ Stores session truth
✔ No logic, only state

SQL Query Ownership (queries.conf)

• Concept/Logic/Purpose: Centralizes all accounting-related SQL logic in a single, predictable location managed by FreeRADIUS. This separation ensures that runtime logic (unlang) and data manipulation (SQL) remain clean and auditable.

File Location

• /etc/freeradius/mods-config/sql/main/mysql/queries.conf

All SQL below lives only in this file.

---

Accounting-Start Query

• Used by: FreeRADIUS accounting {}
• Concept/Logic/Purpose: Creates a new accounting record when a PPPoE session is first established.
  This marks the authoritative beginning of a user session and initializes all tracking fields.

accounting_start_query = "
INSERT INTO radacct (
  acctsessionid, acctuniqueid, username, nasipaddress,
  acctstarttime, acctupdatetime, acctstoptime,
  acctsessiontime, last_acct_session_time, session_state
) VALUES (
  '%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
  '%{User-Name}', '%{NAS-IP-Address}',
  NOW(), NOW(), NULL,
  '%{Acct-Session-Time}', '%{Acct-Session-Time}', 'ACTIVE'
)"

---

Interim-Update Query

• Used by: FreeRADIUS accounting {}
• Concept/Logic/Purpose: Periodically refreshes session counters and timestamps to confirm that the user is still actively connected. It also safely resumes sessions after temporary NAS–RADIUS connectivity loss.

accounting_update_query = "
UPDATE radacct
SET acctupdatetime = NOW(),
    acctsessiontime = '%{Acct-Session-Time}',
    last_acct_session_time = '%{Acct-Session-Time}',
    session_state = 'ACTIVE'
WHERE acctsessionid = '%{Acct-Session-Id}'
AND nasipaddress = '%{NAS-IP-Address}'
AND acctstoptime IS NULL
"

---

Accounting-Stop Query

• Used by: FreeRADIUS accounting {}
• Concept/Logic/Purpose: Close session normally. Closes a session only when FreeRADIUS receives an explicit stop notification from the NAS. This represents a verified and intentional session termination.

accounting_stop_query = "
UPDATE radacct
SET acctstoptime = NOW(),
    acctterminatecause = '%{Acct-Terminate-Cause}',
    session_state = 'CLOSED'
WHERE acctsessionid = '%{Acct-Session-Id}'
AND nasipaddress = '%{NAS-IP-Address}'
AND acctstoptime IS NULL
"

---

NAS Reboot Cleanup Query

• Used by: FreeRADIUS unlang only
• Concept/Logic/Purpose: Close sessions after verified NAS reboot. Force-closes all open sessions for a NAS only after a reboot has been positively detected. This prevents ghost sessions while preserving accounting accuracy.

nas_reboot_cleanup_query = "
UPDATE radacct
SET acctstoptime = acctupdatetime,
    acctterminatecause = 'NAS-Reboot',
    session_state = 'CLOSED'
WHERE nasipaddress = '%{NAS-IP-Address}'
AND acctstoptime IS NULL
"

---

Last Session-Time Lookup Query

• Used by: FreeRADIUS unlang only
• Concept/Logic/Purpose: Detect MikroTik reboot Retrieves the previously stored Acct-Session-Time to detect timer regression.This is the primary mechanism used to infer MikroTik NAS reboots reliably.

nas_last_session_time_query = "
SELECT last_acct_session_time
FROM radacct
WHERE acctsessionid = '%{Acct-Session-Id}'
AND nasipaddress = '%{NAS-IP-Address}'
AND acctstoptime IS NULL
LIMIT 1
"

---

Simultaneous-Use Count Query

• Used by: SQL authorize stage
• Concept/Logic/Purpose: Enforce single login. Counts only ACTIVE sessions to enforce single-login policies correctly. STALE sessions are excluded to avoid false user lockouts during temporary failures.

simul_count_query = "
SELECT COUNT(*)
FROM radacct
WHERE username = '%{User-Name}'
AND acctstoptime IS NULL
AND session_state = 'ACTIVE'
AND nasipaddress != '%{NAS-IP-Address}'
"

---

FreeRADIUS unlang Logic (Runtime Decisions)

Implements real-time decision making based on live accounting packets. This layer handles session creation, resumption, and NAS reboot detection without relying on timers.

Once stale sessions are identified at the database level, FreeRADIUS must use this verified state during authorization. This is where unlang becomes critical — it allows dynamic decision-making based on real-time session validity rather than raw record existence.

File Location

• /etc/freeradius/sites-enabled/default

Section

• server default → accounting { }

Responsibility

• ✔ Real-time decisions
• ✔ Packet-driven logic
• ❌ No time-based cleanup

Complete accounting{} block

# /etc/freeradius/sites-enabled/default
accounting {
  if (Acct-Status-Type == Start) {
    sql
    ok
  }
  if (Acct-Status-Type == Interim-Update) {
    update request {
      Tmp-Integer-0 := "%{sql:nas_last_session_time_query}"
    }
    if (&Tmp-Integer-0 && (&Acct-Session-Time < &Tmp-Integer-0)) {
      radiusd::log_warn("NAS reboot detected %{NAS-IP-Address}")
      sql:nas_reboot_cleanup_query
    }
    sql
    ok
  }
  if (Acct-Status-Type == Stop) {
    sql
    ok
  }
}

---

Cron Layer (Time-Based Maintenance Only)

Practical Example

Assume the NAS is configured to send Interim-Updates every 5 minutes.

• Session start time: 10:00
• Last Interim-Update received: 10:25
• Current time: 10:45

Since no updates were received for 20 minutes (4× the interim interval), the session can be confidently classified as stale and excluded from active session counts.

NOTE: CRON intervals

In production ISP environments, such cleanup jobs are typically executed every 5 to 15 minutes. Running them too frequently can increase database load, while long intervals delay user recovery. The exact timing should be aligned with the configured Interim-Update interval.

---

Cron Script #1 — Mark STALE Sessions

File:

• /usr/local/sbin/radius/mark-stale-sessions.sh
• Concept/Logic/Purpose: Periodically identifies sessions that stopped sending Interim-Updates but may still be valid. Sessions are marked STALE instead of being disconnected, allowing safe recovery.
• Does NOT disconnect users

#!/bin/bash
mysql -u radius -p'PASSWORD' radiusdb <<EOF
UPDATE radacct
SET session_state = 'STALE'
WHERE acctstoptime IS NULL
AND session_state = 'ACTIVE'
AND TIMESTAMPDIFF(MINUTE, acctupdatetime, NOW()) > 15;
EOF

Note: This query intentionally avoids deleting records blindly. Instead, it relies on time-based verification to determine whether a session has genuinely stopped sending updates. This approach prevents accidental cleanup of slow or temporarily delayed sessions and ensures billing and audit accuracy.

Crontab Entry:

• */5 * * * * /usr/local/sbin/radius/mark-stale-sessions.sh

---

Cron Script #2 — Cleanup Lost Sessions

Concept/Logic/Purpose: Final cleanup for dead accounting. Performs conservative, time-based cleanup of sessions that will never return.This protects database integrity without interfering with active or recoverable sessions.

File:

• /usr/local/sbin/radius/cleanup-lost-sessions.sh

#!/bin/bash
mysql -u radius -p'PASSWORD' radiusdb <<EOF
UPDATE radacct
SET acctstoptime = acctupdatetime,
    acctterminatecause = 'Lost-Accounting',
    session_state = 'CLOSED'
WHERE acctstoptime IS NULL
AND TIMESTAMPDIFF(MINUTE, acctupdatetime, NOW()) > 120;
EOF

Crontab Entry:

• 0 * * * * /usr/local/sbin/radius/cleanup-lost-sessions.sh

What Runs Where (Zero Ambiguity)

Logic	Location
Start / Stop	sites-enabled/default → accounting {}
Interim resume	sites-enabled/default → accounting {}
NAS reboot detection	sites-enabled/default → accounting {}
STALE marking	mark-stale-sessions.sh
Final cleanup	cleanup-lost-sessions.sh
Simultaneous-Use	queries.conf → simul_count_query

What MUST NOT Exist

❌ Anonymous cron entries
❌ Logic without file ownership
❌ Session deletion without cause
❌ Time-based reboot assumptions

---

 

Session Resume After Temporary Accounting Outage

In our implementation:

• Sessions are marked as STALE, not closed
• acctstoptime remains NULL
• When connectivity restores and the same Acct-Session-Id sends Interim-Updates again:
  • FreeRADIUS updates the existing record
  • session_state is set back to ACTIVE
  • The STALE condition is effectively cleared
  • Accounting continues normally

This is exactly the correct behavior.

Step-by-step (what really happens in your setup)

1️⃣ Interim updates stop (temporary NAS↔RADIUS issue)

• Cron runs:
  • mark-stale-sessions.sh
• Result in radacct:

acctstoptime = NULL

session_state = STALE

👉 Session is not closed, only flagged as stale.

2️⃣ NAS–RADIUS connectivity is restored

• MikroTik resumes sending:

Acct-Status-Type = Interim-Update

Acct-Session-Id = SAME

3️⃣ FreeRADIUS processes Interim-Update

Inside:

•  /etc/freeradius/sites-enabled/default

→ accounting { }

Flow:

1. nas_last_session_time_query finds the existing row
   (acctstoptime IS NULL ✔)
2. NAS reboot check:
   • Acct-Session-Time has not reset
   • No reboot detected ✔
3. accounting_update_query executes:

UPDATE radacct
SET
acctupdatetime = NOW(),
acctsessiontime = ...,
last_acct_session_time = ...,
session_state = 'ACTIVE'
WHERE acctsessionid = ?
AND acctstoptime IS NULL;

4️⃣ Result in database

Field	Value
acctstoptime	NULL
session_state	ACTIVE
counters	updated
accounting	continuous

👉 The STALE flag is removed automatically by the Interim-Update.

What does NOT happen (important)

❌ No new row is created
❌ No duplicate session
❌ No accounting reset
❌ No Simultaneous-Use false block

Why this works (core principle)

FreeRADIUS can safely resume a session only if acctstoptime was never set.

OUR  design respects this rule.

That is the entire reason STALE exists as a state.

One-line confirmation…

Yes, in this design sessions are marked as STALE (not closed), and when the same accounting session resumes, FreeRADIUS continues updating the existing record and automatically restores the session to ACTIVE.

One operational warning

• If a session is ever closed (acctstoptime set), it can never be resumed — only restarted.

---

🛡️ Audit Justification Points

Audit Rationale: This session model is designed to ensure that PPPoE session closures are only recorded when there is explicit evidence of termination, either through an accounting stop, verified NAS reboot, or prolonged accounting silence beyond operational thresholds. Temporary outages do not constitute termination, preserving billing integrity and avoiding false positive disconnects.

To make this useful for Enterprise/ISP managers who need to justify these changes to auditors, add a section titled “Audit & Compliance: Why This Matters”.

Audit & Compliance Justification Implementing automated stale session handling is not just an operational fix; it is a data integrity requirement.

• AAA Data Integrity (Authentication, Authorization, Accounting): Auditors require that Accounting logs accurately reflect user usage. Leaving stale sessions “open” (with AcctStopTime IS NULL) falsifies usage duration records, leading to incorrect billing disputes and “ghost” data consumption logs.

• Revenue Assurance: For prepaid or quota-based ISPs, stale sessions prevent the system from calculating the final session volume. By forcing a closure based on the last known Interim-Update, we ensure that the billable data matches the actual network activity, preventing revenue leakage or customer overcharging.

• Traceability & Non-Repudiation: Our customized SQL queries introduce a acctterminatecause = 'NAS-Reboot' flag. This provides a distinct audit trail, differentiating between a user logging off (User-Request) and a system correction (System-Cleanup), which is critical for forensic analysis during network outages.*

When this logic does NOT apply

• NAS firmware that does not send Interim-Update reliably
• Cases where NAS sends stale Interim-Updates after long outages
• Networks with asymmetric paths and intermittent packet loss

In such cases you may need longer STALE thresholds or secondary reachability checks (SNMP/ICMP).

📌 NOC Operational Expectation

• STALE threshold: 15 minutes
• Final cleanup: 120 minutes
• Alerts for NAS reboot events should be integrated into monitoring (Syslog/SNMP)
• Any unexpected growth in STALE counts must be investigated

⃣General View for Non-Technical Reader

In simple terms, sessions are only ended when there is verified evidence of exit. Temporary network issues are handled without affecting service continuity, ensuring users don’t lose sessions or get billed incorrectly.

---

Important Caveats

• If Interim-Updates are disabled or misconfigured on the NAS, this method will not work correctly.
• Database latency or replication delay must be considered in large deployments.
• Multi-NAS environments should ensure session verification is NAS-aware to avoid false positives.

Final Operational Principle

Stale PPPoE sessions are not a database anomaly but a natural consequence of real-world network behavior. Treating them as such requires session verification based on accounting activity rather than the mere presence of an open record.

By relying on Interim-Update freshness, SQL-based validation, and unlang-driven authorization logic, ISPs can accurately distinguish between active and defunct sessions. This method ensures Simultaneous-Use enforcement remains fair, prevents unnecessary customer lockouts, and preserves accounting accuracy for billing and audit purposes.

When implemented correctly and aligned with NAS interim update intervals, this approach provides a scalable and production-ready solution for managing PPPoE session state in MikroTik and FreeRADIUS environments.

---

© Syed Jahanzaib — aacable.wordpress.com

 

FREERADIUS WITH MIKROTIK – Part #13 – FreeRADIUS with MikroTik – Detecting User Device Vendor Using MAC OUI

 

FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s Click here to read more on FR tutorials …

---

 Disclaimer (Please Read)

Every network environment is different. A solution that works in one setup may not be suitable for another. Readers are strongly encouraged to understand the underlying logic and adapt or modify solutions according to their own network design and operational requirements. Blind copy-paste without understanding is never recommended.

I would also like to humbly clarify that I do not consider myself an expert in this domain. I hold only limited certifications across MikroTik, Cisco, Linux, virtualization and Windows. However, I have worked with real-world core networks and spend a significant amount of time reading, researching, testing, and learning.

The content shared here is based primarily on hands-on experience, self-learning, and practical experimentation, not formal training alone. If I do not know something, I make it a point to study it before forming an opinion.

While I always strive for accuracy, mistakes are possible—as with any technical work. Please do not assume that every post is 100% perfect. I continuously learn from errors, improve my understanding, and share knowledge with the intention of helping others in the community.

Regard’s
Syed Jahanzaib

---

How to Enrich FreeRADIUS Accounting with MAC Vendor Lookup for MikroTik Networks!

In ISP and enterprise RADIUS deployments, knowing the device vendor connecting to your network provides valuable operational insight:

• Detect home routers used behind PPPoE accounts
• Identify abnormal device patterns
• Generate vendor-based reports
• Support fraud detection or policy enforcement

In this guide, we implement a production-ready MAC OUI lookup system using:

• FreeRADIUS 3.x
• MikroTik NAS
• MySQL/MariaDB backend
• radacct accounting table

This version improves earlier approaches by adding:

✔ Duplicate protection
✔ Error handling
✔ Normalization
✔ Better database design
✔ Scalability considerations

Where the MAC Address Comes From

When MikroTik sends accounting packets to FreeRADIUS, the client MAC appears in the attribute:

• Calling-Station-Id
• Example log output:
• Calling-Station-Id = “70:54:D2:16:A5:D9”

In this article we capture the MAC address from FreeRADIUS accounting and enrich user profiles with device vendor information using a local MAC vendor database (OUI lookup). This helps with reporting, policy enforcement, and detecting unauthorized device types (e.g., routers used on residential ISPs).

We have a generic freeradius based billing system in place. in RADACCT table, we have a trigger that fire-up after successfull connection made from user device & it inserts user device MAC address in radcheck table for MAC validation & restriction.

What RADIUS Sends for MAC

• MikroTik typically sends the Calling-Station-ID attribute containing the station MAC.

Why MAC Vendor Lookup

Before showing code, explain:

• MAC address first 3 octets = Organizationally Unique Identifier (OUI)
• OUI maps to vendor name (Apple, Samsung, etc.)
• Local lookup avoids dependency on external APIs

Understanding MAC OUI

A MAC address:

• 70:54:D2:16:A5:D9
• The first 3 octets:
• 70:54:D2

This is called the OUI (Organizationally Unique Identifier) and maps to the device manufacturer.

We will build a local lookup database using IEEE’s official OUI list.

* Requirement:

For better management , control & reporting purposes we want to add more checks and controls by adding user calling device VENDOR name in an additional column so that we can have idea what devices are most common in our network, or to detect any device that is prohibited by the company SOP or policy example commercial grade routers to minimize misuse of our residential services. Based on this reporting  we can prohibit connecting these devices as well if required.

To fulfill this we will do following

1. Create a TRIGGER on RADACCT that will executes after INSERT record (like when user will connect to system successfully)
2. Create DB and upload vendors mac address data so that we can query for vendor name locally (alternate approach is to simple use any Perl or bash script to look up vendor name dynamically on the fly form the internet)

---

OK before creating TRIGGER we must learn or understand our USER table where mac related will be added or update. One example for such table is showed below …

root@radius:/temp# mysql -uroot -pSQLPASS -e "use radius; describe users;"

+-------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+-------------------+-----------------------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| username | varchar(128) | NO | UNI | NULL | |
| password | varchar(32) | NO | | NULL | |
| firstname | text | NO | | NULL | |
| lastname | text | NO | | NULL | |
| email | text | NO | | NULL | |
| mobile | text | NO | | NULL | |
| cnic | text | NO | | NULL | |
| srvname | text | NO | | NULL | |
| srvid | int(3) | NO | | NULL | |
| expiration | date | YES | | NULL | |
| mac | varchar(30) | NO | | NULL | |
| macvendor | varchar(128) | NO | | NULL | |
| bwpkg | varchar(256) | NO | | NULL | |
| pool | varchar(128) | YES | | other | |
| is_enabled | int(1) | NO | | NULL | |
| is_days_expired | int(1) | NO | | NULL | |
| is_qt_expired | int(1) | NO | | NULL | |
| is_uptime_expired | int(1) | NO | | NULL | |
| qt_total | varchar(32) | NO | | NULL | |
| qt_used | varchar(20) | NO | | NULL | |
| uptime_limit | varchar(20) | NO | | NULL | |
| uptime_used | varchar(32) | NO | | NULL | |
| owner | text | NO | | NULL | |
| vlanid | varchar(32) | NO | | NULL | |
| createdon | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------------+--------------+------+-----+-------------------+-----------------------------+

In this post we have interest in two columns named mac and macvendor

---

Now we will create TRIGGER with our so called magical code 😉

1- TRIGGER for radacct table

CREATE TRIGGER `chk_mac_after_insert` AFTER INSERT ON `radacct`
FOR EACH ROW BEGIN
# Check if user mac is already added in radcheck table,
SET @mac = (SELECT count(*) from radcheck where username=New.username and attribute='Calling-Station-ID');
# If there is no entry for mac, then update mac in radcheck table, so that in future only this mac will be able to connect with that username
IF (@mac = 0) THEN
INSERT into radcheck (username,attribute,op,value) values (NEW.username,'Calling-Station-ID',':=',NEW.callingstationid);
# add mac in users table for general record purpose
UPDATE users SET mac = NEW.callingstationid where username = NEW.username;
# trim mac for first 3 strings to detect vendor company
SET @mactrim = (select LEFT(mac, 8) from users where username=New.username);
# get vendor name from mac db table
SET @macvendor1 = (select vendor from macdb where oui=@mactrim);
# Update vendor name in user table
UPDATE users SET macvendor=@macvendor1 where username = NEW.username;
END IF;
END
#SYED Jahanzaib - 

---

2- Create MAC Address Database for VENDOR Lookup

This is a bit debatable part, I prefer to have local database for vendor mac addresses so that all lookup should be done locally rather then using any API for mac lookup. But if you want to prefer using internet base lookup , then you can use perl, bash or any other method to do lookup one example for internet lookup is as follows …

curl http://api.macvendors.com/70-54-D2-16-A5-D9
#OUTPUT
PEGATRON CORPORATION

MACDB.SQL

Download macdb.sql from my google drive and import it in RADIUS DB

https://drive.google.com/drive/folders/1WwQTsK2WegT6T7IFH19IDWZ3-L_lV22v

If no error occurs you will be seeing a new table named macdb with all vendors names as well 🙂

 

to get trimmed OUI, use following

mysql -uroot -pSQLPASS --skip-column-names -s -e "USE radius; SELECT LEFT(mac, 8) FROM users;"

Now connect any user as normal, and see the mysql.log file

2018-02-20T06:41:04.593739Z 24 Query INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress, acctstartdelay, acctstopdelay, xascendsessionsvrkey) VALUES ('81100003', '702d22ac0a080f57', 'zaib', '', '101.11.11.253', '9', 'Ethernet', '2018-02-20 11:41:04', NULL, '0', 'RADIUS', '', '', '0', '0', 'service1', '00:0C:29:B9:D8:A0', '', 'Framed-User', 'PPP', '192.168.50.255', '0', '0', '')
#As soon we receive entry for INSERT, TRIGGER will fire-up, see below log
2018-02-20T06:41:04.594676Z 24 Query SET @mac = (SELECT count(*) from radcheck where username=New.username and attribute='Calling-Station-ID')
2018-02-20T06:41:04.594871Z 24 Query INSERT into radcheck (username,attribute,op,value) values (NEW.username,'Calling-Station-ID',':=',NEW.callingstationid)
2018-02-20T06:41:04.595020Z 24 Query UPDATE users SET mac = NEW.callingstationid where username = NEW.username
2018-02-20T06:41:04.595151Z 24 Query SET @mactrim = (select LEFT(mac, 8) from users where username=New.username)
2018-02-20T06:41:04.595256Z 24 Query SET @macvendor1 = (select vendor from macdb where oui=@mactrim)
2018-02-20T06:41:04.607786Z 24 Query UPDATE users SET macvendor=@macvendor1 where username = NEW.username

& FINALLY we will see records in USERS table that will be displayed on FRONTEND 🙂

---

Security & Real-World Limitations

Be aware:

• iOS/Android use MAC randomization
• OUI may not always reflect actual manufacturer
• Some vendors share OUI blocks

Important for credibility. Do not rely on this method for strict device enforcement — it is best used for reporting and analytics.

Optional: Move Logic to FreeRADIUS Instead of SQL

For high-scale ISP, Better approach is:

• Use unlang policy
• Call external script
• Cache results

Triggers under heavy load can slow large radacct tables.

Performance Considerations (Important for ISPs)

If you have:

• 10,000+ sessions daily
• High accounting inserts

Then:

• Ensure mac_oui.oui is indexed
• Ensure user_device_info.username is indexed
• Monitor trigger overhead

Interim Updates Flooding Trigger

If needed, restrict execution:

IF NEW.acctstarttime IS NOT NULL THEN

So lookup runs only on session start.

📊 Use Cases for ISP Reporting

Once data is enriched, you can generate:

• Top 10 device vendors
• Vendor-based bandwidth usage
• Detection of CPE routers behind PPPoE
• Suspicious device pattern alerts

This integrates well with:

• Grafana
• Metabase
• Custom billing dashboards

✅ Conclusion

By integrating OUI lookup directly into FreeRADIUS accounting via MySQL trigger, we gain:

✔ Automatic vendor detection
✔ Clean database architecture
✔ No dependency on external APIs
✔ Scalable reporting capability

This approach is suitable for:

• ISP billing systems
• Enterprise RADIUS deployments
• Network analytics environments