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