Syed Jahanzaib Personal Blog to Share Knowledge !

October 31, 2017

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

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


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




FREERADIUS WITH MIKROTIK – Part #4 > You are here 

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 TRIGGER method to auto insert the mac address of user if there is no mac entry in the radcheck table for his username, Trigger are more efficient method in my opinion.]

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]


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

// PHP page to check if MAC is not aleady there for the user, then INSERT it for MAC VALIDATION,
// it will add mac for 1st time login user only
// Syed Jahanzaib / aacable at hotmail dot com
// https://aacable . wordpress . com
// 31-OCT-2017

$link = mysql_connect('localhost', 'root', 'MYSQL-ROOT-PASSWORD');
if (!$link) {
die('Could not connect: ' . mysql_error());
// Default DB is radius
// Look for MAC entry for this user
$result=mysql_query("select * FROM radcheck WHERE `UserName`='$argv[1]' AND attribute='Calling-Station-Id' order by Username limit 1");
$val = mysql_num_rows($result);
if ($val > 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.

Method #2
Trigger approach to add MAC address
🙂 ~ ZAIB

Use following TRIGGER on radacct table. It will add the MAC address for the user in RADCHECK table. (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

Syed Jahanzaib ~


October 19, 2017

Prevent Mikrotik from Chocking with Cisco Inter-Vlan Routing

Filed under: Cisco Related, Mikrotik Related — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 4:50 PM


I donot have professional level expertise with the mikrotik & specially Cisco. It’s just personnel R&D that sometimes leads me to a working solution. After posting on the internet, I got some clues & Alhamdoillah it worked !

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

September 13, 2017

IBM v3700 – Noisy PSU Problem

Filed under: IBM Related — Tags: , , , — Syed Jahanzaib / Pinochio~:) @ 3:05 PM




We have IBM v3700 SAN system along with expansion unit as well. From past 2 weeks, there was loud noise generating from the SAN PSU like its running on full capacity. After some research it was found out that its a known bug in the v3700 series SAN and following actions should be taken to sort it.

  1. If you have IBM warranty/SLA, call the support, they will rectify the issue as IBM support is very good & quick in most cases. MAke sure you get the San Machine Type / Serial numbers before calling.

If you are managing the SAN on your own, then follow these instructions

(this doesnt requires any downtime, the process was non-destructive)

  1. The first thing to try is to ‘reseat the PSU cable’ of the PSU that is running high.
  2. If reseating the cable did not work, try reseating the PSU. After a few seconds, then fan speed should start to fall.
  3. Make sure you are running the latest firmware. I was running (build 80.4.1309270000) that was very old (as of Sep,2017). A fix for this issue has been included from code level 7.6 onward. This fix works for V3700, V5000 and V7000 Gen2 so an upgrade will fix the problem. The new firmware is 7.8 as of current date.
  4. Finally the issue should be resolved by resetting the entire canister. Connect to the SAN controller using PUTTY, & issue following command [This point#3 solved my problem]

chenclosurecanister -reset -canister <can> <encl>

You need to find out which canister PSU is making noise. Example

chenclosurecanister -reset -canister 1 1

If you have additional canister, then you may use (wait half hour before applying command to 2nd canister)

chenclosurecanister -reset -canister 2 1

As showed in below image …

v3700 commands.JPG

Wait for few minutes & the PSU sound should come back to normal.

24-October- 2017 Update:

More then one month have been passed & the SAN PS are working fine with normal sound level. So the command really worked (at least in my case)

Syed Jahanzaib


August 16, 2017

IBM Lotus Domino: Layman’s approach to move Archive’s to new partition

Filed under: IBM Related — Syed Jahanzaib / Pinochio~:) @ 9:16 AM



We are using IBM’s Lotus Domino 8.x on Windows 2008 R2 with following folders structure.

  • D:\LOTUS\DOMINO\DATA\MAIL   > 500 GB , users inbox
  • D:\LOTUS\DOMINO\DATA\MAIL\ARCHIVE > 1000 GB , users archived mails

Archiving policy is enabled on the server-end which runs on a weekly basis, It moves One year old email from the inbox folder to ARCHIVE folder with a_username structure. Disk Space was getting low in D: partition therefore I had added new drive (E:) and wanted to move user ARCHIVE(s) to new partition E:\ARCHIVE

There were few solutions to perform the operation, Online & Offline.

With Online approach we could use the Domino’s builtin MOVE operation (via domino admin client) in which we dont have to take any shutdown, but then we would need to get the timing right. If the mailfiles are not moved into the new folder before our  scheduled , server archive runs then new archive files will be created which may complicate things.

But since I was able to afford 2 hours down time I took the OS cut/paste option.

I did following

  1. Quit the Domino via Admin Client, then Stop the Domino Services via SERVICES.
  2. Moved (Cut n Paste) ARCHIVE folder from D to E: drive (e:\archive2 folder)
  3. In D:\LOTUS\DOMINO\DATA\MAIL folder , I created a text file called ARCHIVE.DIR
    In text file put I added path E:\ARCHIVE).
  4. Start Domino Server service (Or better to restart the server).

& all went fine.

I am big fan of Domino’s own MOVE operation, but after few months, I will be replacing this machine with new server, then it would be a problem to move the archives again. there fore above Operation was a good choice from Layman’s management perspective 🙂

Hope it will help someone with same situation.

~Syed Jahanzaib~

August 15, 2017

Playing with the `radpostauth` table in Freeradius

Filed under: Radius Manager — Syed Jahanzaib / Pinochio~:) @ 3:40 PM

Following post was tested with DMA,

For better approach you may want to see following …

Freeradius External Auth BASH Script & RADPOSTUATH logging with customized reply message !

Freeradius is a well known billing system which is commonly used by ISP’s worldwide due to its reliability,  highly customizable and versatility. Many 3rd party vendors have made some good GUI fronted to manage the FR back-end engine.

It also sues mysql to facilitate logging of various users details. One of the table called radpsotauth which can hold information about users successfull/failed login attempts. Using this table, we can compliment our own GUI or 3rd party fronted (for easy management of freeradius engine) like DMASoftlab radius manager Authentication Log section so that admin / support can see users authentication logs. With some modification we can add useful information for quick troubleshooting example why user denied the authentication request, either invalid mac, wrong password, or account expired.

Note to *.*

  • You can add UNLAG query as well to apply IF statement, and add REPLY result according to your requirements.
  • This post was written for some reference purposes & will be updated as per request.
  • This guide is incomplete post. But it can be used as a reference as well for future retrieval


showing auth logs with errors numbers.JPG

As we can see in above image that in Radius Reply column, it clearly showing why user is denied like invalid mac address , account expires, invalid service reference (when user account id disabled in dma).

To enable these features we have to perform few steps as following …



First we need to edit the default sites config file for raddb.

Edit following file

nano /usr/local/etc/raddb/sites-available/default

Now search for “post-auth {” section

To make it simple and copy-paste format, Use following

post-auth {
Post-Auth-Type REJECT {

As showed in the image below …

psot-auth section

Save & Exit.


Edit the post-auth section in /usr/local/etc/raddb/sql/mysql/dialup.conf file

At the end of this file you will see “postauth_query” section. You need to change it

Old post-auth query


After changing


or copy paste text as below…

# Authentication Logging Queries
# postauth_query - Insert some info after authentication

postauth_query = "INSERT INTO ${postauth_table} \
(username, pass, reply, authdate, nasipaddress, mac) \
'%{User-Name}', \
'%{%{User-Password}:-%{Chap-Password}}', \
'%{reply:Packet-Type} - %{reply:Reply-Message}', \
'%S', \
'%{NAS-IP-Address}', \

Save & Exit.

#Alter the RADPOSTAUTH table using mysql cmd …

Using mysql cmd, we will perform 2 functions

  1. Increase the REPLY column length to accommodate longer reply messages display properly
  2. Add the MAC Address column so we can detect the calling user device mac address
mysql -uroot -pPASSWORD
use radius;
ALTER TABLE radpostauth MODIFY `reply` VARCHAR( 100 );

Restart the RADIUSD service

service radiusd restart

using CMD, you can now see the authentication log table

mysql -uroot -pPASSWORD -e “use radius; select * from radpostauth;”

& you will see the information

phpmyadmin query for table

1 JOHN     123456     Access-Reject - The account has expired=21      2017-08-15 [14:14:05         10:FE:ED:33:BD:AX


  • You can modify the messages appearing in the different columns, you can add your own customized columns as well like called station, or others
  • You can add UNLAG query as well to apply IF statement, and add REPLY result according to your requirements.
  • It can chew up your disk space, so try to make text shortest possible, like error codes only, not the whole text.
  • scheduled a cron job so that it can empty the table on weekly/monthly basis so that it may not swallow disk space plus performance should remain optimal.
  • If used in heavy environment it can put considerable strain on your system resources by putting large quantity of mysql INSERT queries into the table.



Syed Jahanzaib


July 26, 2017

Mikrotik script to monitor any host with optional SMS/Email alert

Filed under: Mikrotik Related — Syed Jahanzaib / Pinochio~:) @ 2:04 PM

Note to Self:

Following is a simple script for Mikrotik RouterOS to monitor any device by PING & upon status change like Donw/UP, it can take action like sending SMS/Email alert, change routes , interface etc. It is customized according to local OP requirements on demand. You can modify at , add remove any option as per taste. There are lot of good working scripts available on the internet. Just try not to blindly copy paste any one else script. Read it few times until you understand clearly what its made for & what functions it will perform. I have added some comments for the clarity.

Script??? Why use Script ?

Why use such complicated scripts while you can do this easily with builtin net-watch or windows base applications like the Dude, or Nix base Nagios, and so many other tools? the answer is simple, using script you have more Power, more control, more maneuverability , customized ,bizarre and strange actions you can add. Above all, Learning & feeling of Power you have over the system 🙂 this alone justify for me to use scripting 🙂

Thank you
~Syed Jahanzaib~

Script Output Examples:

When Device is DOWN …

1- deviec down


When Device is UP (restored) …

2- deviec up


Example of SMS received:

2017-07-26 13.44.01

the Script !

# This is Mikrotik Script for Local Device Link monitoring by IP
# - with Optional SMS Alert. We are using local Linux base KANNEL
# You can modify it to add EMAIL alerts as well using GMAIL or local Mail Gw.
# system as SMS gateway with local modem attached
# Script By Syed Jahanzaib / #
# Email : aacable at hotmail dot com
# Script Last Modified : 26-July-2017

# Set Device IP here
:local DEVICE1host1 ""
# Dont use SPACEC Here, because our KANNEL system dont like spaces, use + sign instead
:global DEVICE1LanStatus;
:global DEVICE1LanLastChange;

#:log warning "Checking status of Device $DEVICE1host1 by ping ..."
:local DELAY "3s"
:local i 0;
:local F 0;
:local date;
:local time;
:set date [/system clock get date];
:set time [/system clock get time];
# Setting Date Time variables
:local sub1 ([/system identity get name])
:local sub2 ([/system clock get date])
:local sub3 ([/system clock get time])

# Company Name, Dont use SPACEC Here, because our KANNEL system dont liek spaces, use + sign instead

# Number of Ping Count, how many times mikrotik should ping the target device
:local PINGCOUNT "5"
# Ping threshold
:local PINGTS "5"

# Provide details of Kannel SMS gateway, ID pass, and cell numbers on which sms is required
:local KURL "http://KANNEL-GW-IP-OR-NAME:13013/cgi-bin/sendsms"
:local KID "kannel"
:local cell1 "03333021909"

# Mail Alert information
:local ADMINMAIL1 ""

# SMS Msg format for Kannel SMS gateway (donot use spaces in it)

# LOG error
:local DOWNLOG1 "$COMPANY ALERT: $DEVNAME with IP $DEVICE1host1 is now DOWN @ $sub1 $sub2 $sub3..."
:local UPLOG1 "$COMPANY INFO: $DEVNAME with IP $DEVICE1host1 is now UP @ $sub1 $sub2 $sub3 ..."

# Start the SCRIPT

# If Script is running for the first time , consider target device UP,
# Just to avoid any errors in the script dueto empty variable.
:if ([:len $DEVICE1LanStatus] = 0) do={
:set DEVICE1LanStatus "UP";

# PING each host $PINGCOUNT times
:for i from=1 to=$PINGCOUNT do={
if ([/ping $DEVICE1host1 count=1]=0) do={:set F ($F + 1)}
:delay 1;

# If no response (all ping counts fails for both hosts, Time out, then LOG down status and take action
:if (($F=$PINGTS)) do={
:if (($DEVICE1LanStatus="UP")) do={

# If the link is down, then LOG warning in Mikrotik LOG window [Zaib]
:log error "$DOWNLOG1";
:set DEVICE1LanStatus "DOWN";
# Also add status in global variables to be used as tracking
:set date [/system clock get date];
:set time [/system clock get time];
:set DEVICE1LanLastChange ($time . " " . $date);
# Send SMS via KANNEL for DOWN Status
:log warning "Sending EMAIL/SMS for DOWN status of $DEVNAME $DEVICE1host1 ..."
#/tool fetch url="$KURL\?username=$KID&password=$KPASS&to=$cell1&text=$MSGDOWNSMS"
/tool e-mail send to=$ADMINMAIL1 subject="$COMPANY ALERT: $DEVNAME $DEVICE1host1 is now DOWN @ $sub3 $sub2 $sub1" start-tls=yes
#/interface sfp1 disable;
#:delay $DELAY
#/interface sfp1 enable;

# If ping reply received, then LOG UP and take action as required
} else={:set DEVICE1LanStatus "DOWN";}
} else={
:if (($DEVICE1LanStatus="DOWN")) do={
# If link is UP, then LOG info and warning in Mikrotik LOG window [Zaib]
:log warning "$UPLOG1"
:set DEVICE1LanStatus "UP";

# Send SMS via KANNEL for UP Status
:set date [/system clock get date];
:set time [/system clock get time];
:set DEVICE1LanLastChange ($time . " " . $date);
:log warning "Sending EMAIL/SMS for UP status of $DEVNAME $DEVICE1host1 ..."
#/tool fetch url="$KURL\?username=$KID&password=$KPASS&to=$cell1&text=$MSGUPSMS"
/tool e-mail send to=$ADMINMAIL1 subject="$COMPANY INFO: $DEVNAME $DEVICE1host1 is now UP @ $sub3 $sub2 $sub1" start-tls=yes
#/interface sfp1 disable;
#:delay $DELAY
#/interface sfp1 enable;
} else={:set DEVICE1LanStatus "UP";}
# Script ends here ...

Expanding Guest IDE disk in ESXI

Filed under: Forefront TMG 2010 Related — Syed Jahanzaib / Pinochio~:) @ 9:47 AM

We are using VMWARE Esxi 5.0 server hosting few VM guests. One of our guest machine which is acting as proxy/filter system (Forefront TMG 2010 ) for our users, its  disk space was getting lower (as its total size was 60 GB only). I installed this TMG in year 2012 & I was missing its patches / service packs therefore I was avoiding doing fresh installation. As per Vmware documentation, you cannot increase IDE disk size.


I tried another workaround but for some reasons, it didn’t worked in my case.
I used below workaround & it worked perfectly for me.

  • Power OFF the Target guest VM.
  • Login to ESXI Server via putty or any SSH client.
  • Navigate to folder where your guest VMDK file reside,
  • e.g: /vmfs/volumes/XXXXX/Guestname
  • Note down the .vmdk file name & issue expand command against that file
vmkfstools -X 200G TMG.VMDK

-X = expand
200G = size to increase (total size)
TMG.VMDK = File name which we want to expand

As showed in the image below …

1- space added

Depends on your hardware speed, it may take some time to complete the operation. I had some good IBM hardware & it took around 1-2 minutes to increase the size from 60GB to 200GB.

After few minutes it will populate to ESXI client automatically.

Now power ON the guest machine, and increase the size as required , either using Windwos Disk Management tool or cmd. I used MiniTool Partition Wizard Server Edition 7.1 which worked beautifully to complete the task without any hassle.

Below is Image after the task finished.

2- space added. final snap



June 16, 2017

Quick Notes on moving MySQL database(s) directory to New Partition

Filed under: Linux Related — Syed Jahanzaib / Pinochio~:) @ 4:59 PM


Following are short notes on how you can move mysql db directory to another partition.


Our Government base telecom authority called PTA (in PK) regularly issues letter to local ISP’s asking them to keep user traffic data for period of minimum one year.

As showed here (Thanks to Mr. Khalid for providing this notice copy)

pta letter

We know that almost all small scale ISP are natting there users by using local dsl or other medium bandwidth, and keeping private IP data traffic is (almost) useless because finding any thing from such data is like finding a needle in the haystack. If OP is assigning users with public ip, then OP should record users public ip assignment only like showed here, but as private natted IP are still being used therefore OP should save users traffic as per law despite of it will almost useless in practical)

In general we can log user traffic by using mikrotik own small scale web proxy, or adding external proxy like SQUID and route all traffic to squid proxy and keep logs there.

But if you are using Radius Manager, we have option of CONNECTION TRACKING. Using this method, we can track all users connection in mysql DB.



Connection Tracking requires lot of disk space for local ISP. Recently I recommend many OP to use SSD disk as SSD disks are more reliable , long life & offer fast disk access with minimal latency, but as SSD are costly therefore as alternative, we can add secondary disk for mysql like 2.TB and move our MySQL DB in this drive, rest OS or RM will operate from our primary SSD.


We have 2 disk in system as follows

  1. 128 GB SSD [Ubuntu 12.4 installed along with radius manager 4.1.5]
  2. 2 TB SATA Disk [Empty & Mounted as /2tb, howto mount disk read this ]

So our requirement is to move MySQL DB to this 2 TB disk.

Quick Cmd’s …

First login to MySQL and see your current Data Directory location.

mysql> select @@datadir;
| @@datadir |
| /var/lib/mysql |
1 row in set (0.00 sec)

Now we need to move this folder to our new 2tb. Follow below ,,,

Stop MySQL Service & Moves files to 2 TB Disk

sudo service mysql stop

Copy mysql DB Data directory to our 2tb disk with permissions intact, this part is crucial, pay attention to this section. We will use RSYNC to have same permission level in new folder.

sudo rsync -av /var/lib/mysql /2tb/

Rename current MySQL DB directory /var/lib/mysql to .bak [for backup purposes so that in case any thing goes wrong , we still have this restore point]

sudo mv /var/lib/mysql /var/lib/mysql.bak

Change PATH in MySQL INF file

Edit mysql inf file to change the DB directory

sudo nano /etc/mysql/my.cnf

in this file, find DATADIR line and change the old path to new one As showed below …

datadir = /2tb/mysql

Save & Exit

Apparmor Section [for Ubuntu OS]:

Allow new folder in APPARMOR (if you will skip this, you will get access / permission  errors)

sudo nano /etc/apparmor.d/tunables/alias

at the bottom add this line

alias /var/lib/mysql/ -> /2tb/mysql/,

Save n Exit.

It’s also recommended to disable SELINUX.

Start MySQL & Test

Now start the mysql service

service mysql start

& if all ok you may see following …

mysql start/running, process 1881

further verify it with process check

root@radius:~# ps aux |grep mysql
mysql 1881 0.1 3.9 328928 40536 ? Ssl 16:09 0:00 /usr/sbin/mysqld

Login to my mysql and verify all db/tables showing ok

root@radius:~# mysql -uroot -pMYSQLPASSWORD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 964
Server version: 5.5.54-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@datadir;
| @@datadir |
| /2tb/mysql/ |
1 row in set (0.00 sec)



Syed Jahanzaib

« Newer PostsOlder Posts »

%d bloggers like this: