02 | June | 2016 | Syed Jahanzaib - Personal Blog to Share Knowledge !

June 2, 2016

Getting ‘Out of the Box’ solution with Mikrotik , BASH & mySQL

Filed under: Linux Related, Mikrotik Related, Radius Manager — Tags: dhcp usage statistics, mikrotik bash script, mikrotik dhcp pool usage — Syed Jahanzaib / Pinochio~:) @ 4:52 PM

DISCLAIMER:

JUST AN EXAMPLE SAMPLE !

Following post is an example of fun coding. Just to learn and explore new ways of howto get ‘out of the box’ solution. In this example I have used Mikrotik Script, Bash Script, mySQL, and sendEmail tool all together. I made this solution, and surely I know that it’s not very elegant, not professional but I learned few things from it . This is just my own idea and sharing it , maybe someone will find it useful for some other project. Just to share my two cents …

Most of tasks described in this lengthy post can be achieved using mikrotik scripting alone, But

I just wanted to explore the possibilities on how multi platform systems , scripts, functions can be used all together to get our desired results with twisted, molded and formatted results in a way we want it to be !!! Simple is this !!!

BASH is Fun !

Regard's
Syed Jahanzaib

Scenario:

The OP have several dhcp pools in Mikrotik for users. In peak time , the dhcp assigned all or most available ips from the specific pool and error starts appearing in LOG.

Jun 1 14:46:51 X.X.X.X dhcp,error dhcp12: failed to give out IP address: pool  is empty

Requirements

The OP wanted to receive email alert when any pool configured in pool section of mikrotik crosses xx %.
and all pool statistics should be stored in mySQL as well, so that it can be used for various purposes. The script should also email the admin about the pool usage alert if it crosses XX %.

Solution

At mikrotik forum, dssmiktik posted an script which can query all pools and display there statistics.
Example of this script result on mikrotik terminal is as follows.

We will use this script on the mikrotik, and configure scheduler on Ubuntu/Lilnux to execute this script remotely and fetch the results in a local file, Format it, Store it in mySQL custom table, Do Comparison and ACT accordingly.

Example if any pool crosses specific % limit, the bash script will update table accordingly, Send email and it will also prevent repeated email for the same.

Mikrotik Section #

Add following script in mikrotik script section …


# List stats for IP -> Pool
#
# criticalthreshold = output pool display in red if pool used is above this %
# warnthreshold = output pool display in gold if pool used is above this %

:local criticalthreshold 85
:local warnthreshold 50

# Internal processing below...
# ----------------------------------
/ip pool {
:local poolname
:local pooladdresses
:local poolused
:local poolpercent
:local minaddress
:local maxaddress
:local findindex
:local tmpint
:local maxindex
:local line

# :put ("IP Pool Statistics")
# :put ("------------------")

# Iterate through IP Pools
:foreach p in=[find] do={

:set poolname [get $p name]
:set pooladdresses 0
:set poolused 0
:set line ""

:set line (" " . $poolname)

# Iterate through current pool's IP ranges
:foreach r in=[:toarray [get $p range]] do={

# Get min and max addresses
:set findindex [:find [:tostr $r] "-"]
:if ([:len $findindex] > 0) do={
:set minaddress [:pick [:tostr $r] 0 $findindex]
:set maxaddress [:pick [:tostr $r] ($findindex + 1) [:len [:tostr $r]]]
} else={
:set minaddress [:tostr $r]
:set maxaddress [:tostr $r]
}

# Convert to array of octets (replace '.' with ',')
:for x from=0 to=([:len [:tostr $minaddress]] - 1) do={
:if ([:pick [:tostr $minaddress] $x ($x + 1)] = ".") do={
:set minaddress ([:pick [:tostr $minaddress] 0 $x] . "," . \
[:pick [:tostr $minaddress] ($x + 1) [:len [:tostr $minaddress]]]) }
}
:for x from=0 to=([:len [:tostr $maxaddress]] - 1) do={
:if ([:pick [:tostr $maxaddress] $x ($x + 1)] = ".") do={
:set maxaddress ([:pick [:tostr $maxaddress] 0 $x] . "," . \
[:pick [:tostr $maxaddress] ($x + 1) [:len [:tostr $maxaddress]]]) }
}

# Calculate available addresses for current range
:if ([:len [:toarray $minaddress]] = [:len [:toarray $maxaddress]]) do={
:set maxindex ([:len [:toarray $minaddress]] - 1)
:for x from=$maxindex to=0 step=-1 do={
# Calculate 256^($maxindex - $x)
:set tmpint 1
:if (($maxindex - $x) > 0) do={
:for y from=1 to=($maxindex - $x) do={ :set tmpint (256 * $tmpint) }
}
:set tmpint ($tmpint * ([:tonum [:pick [:toarray $maxaddress] $x]] - \
[:tonum [:pick [:toarray $minaddress] $x]]) )
:set pooladdresses ($pooladdresses + $tmpint)
# for x
}

# if len array $minaddress = $maxaddress
}

# Add current range to total pool's available addresses
:set pooladdresses ($pooladdresses + 1)

# foreach r
}

# Now, we have the available address for all ranges in this pool
# Get the number of used addresses for this pool
:set poolused [:len [used find pool=[:tostr $poolname]]]
:set poolpercent (($poolused * 100) / $pooladdresses)

# Output information
:set line ([:tostr $line] . " [" . $poolused . "/" . $pooladdresses . "]")
:set line ([:tostr $line] . " " . $poolpercent . " % used")

# Set colored display for used thresholds
:if ( [:tonum $poolpercent] > $criticalthreshold ) do={
:log error ("IP Pool " . $poolname . " is " . $poolpercent . "% full")
:put ([:terminal style varname] . $line)
} else={
:if ( [:tonum $poolpercent] > $warnthreshold ) do={
:log warning ("IP Pool " . $poolname . " is " . $poolpercent . "% full")
:put ([:terminal style syntax-meta] . $line)
} else={
:put ([:terminal style none] . $line)
}
}

# foreach p
}
# /ip pool
}

Create Tables in DB first !

Following is mysql table mikrodhcp.sql dump. Save it in file, and restore it using mysql command.

Example: [restore mikrodhcp table in mysql radius database, change it as per your own configuration]

mysql -u root -prootpassword radius < mikrodhcp.sql


-- MySQL dump 10.13 Distrib 5.5.49, for debian-linux-gnu (i686)
--
-- Host: localhost Database: radius
-- ------------------------------------------------------
-- Server version 5.5.49-0ubuntu0.12.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `mikrodhcp`
--

DROP TABLE IF EXISTS `mikrodhcp`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mikrodhcp` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`mikrotikip` varchar(16) CHARACTER SET utf32 NOT NULL,
`poolname` text NOT NULL,
`poolipusedno` int(11) NOT NULL,
`pooliptotal` int(11) NOT NULL,
`percentage` int(11) NOT NULL,
`mailsent` tinyint(1) NOT NULL,
`status` tinyint(1) NOT NULL,
`lastupdate` datetime NOT NULL,
`autodateupdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=727 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `mikrodhcp`
--

LOCK TABLES `mikrodhcp` WRITE;
/*!40000 ALTER TABLE `mikrodhcp` DISABLE KEYS */;
/*!40000 ALTER TABLE `mikrodhcp` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2016-06-02 15:58:13

IMPORTANT ! TEST THE TABLE !

One the table is imported without any error. Check it with following command

mysql -uroot -pROOTPASSWORD -e "use radius; describe mikrodhcp;"

and you may get following result if ALL is OK !

+----------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| mikrotikip | varchar(16) | NO | | NULL | |
| poolname | text | NO | | NULL | |
| poolipusedno | int(11) | NO | | NULL | |
| pooliptotal | int(11) | NO | | NULL | |
| percentage | int(11) | NO | | NULL | |
| mailsent | tinyint(1) | NO | | NULL | |
| status | tinyint(1) | NO | | NULL | |
| lastupdate | datetime | NO | | NULL | |
| autodateupdate | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------------+------------------+------+-----+-------------------+-----------------------------+

Now you can use following bash script …

the BASH SCRIPT !

#!/bin/bash
#set -x
# Script to fetch dhcp ip pool results from the mikrotik
# then update these results in mysql table, and email accordingly
# No portion of this script is copied from the internet.
# You are free to copy, modify, distribute it as you like
# Make sure you change all the variables as required like mysql id, tables etc.
# Created by : Syed Jahanzaib / aacable @ hotmail dot com
# https://aacable.wordpress.com
# Created: 2nd-MAY-2016
clear
# Colors Config . . . [[ JZ . . . ]]
ESC_SEQ="\x1b["
COL_RESET=$ESC_SEQ"39;49;00m"
COL_RED=$ESC_SEQ"31;01m"
COL_GREEN=$ESC_SEQ"32;01m"
#Temporary Holder for DHCP Status from Mikrotik
RESULT="/tmp/dhcpstatus.txt"
&gt; $RESULT
#Mikrotik Details
MIKROTIK="1.2.3.4"
MTPORT="8291"
MTUSER="admin"
MTDHCPSCRIPT="dhcpstatus"
# DATE TIME
DATE=`date`
TODAYTIME=$(date +"%Y-%m-%d %T")
#MYSQL INFO
SQLUSER="root"
SQLPASS="ROOTPASS"
DB="radius"
TABLE="mikrodhcp"
MAINTABLE="rm_users"
ALERTPERCENTAGE="80"
#EMAIL SECTION
GMAILID="MYGMAIL@gmail.com"
GMAILPASS="PK"
# Correct below email, modified it to save from webbug
ADMINMAIL1="aacableAThotmailDOTcom"
COMPANY="ZAIB (Pvt) Ltd."
FOOTER="Powered by Syed Jahanzaib"
# Create mikrodhcp table if not exists
DBCHECK=`mysql -u$SQLUSER -p$SQLPASS -e " SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '$DB';"`
if [ ! -z "$DBCHECK" ];
then
echo -e "Step-1# Checking $DB DB ... $DB database Found OK, proceeding further ... $COL_GREEN OK $COL_RESET"
#sleep 3
else
echo -e "$COL_RED ERROR: $DB database does NOT exists in mysql. it is required to store dhcp pool status data ...$COL_RESET"
exit 0
fi
# Create mikrodhcp table if not exists
TABLECHECK=`mysql -u$SQLUSER -p$SQLPASS -e " SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '$DB' AND TABLE_NAME = '$TABLE';"`
if [ ! -z "$TABLECHECK" ];
then
echo -e "Step-2# Checking $TABLE table ... $TABLE TABLE Found OK, proceeding further ... $COL_GREEN OK $COL_RESET"
#sleep 3
else
echo -e "$COL_RED ERROR: $TABLE does NOT exists in $MAINTABLE. it is required to store mikroptik dhcp pool status data ...$COL_RESET"
exit 0
fi
# Check if Mikrotik is accessibel or not, if not then EXIT immediately with error / zaib
if [[ $(ping -q -c 1 $MIKROTIK) == @(*100% packet loss*) ]]; then
echo -e "$COL_RED ALERT ..... MIKROTIK $MIKROTIK is DOWN$COL_RESET"
exit
else
echo -e "Step-3# Mikroik is Accessible, now proceeding further ... $COL_GREEN OK $COL_RESET"
fi
# Execute script on mikrotik which will get the required results liek dhcp ip pool status
ssh -q -p $MTPORT $MTUSER@$MIKROTIK /sys script run $MTDHCPSCRIPT &gt; $RESULT
# VERIFY $RESULT FILE
A=`cat $RESULT`
B="no such item"
if [ "$A" == "$B" ];
then
echo -e "$COL_RED Mikrotik Script name '$MTDHCPSCRIPT' not found on Mikrotik. Please verify script name, test it on mikrotik first .... $COL_RESET"
exit 0
fi
sed -i '1,2d' $RESULT
echo -e "Step-4# Mikroik script fetched is Accessible, now proceeding further ... $COL_GREEN OK $COL_RESET"
# Verify if file is downloaded from mikrotik or not, if not dueo to ssh delay bug or other , then print error and exit <img class="emoji" alt="" src="https://s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/svg/1f642.svg"> Security Check by zaib
{
if [ ! -f $RESULT ]; then
echo -e "$COL_RED ERROR: Mikrotik $MIKROTIK is live but it's SSH not accessible !!! $COL_RESET"
exit 0
fi
}
echo -e "Step-5# Mikroik $MIKROTIK SSH is accessible, now proceeding further ... $COL_GREEN OK $COL_RESET"
echo -e "Showing Results fetched from Mikrotik script ... $COL_GREEN OK $COL_RESET
"
echo -e "[POOL-NAME] [IP-USED-IN-POOL] [TOTAL-IP-IN-POOL] [POOL-USED-PERCENTAGE-%]" | awk '{printf "%-30s %-40s %-40s %-40s\n",$1,$2,$3,$4}'
echo ""
# Run Loop Formula
# Apply Formula to read the file in which dismissed users list and act accordingly.
num=0
cat $RESULT | while read data
do
num=$[$num+1]
POOLNAME=`echo $data | awk '{print $1}'`
POOLSTATUS=`echo $data | awk '{print $2}'`
POOLUSEDPERC=`echo $data | awk '{print $3}'`
POOLIPTOTAL=`echo $data | awk '{print $2}' | sed 's/\(\[\|\]\)//g' | sed 's#/#\ #g' | awk '{print $2}'`
POOLIPUSEDNO=`echo $data | awk '{print $2}' | sed 's/\(\[\|\]\)//g' | sed 's#/#\ #g' | awk '{print $1}'`
# Adding POOL names in table, so they can be updated according to teh usage in later stage ... zaib
mysql -u$SQLUSER -p$SQLPASS -e "use $DB; INSERT INTO $TABLE (mikrotikip, poolname) SELECT * FROM (SELECT '$MIKROTIK', '$POOLNAME') AS tmp WHERE NOT EXISTS (
SELECT poolname FROM $TABLE WHERE poolname = '$POOLNAME') LIMIT 1;"
if [ "$POOLUSEDPERC" -gt $ALERTPERCENTAGE ]
then
#echo -e "$COL_RED ALERT: $POOLNAME have consumed $POOLIPUSEDNO ips from $POOLIPTOTAL Total IPs / Percetnage Used = $POOLUSEDPERC % $COL_RESET"
echo -e "$COL_RED$POOLNAME $POOLIPUSEDNO $POOLIPTOTAL $POOLUSEDPERC Crossed $ALERTPERCENTAGE% $COL_RESET" | awk '{printf "%-40s %-40s %-40s %-5s %-5s %-5s *** ALERT ***\n",$1,$2,$3,$4,$5,$6}'
# UPDATE pool status with ALERT Status and other info
mysql -u$SQLUSER -p$SQLPASS -e "use $DB; UPDATE $TABLE SET mikrotikip = '$MIKROTIK' , poolipusedno = '$POOLIPUSEDNO' , pooliptotal = '$POOLIPTOTAL' , percentage = '$POOLUSEDPERC' , status = '1' , lastupdate = '$TODAYTIME' WHERE poolname = '$POOLNAME';"
else
# If percentage is low, Show result and update mysql table as well
#echo -e "$COL_GREEN NORMAL USAGE: $POOLNAME have consumed $POOLIPUSEDNO ips from $POOLIPTOTAL Total IPs / Percentage Used = $POOLUSEDPERC % $COL_RESET"
echo -e "$COL_GREEN$POOLNAME $POOLIPUSEDNO $POOLIPTOTAL $POOLUSEDPERC $COL_RESET" | awk '{printf "%-40s %-40s %-40s %-40s\n",$1,$2,$3,$4}'
# UPDATE pool status with normal values
mysql -u$SQLUSER -p$SQLPASS -e "use $DB; UPDATE $TABLE SET mikrotikip = '$MIKROTIK' , poolipusedno = '$POOLIPUSEDNO' , pooliptotal = '$POOLIPTOTAL' , percentage = '$POOLUSEDPERC' , status = '0' , mailsent = '0' , lastupdate = '$TODAYTIME' WHERE poolname = '$POOLNAME';"
fi
# Testing if email is required to be sent, if not alreasy sent
MAILSENT=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; select mailsent from mikrodhcp where poolname = '$POOLNAME';"`
if [[ $POOLUSEDPERC -gt $ALERTPERCENTAGE &amp;&amp; $MAILSENT -eq 0 ]]
then
echo "Sending email for $POOLNAME ..."
mysql -u$SQLUSER -p$SQLPASS -e "use $DB; UPDATE $TABLE SET mailsent = '1' where poolname = '$POOLNAME';"
##################### START SENDING EMAIL
# create temporary holder where EMAIL will be stored
EMAILFILE="/tmp/$POOLNAME.dhcp.email"
&gt; $EMAILFILE
echo "$COMPANY DHCP ALERT:
$POOLNAME pool in Mikrotik DHCP have crossed $ALERTPERCENTAGE % Limit
$POOLNAME have consumed $POOLIPUSEDNO ips from $POOLIPTOTAL Total IPs
$POOLNAME Percetnage Used = $POOLUSEDPERC %
Regard's
$COMPANY
$FOOTER" &gt; $EMAILFILE
# Make sure you install sendEMAIL tool and test it properly before using email section.
# SEND EMAIL Alert As well using sendEMAIL tool using GMAIL ADDRESS.
# If you want to send email , use below ...
echo "Sending EMAIL ALERT to $ADMINMAIL1 ..."
sendEmail -u "$COMPANY DHCP ALERT: $POOLNAME have consumed $POOLUSEDPERC %." -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$EMAILFILE -o message-content-type=text

fi
##################### EMAIL SENT DONE
if [[ $POOLUSEDPERC -gt $ALERTPERCENTAGE &amp;&amp; $MAILSENT -eq 1 ]]
then
echo "Email alert already sent for $POOLNAME to $ADMINMAIL1..."
#mysql -u$SQLUSER -p$SQLPASS -e "use $DB; UPDATE $TABLE SET mailsent = '1' where poolname = '$POOLNAME';"
fi
done
###### LOOP DONE ########
#Reset Terminal Color to Default
tput sgr0
POOLIPTOTAL=`cat $RESULT | awk '{print $2}' | sed 's/\(\[\|\]\)//g' | sed 's#/#\ #g' | awk '{print $2}'`
POOLIPUSEDNO=`cat $RESULT | awk '{print $2}' | sed 's/\(\[\|\]\)//g' | sed 's#/#\ #g' | awk '{print $1}'`
TOTALIP=`echo "$POOLIPTOTAL" | awk '{ sum+=$1} END {print sum}'`
USEDIP=`echo "$POOLIPUSEDNO" | awk '{ sum+=$1} END {print sum}'`
echo "
Alert Threshold % = $ALERTPERCENTAGE
Total IPs in All POOLs = $TOTALIP
Total IPs used in ALL Pools = $USEDIP
"
echo -e "Updating MYSQL Table on Billing @ $DATE ... $COL_GREEN OK $COL_RESET"
echo "Powered by Syed Jahanzaib"

END RESULTS ! with FANCY COLORED OUTPUT : ) We all love COLORS don’t we ?

SCRIPT EXECUTION RESULT #1

1-dhcp-alert-on-bash-screen

SCRIPT EXECUTION RESULT #2

2-dhcp-alert-on-bash-screen-and-show-already-sent email

TABLE RESULTS AFTER SCRIPT UPDATE !

EMAIL ALERT SAMPLE #1

EMAIL ALERT SAMPLE #2

Next Tasks: To be continued …

Create MRTG graph for each pool, so that OP can have idea on which pool is most used in what timings exactly.

Comments (1)

Syed Jahanzaib – Personal Blog to Share Knowledge !

June 2, 2016

Search My Blog . . .

Blog Stats

Categories

Meta

Email Subscription

Archives

Recent Posts

Pages

Top Clicks

Top Posts

RSS

Syed Jahanzaib FB Link

Syed Jahanzaib – Personal Blog to Share Knowledge !

June 2, 2016

Getting ‘Out of the Box’ solution with Mikrotik , BASH & mySQL

DISCLAIMER:

JUST AN EXAMPLE SAMPLE !

Scenario:

Requirements

Solution

Mikrotik Section #

Create Tables in DB first !

IMPORTANT ! TEST THE TABLE !

the BASH SCRIPT !

END RESULTS ! with FANCY COLORED OUTPUT : ) We all love COLORS don’t we ?

SCRIPT EXECUTION RESULT #1

SCRIPT EXECUTION RESULT #2

TABLE RESULTS AFTER SCRIPT UPDATE !

EMAIL ALERT SAMPLE #1

EMAIL ALERT SAMPLE #2

Share this:

Search My Blog . . .

Blog Stats

Categories

Meta

Email Subscription

Archives

Recent Posts

Pages

Top Clicks

Top Posts

RSS

Syed Jahanzaib FB Link