Syed Jahanzaib – Personal Blog to Share Knowledge !

October 31, 2017

Mikrotik with Freeradius/mySQL – Auto MAC Binding on 1st Login – Part 4


~ Auto Mac Binding via EXEC / PHP in Freeradius 2.x ~
! From the CORE of FREERADIUS !
Syed jahanzaib



There are others parts too, look at part-1 for listing, i will update only part-1 listing

Personnel Note:

[At end of this guide I used BASH script method. This is efficient and customizable at many extend and can be scheduled to run hourly giving leverage to users. TRIGGER method is also good to auto insert the mac address immediately once new session is made in radacct table of user if there is no mac entry in the radcheck table for his username, in my opinion .Trigger are not recommended if you have thousands of active users connecting .]

This post is just for demonstration purposes. in production environment you should make your own module and add it in proper relevant places. This post contains just minimalist working config to begin with. Make sure to refine it in prd environment.

This is another post about freeradius. My aim is to let people know that creating your own Radius Billing system is not ROCKET SCIENCE. The only thing required is the ultimate passion to achieve the goal & with the proper googling , reading a LOT, understand logic’s, then you can do all on your own. Just wanted to break the image that most of professionals don’t like to share there knowledge. I strongly encourage to read the FR mailing list and Google

OP Requirements:

[ Sort of Wired one 😉 ]

We have a working Freeradius installation. All users can login to mikrotik which verify user account authentication requests via this radius. All working fine. Now OP wants to add Auto MAC binding feature so that when user first time login to NAS, his MAC should auto binds with his account, so next time if he tries to login from another workstation, he must get access denied.

Components used in this guide:

  • Ubuntu 12.4 / x86
  • Freeradius 2.1.10 [Default apt-get installation]
  • MySQL 5.5.47 [Default apt-get installation]

SOLUTION # 1 – [My personnel preferred method]

1# BASH script to add MAC address in radcheck fo FREERADIUS [Zaib Updated this old script at 29-OCT-2019]

I scheduled this bash script to run hourly at some ISP network. It worked flawless and gave good results.

  • It will list Online users from radacct table into file
  • then it will run loop formula, & see which user dont have entry in RADCHECK table for mac validation
  • if not found, then it will add mac entry in RADCHECK (for validation) and in users table as well (for display purposes)
#!/usr/bin/env bash
#set -x
#trap "set +x; set -x" DEBUG
# BASH base script to add MAC for missing users # By Syed Jahanzaib
# Modified on : 25-OCTOBER-2019
# Local Variables
# Mysql credentials
CMD="mysql -u$SQLID --skip-column-names -s -e"
#Table which contain main users information
#Rad user group in which we will update user profile like from 1mb to expired or likewise
# Date Time Variables
DATE=$(date +%d-%m-%Y)
DT_HMS=$(date +'%H:%M:%S')
CURR_HOUR=$(date +%H)
TODAY=$(date +"%Y-%m-%d")
WEEK=`date -d "-1000 days" '+%Y-%m-%d'`
H=$(date +'%-H')

$CMD "use $DB; select username, callingstationid from radacct WHERE acctstoptime IS NULL;" | sort >> $RADACCT_USR_LIST_TMP
# IF no user found , show error and exit - zaib
CHK=`wc -m $RADACCT_USR_LIST_TMP | awk {'print $1}'`
if [ "$CHK" -eq 0 ]; then
echo "No user found who have empty mac , Exiting ..."
exit 1

echo "
- Script Start Time : $DATE $DT_HMS
- Total Users Online : $TOT
- Sleeping 5 Seconds for review ..."
sleep 5
echo " - Now checking online users mac entry in RADACCT table ...
# Apply Count Loop Formula while deleting first line which have junk text
cat $RADACCT_USR_LIST_TMP | while read users
USERNAME=`echo $users | awk '{print $1}'`
MAC_IN_RADACCT=`echo $users | awk '{print $2}' | tr -d ' '`
MAC_CHK_IN_RDCHK_TBL=`$CMD "use $DB; select value from radcheck where username = '$USERNAME' and attribute = 'Calling-Station-Id';"`
if [ -z "$MAC_CHK_IN_RDCHK_TBL" ]; then
echo "**INFO: $USERNAME have no MAC binded in RADCHECK, Now adding $MAC_IN_RADACCT from radacct table ******************
$CMD "use $DB; insert into radcheck (username, attribute, op, value) VALUES ('$USERNAME', 'Calling-Station-Id', ':=', '$MAC_IN_RADACCT');"
$CMD "use $DB; update $TBL set binded_mac_0 = '$MAC_IN_RADACCT' where username = '$USERNAME';"
echo "ALERT: $USERNAME mac already binded."
DATE=$(date +%d-%m-%Y)
DT_HMS=$(date +'%H:%M:%S')
echo "- Total Users Scanned : $TOT
- Script Start Time : $DATE $DT_HMS"
echo "Script Finished here ... Powered by Syed.Jahanzaib"



To fulfill such weird requirements, we have to use external program example PHP program (via exec) which will be executed when user gets connect successfully. It will then look in RADCHECK table for this specific user MAC address value name “Calling-Station-Id”. If it’s unable to find it, then it will add the entry so that next time user will login his MAC will be verified by the CHECKVAL module in freeradius to match the mac address. If there is mac address entry, it will simply ignore and process further , will also print message that “MAC Entry already found – z@iB”

First enable the CHECKVAL module in following file > /etc/freeradius/sites-enabled/default

nano /etc/freeradius/sites-enabled/default

Search & uncomment the checkval module. Save & Exit.

Now edit EXEC module file by

nano /etc/freeradius/modules/exec

Remove all previous contents (if its lab testing otherwise be careful editing this file) & paste following

exec {
wait = yes
program = "/usr/bin/php /temp/checkmac.php %{User-Name} %{Calling-Station-Id}"
input_pairs = request

Save & Exit.

Now create the php program which will be executed by above module.

mkdir /temp
mkdir /temp/checkmac.php
touch /temp/checkmac.php
nano /temp/checkmac.php

and use following to paste make sure to modify relevant details …


checkmac.php contents

 0) {
printf ("MAC Entry already found by ZAiBBBBBBBBBBBBBBBB");
else {
printf ("Seems to be New User, adding its MAC address in table ...");
mysql_query("INSERT into radcheck (UserName, Attribute, op, Value) values ('$argv[1]', 'Calling-Station-Id', ':=', '$argv[2]')");


Start FR in debug mode by freeradius -X and try to login with the test ID from your workstation (or use the radtest or ntradping)

rad_recv: Access-Request packet from host port 42449, id=45, length=188
Service-Type = Framed-User
Framed-Protocol = PPP
NAS-Port = 15728851
NAS-Port-Type = Ethernet
User-Name = "zaib"
Calling-Station-Id = "0C:84:DC:1E:0B:8D"
Called-Station-Id = "service1"
NAS-Port-Id = "ether10"
MS-CHAP-Challenge = 0x49c4549501e07fad5e6dae708bc815ed
MS-CHAP2-Response = 0x0100acaa712e29adad9abb681c5ef666e69300000000000000003cd5a092d7c816de798b7f5d09acba6f04eeed208cd6c19b
NAS-Identifier = "MIKROTIK"
NAS-IP-Address =
# Executing section authorize from file /etc/freeradius/sites-enabled/default
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
[mschap] Found MS-CHAP attributes. Setting 'Auth-Type = mschap'
++[mschap] returns ok
++[digest] returns noop
[suffix] No '@' in User-Name = "zaib", looking up realm NULL
[suffix] No such realm "NULL"
++[suffix] returns noop
[eap] No EAP-Message, not doing EAP
++[eap] returns noop
[exec] expand: %{User-Name} -> zaib
[exec] expand: %{Calling-Station-Id} -> 0C:84:DC:1E:0B:8D
Exec-Program output: Seems to be New User, adding its MAC address in table ...
Exec-Program-Wait: plaintext: Seems to be New User, adding its MAC address in table ...
Exec-Program: returned: 0
++[exec] returns ok

As you CAN SEE

“Exec-Program output: Seems to be New User, adding its MAC address in table …”

Now see the difference …

RADCHECK TABLE, Before Login …

1- before login


2- after login ok

When user will login again, radcheck table will be searched, if the mac found it will simply skip the add part and print the statement

[exec] expand: %{User-Name} -> zaib
[exec] expand: %{Calling-Station-Id} -> 0C:84:DC:1E:0B:8D
Exec-Program output: MAC Entry already found
Exec-Program-Wait: plaintext: MAC Entry already found
Exec-Program: returned: 0
++[exec] returns ok

& If the user will login from any other mac/workstation, he will be denied access.


Trigger approach to add MAC address automatically upon user connection

Use following TRIGGER on radacct table. It will add the MAC address for the user in RADCHECK table after user is successfully connected. (or you can modify it as well)

-- Triggers `radacct`
CREATE TRIGGER `chk_mac_after_insert` AFTER INSERT ON `radacct` FOR EACH ROW BEGIN
SET @mac = (SELECT count(*) from radcheck where username=New.username and attribute='Calling-Station-ID');
IF (@mac = 0) THEN
INSERT into radcheck (username,attribute,op,value) values (NEW.username,'Calling-Station-ID',':=',NEW.callingstationid);
UPDATE users SET mac = NEW.callingstationid where username = NEW.username;

trigger for mac add.JPG

3# Allow Multiple MAC Login along with CHECKVAL !

We know that CHECKVAL allows only single mac to connect. Recently there was a requirement that a user have 2 systems that he uses in day & night timings. Since he had no wifi router,  therefore he wanted to have 2 MAC allowed. (one Id will connect at one time). So we can Use the += operator instead to allow multiple mac if we are using CHECKVAL operative to limit mac address!

multiple mac

INSERT INTO `radcheck` (`id`, `username`, `attribute`, `op`, `value`) VALUES
(1, 'zaib', 'Cleartext-Password', ':=', 'zaib'),
(11, 'zaib', 'Expiration', ':=', '25 Feb 2019 17:00:00'),
(27, 'zaib', 'Calling-Station-ID', '+=', '24:26:42:D4:BC:43'),
(42, 'zaib', 'Calling-Station-ID', '+=', '00:0C:29:B9:D8:A0');[/soureceode]</pre>

<hr />


<hr />

<hr />

<h2>Additional Notes for DMASOFTLAB Radius Manager Auto Mac binding on user first login</h2>
in DMA radius Manager there is builtin option to auto bind the user mac upon first login. in DMA panel, Goto <em>Home &gt; Settings &gt; Lock first seen mac</em> , although this option is working fine, but still if you need alternate method using mysql trigger, then use this trigger

Login to mysql, use radius db, and issue

-- MySQL dump 10.13 Distrib 5.5.54, for debian-linux-gnu (i686)
-- Host: localhost Database: radius
-- ------------------------------------------------------
-- Server version 5.5.54-0ubuntu0.12.04.1
/*!40103 SET TIME_ZONE='+00:00' */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `chk_mac_after_insert` AFTER INSERT ON `radacct` FOR EACH ROW BEGIN
SET @mac = (SELECT count(*) from rm_users where username=New.username and mac='Calling-Station-ID');
IF (@mac = 0) THEN
UPDATE rm_users SET mac = 'Calling-Station-ID' , usemacauth = '1' where username = 'New.username';
END */;;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;

-- Dumping routines for database 'radius'


Syed Jahanzaib ~

October 19, 2017

Prevent Mikrotik from Chocking with Cisco Inter-Vlan Routing




Disclaimer! This is important!

My humble request is that kindly donot consider me as 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. When you are enslaved by private job & working as one man army, you have to perform many task in which you are not formally trained for. 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 try to help others

For adding classless routes under Mikrotik DHCP , Please read below

Scenario: [Example]

OP have mini ISP setup. Different areas are connected with Cisco 3750 switch where Vlan(s) for each port is configured. Trunk port is connected with Mikrotik Routerboard where vlan interfaces are configured accordingly. DHCP for each VLAN is configured on the Mikrotik RB which provides different subnet to each vlan with default gateway pointing to each VLAN IP.

PPPoE Server is configured on the RB to facilitate ppp dialing for each vlan. As per policy, user must dial pppoe dialer to connect with the mikrotik PPP server in order to access internet.


OP have few media sharing server located on Vlan No 3. When user starts downloading heavy media files from the Vlan No 3, all of his traffic routes via Mikrotik Router which creates load on router.

Solution # 1: [that worked partially]

After some R&D, I implemented following

  • Moved DHCP role to Cisco
  • Setup intervlan routing. enabled ip routing
  • Added default gateway in DHCP options pointing to Cisco local vlan ip respectively

This partially solves the problem. When user join the LAN, he gets IP address from the Cisco dhcp with default gateway to its respective vlan IP. all goes well , communication was happening fine with in vlan without touching the Mikrotik. But as soon as users dial the PPPOE dialer, his traffic starts routing via Mikrotik . after some troubleshooting it appears that when user dials pppoe dialer, his routes changes and ppp gets preference over other routes which force all traffic to go via RB.

As showed in the image below …

Load on Trunk Port when ppp user download from vlan no 3


routes and ipconfig of client before dhcp option

Solution # 2: [worked 100% as required]

In Cisco Switch DHCP settings for each vlan, Remove Default Gateway,  and add static routes for the sharing media servers subnet via using DHCP classless static routes option

Sounds fair enough :~)

Working Example Config for Cisco Switch 3750

# Cisco Switch Part

[Model: ws-c3750e-24pd / Version 15.0(2)SE10a ]

system mtu routing 1500
ip routing
ip dhcp pool vlan2
option 121 ip ## This option provides route information , /24.x is the subnet info and other is gw
ip dhcp pool vlan3
network ## This is media server vlan, we have added manual ip & gateway pointing to vlan ip
! to add multiple routes use below
! option 121 ip
ip dhcp pool vlan4
option 121 ip ## This option provides route information , /24.x is the subnet info and other is gw

! This port is connected with the Mikrotik RB
interface GigabitEthernet1/0/1
switchport trunk encapsulation dot1q
switchport mode trunk

! This port is connected with user area 2
interface GigabitEthernet1/0/2
switchport access vlan 2
switchport mode access

! This port is connected with local FTP/Media sharing server's
interface GigabitEthernet1/0/3
switchport access vlan 3
switchport mode access

!This port is connected with user area 4
interface GigabitEthernet1/0/4
switchport access vlan 4
switchport mode access
interface Vlan1
ip address
interface Vlan2
ip address
interface Vlan3
ip address
interface Vlan4
ip address
! Following route is pointing to Mikrotik RB
ip route

# Mikrotik Routerboard Part

/interface ethernet

set [ find default-name=ether1 ] name=LAN-TRUNK

/interface vlan
add interface=LAN-TRUNK name=vlan2 vlan-id=2
add interface=LAN-TRUNK name=vlan3 vlan-id=3
add interface=LAN-TRUNK name=vlan4 vlan-id=4

# It is recommended to use small subnet, like /29 for below (zaib)
/ip address
add address= interface=LAN-TRUNK network=

/interface pppoe-server server
add default-profile=pppoe-profile disabled=no interface=vlan2 max-mru=1480 max-mtu=1480 mrru=1600 one-session-per-host=yes service-name=service2
add default-profile=pppoe-profile disabled=no interface=vlan3 max-mru=1480 max-mtu=1480 mrru=1600 one-session-per-host=yes service-name=service3
add default-profile=pppoe-profile disabled=no interface=vlan4 max-mru=1480 max-mtu=1480 mrru=1600 one-session-per-host=yes service-name=service4

# FTP / Media Sharing Server Part

at your FTP server, which is under vlan no 3, define static ip like and point its gateway to, That’s It 🙂

Results are showed as below …


client ROUTEs and ipconfig AFTER DHCP OPTIOIN


download gpoign via vlan only after addding dhcp option


no load on mikrotik router and local vlan download going via local vlan




I have posted minimalist configuration to reduce any complication. Most of parts are quite self explanatory. This exercise was done successfully in LAB & required results were achieved. However you must consult with some Cisco expert & conduct your own testing  before implementing it on production.

Also you may want to use ACL in order to restrict access to shared resources, YKWIM

Syed Jahanzaib


October 16, 2017

Restart ppp dialer if getting ‘Private IP’

Filed under: Mikrotik Related — Tags: — Syed Jahanzaib / Pinochio~:) @ 3:42 PM

Reference Post:

Following is short script to reconnect PPPoE dialer if it receives any private IP from the ISP including 10.x.x.x / 172.x.x.x & 192.x.x.x series.


# Script to find if wan link have private ip and act accordingly,
# Tested with Mikrotik ROS 5.x & 6.x versions
# 19-APR-2016 / Syed Jahanzaib

# Set your WAN Interface name , i have added pppoe-out1 , change it as required
:set WANINTERFACE pppoe-out1

# Find Public IP from pppoe-out1 interface & cut subnet
:local WANIP [/ip address get [find where interface=$WANINTERFACE] address];
:set WANIP [:pick $WANIP 0 ([:len $WANIP]-3) ];

# Match if IP address starts with private address 10.*
:if ($WANIP ~"^[0-9 ]*10") do={
:log warning "Private ip address found !!!"
# Set your action here , like Re-Connect the pppoe-link
# /interface pppoe-client disable pppoe-out-1
# :delay 3
# /interface pppoe-client enable pppoe-out-1
} else={

# Match if IP address starts with private address 172.*
:if ($WANIP ~"^[0-9 ]*172") do={
:log warning "Private ip address found !!!"
# Set your action here , like Re-Connect the pppoe-link
# /interface pppoe-client disable pppoe-out-1
# :delay 3
# /interface pppoe-client enable pppoe-out-1
} else={

# Match if IP address starts with private address 192.*
:if ($WANIP ~"^[0-9 ]*192") do={
:log warning "Private ip address found !!!"
# Set your action here , like Re-Connect the pppoe-link
# /interface pppoe-client disable pppoe-out-1
# :delay 3
# /interface pppoe-client enable pppoe-out-1
} else={

# If above statement do not match, then consider it a public ip and take no action, just log : ~ )
:log warning "Public IP - $WANIP - Found, OK ! No action required"
# OR Set your desire action here if required
# Script Ends Here ...


Syed Jahanzaib

October 2, 2017

Safest method to clean /boot partition

Filed under: Linux Related — Tags: , — Syed Jahanzaib / Pinochio~:) @ 11:37 AM

Today morning ,when I tried to upgrade one of remote Ubuntu kernel I received error stating that /boot partition is full.

If your /boot partition usage goes to 100% (or near about) as showed in the image below, then its a good idea to make some room in in order to perform kernel upgrade.

boot part full.JPG

Usually one of safest method is as below …

Note: in this post, I am using Ubuntu 12.4 / 32 bit version.

First check the current kernel version

uname -r

This will show you the current kernel version like below …

root@radius:~# uname -r

As we can see that its 3.13.0-112-generic, make a note of it

Now run this command for a list of installed kernels:

dpkg --list 'linux-image*'

This will show you the current & all previous versions of kernels, like below …

kernel list.JPG

just delete the old kernels (marked in red) that we dont require anymore. Use following command

sudo apt-get remove linux-image-VERSION

Replace VERSION with the version of the kernel you want to remove.

WARNING: Make sure you dont delete the current running kernel (number acquired by uname -r command)

Once you are done removing all old kernels, issue following command

sudo apt-get autoremove

And finally run this to update grub kernel list

sudo update-grub


space after removal.JPG

Syed Jahanzaib

%d bloggers like this: