Syed Jahanzaib Personal Blog to Share Knowledge !

February 20, 2018

FREERADIUS WITH MIKROTIK – Part #13 – Detecting user device vendor based on MAC address

Filed under: freeradius — Tags: , , , , , — Syed Jahanzaib / Pinochio~:) @ 11:52 AM

fre2

mac-address

1- identify vendor from mac

 

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

Disclaimer! This is important!

Every Network is different , so one solution cannot be applied to all. Therefore try to understand logic & create your own solution as per your network scenario. Just dont follow copy paste.

If anybody here thinks I am an expert on this stuff, I am NOT certified in anything Mikrotik/Cisco/Linux or Windows. However I have worked with some core networks and I read , research & try stuff all of the time. So I am not speaking/posting about stuff I am formerly trained in, I pretty much go with experience and what I have learned on my own. And , If I don’t know something then I read & learn all about it.

So , please don’t hold me/my-postings to be always 100 percent correct. I make mistakes just like everybody else. However – I do my best, learn from my mistakes and always try to help others

Regard's
Syed Jahanzaib~

* Scenario:

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.

* 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- triger

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

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 🙂

db import successfull.JPG

 

to get trimmed OUI, use following

sourcecode]mysql -uroot -pSQLPASS –skip-column-names -s -e “use radius; select LEFT(mac, 8) from users;”/sourcecode]


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 🙂

1- identify vendor from mac

2 Comments »

  1. […] FREERADIUS WITH MIKROTIK – Part #13 – Detecting user device vendor based on MAC address […]

    Like

    Pingback by Mikrotik with Freeradius/mySQL # Part-1 | Syed Jahanzaib Personal Blog to Share Knowledge ! — February 20, 2018 @ 11:54 AM

  2. python mactosql.sh inserts only 1 last line in db table

    Like

    Comment by Shahzad Khan — March 5, 2018 @ 1:44 AM


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: