Syed Jahanzaib Personal Blog to Share Knowledge !

June 22, 2016

Retrieve User Old/Original Password in RM

Filed under: Radius Manager — Tags: — Syed Jahanzaib / Pinochio~:) @ 10:44 AM

lostpass

Please beware that this post is just for Example purpose Only. In real production environment you must be very careful for providing such option. Make it tightly secure, add captcha code & provide this feature to requesting users only. AVOID using bash, RELY on PHP !


As requested by an client, Following is an script that can retrieve user’s current current password from 'radcheck' table. This method is useful in some situation where operator dont want to change the password for user, but to provide them there old/original password.

  • In RM, user’s password are encrypted with MD5. which is a Digest algorithm. Think of it as converting a cow into a steak. Now try to reverse that 🙂
  • There are some online MD5 decrypter, but they can decrypt general or common words. If you have something complex password, it wont be able to decrypted.

So rather then getting into MD5 decryption mess, why not retrieve it under the table 😉 by getting it from radcheck table.

There are few methods we can provide ‘current password retrieval’ funcion to user.

  1. We can configure playSMS to receive incoming SMS from user with specific command and username, then the system can retrieve user current password and sms to his Registered mobile number.
  2. Or we can make a simple PHP page where user can enter his user ID and then the system can send password to his Registered Mobile No. and Email address.

the Script ! [SAMPLE]

#!/bin/sh
#set -x
SQLUSER="SQL_USER"
SQLPASS="SQL_PASSWORD"
COMPANY="MyCompany"
CURDATE=$(date +"%Y-%m-%d")
echo $1 > /tmp/rawdata.txt
USERNAME=`cat /tmp/rawdata.txt |awk '{print $1}'`
echo ""
PASS=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; select value from radcheck where username = '$USERNAME';" | awk 'FNR == 1'`
echo "Dear $USERNAME,

Your Password is $PASS

Regard's
$COMPANY"

Execute the script and see the result.

shpass


PHP Form base method using Shell Script !

f1

f2

 

Sample php/shell files uploaded to

https://drive.google.com/folderview?id=0B8B_P2ljEc2xSndud0hDV29HT2s&usp=sharing&tid=0B8B_P2ljEc2xcEdkd2ttV1ZmNFU

Make sure you add good security measurements first !

 

Regard’s
Syed Jahanzaib

 

June 20, 2016

Routing & Natting with Failover ! Brothers in Arms

Filed under: Mikrotik Related — Tags: — Syed Jahanzaib / Pinochio~:) @ 1:39 PM

natro

~ Mikrotik CCR.1036 Performance Statistics ~

4vdsl-1fiber

 

 

mrtg


Reference Notes:

Mikrotik is a very powerful router that can perform variety of functions in one box. Sometimes It’s fun to do complex configuration with customized scripting to achieve our desired results. I just wanted to share some thoughts on one scenario where I configured multiple WAN links with PCC config plus public ips routing for users in single CCR RB. Routing+Natting+Fire-walling+QOS+Scripting and much more all together. Later we added failover so that if pcc wan links fails it should switch to fiber link, and if fiber link fails, it should failover to dsl by blending public ips into PCC.

  • Mikrotik have 4 DSL links which are configured in PCC (Load balancing) and serving local pppoe clients.
  • We have added another WAN Link via Fiber which is 1 STM (155mbps) and have acquire another large public pool for users which is routed to our /29 ip on mikrotik.
  • We have configured services in such a way that normal users gets private ip upon pppoe connectivity, and goes via PCC/Natting. and few services are configured in such a way that user gets public ip and goes to internet via public ip Routing, (bypass natting, preserving his public ip)
  • We have configured VLANs to isolate the different areas/networks to minimize the broadcast and for better network management. Also some corporate clients are connected to separate vlans to provide them public ip pool to be used in there routers.
  • We connected some corporate clients, which of course should not be connected via pppoe method, they wanted direct public ip so they can configure it in there own router/system. So we did it by connecting that client on our vlan switch,TAG there port traffic, and on mikrotik we added new vlan interface (accordingly ) and assign public ip (as required like /30) and assigned it to this new vlan interface, and gave appropriate ip to the client.
  • We have configured FAILOVER by using following techniques
  • 4 vdsl links (100mb each) are configured as PCC. For fail over we are using script that monitor 2 internet hosts for each wan link. we have also created forced route for those hosts with black holes as well to make sure the hosts goes via specific wan link only. once the script failed to ping those 2 hosts, it will simply enable rule in (ip/route/rules) TABLE to lookup the speciifc wan marked packets via main table where fiber link have distance value of 1 which will be default rule. thus traffic for that failed dsl link will start natting via fiber link. of course there are various other measurements need to be done, like proper natting rules, etc.
  • For fiber fail over (public ips) to dsl, we have script that checks for 2 hosts, if it fails, it will simply add the public ip pool to pcc pool as well, so the public pool also starts mixing with the pcc quern 😀
  • CCR performed amazingly good with complex configuration , lots of dynamic queues, and CPU usage usually remains under 10%. We can use PCQ base queues to lower the cpu usage in specific circumstances.
  • QOS is dynamically Done by the radius billing system. In this case DMASOFTLAB Radius Manager.
  • FTP are in DMZ, controlled by Mikrotik Firewall and separate QOS are setup to provide each user with 4 MB of downloads from local media server. This is done to prevent over utilization by each user. I used Queue type and then tag it with the simple queue for FTP. I also marked packets in mangle going to FTP, then later used in queues.
  • There are few other scripts configured like daily backup script, wan monitoring scripts, etc.
  • DDNS is also configured to access mikrotik and other servers/devices behind the MT, to pass through via PCC. port forwarding with the PCC is a bit tricky, and it requires additional rules in mangle and routes. I wrote about it in details in other posts.
  • Lot of port forwarding 🙂
  • Filter rules to block DDOSER, Block PING access ,Port Scanning etc…

 


TIPS for running NATING and ROUTING TOGETHER ~

Updated: 21-Sep-2016

As few asked how to run both natting and routing together in one router, here are few tips, (as I cannot post whole configuration because every network is different so rather then getting the code, try to understand the logic and apply it on your network if required)

Example:

We have Mikrotik RouterOS with 4 DLS(100mb x 4) links plus 1 Fiber Link (1 STM bandwidth) on /30 pool and two separate /24 pools (routed to /30 pool via ISP) for user end. We also have freeradius billing system where all users account / billing is managed. Mikrotik is acting as NAS / PPPoE Server as well.

Now we want that default normal group of clients should use these 4 dsl via PCC (using src-address approach as its more stable and have no ip changing issue) and selected group of users should get public ip and go directly via routing (while preserving their own public ip). here how I did this.

on Mikrotik I have defined two pools. One for the normal clients that will be natted using PCC. and other pool with public IP’s for users that will be routed.

1- pppoe_private_pool / 172.16.0.1-172.16.10.255
2- pppoe_public_pool / 123.0.0.1-123.0.1.255

In Mikrotik pppoe server, default pool for users is pppoe_private_pool (172.16.0.1-172.16.10.255) so any client connects to pppoe server will get ip from 172.16.x.x series. in PCC Mangle rules I defined 172.16.0.1-172.16.10.255 in src-address , this way only clients with these ip series will be processed via PCC and will go via dsl links. Same I did for default NAT rules, I defined this private pool in NAT src-address as well, This is must other wise all users will be natted (private or public). So make sure you pay attention to this portion.

Now to route public ips, I add the route of Fiber link ISP Gateway as Default route with distance value of 1 (all other routes of PCC have values of 2,3,4,5 (default routes for pcc / dsl is not required because they already have routes with marked traffic, but I still created default routes on dsl with different distance values to avail failover if one dsl fail , other should take over auto using MAIN TABLE) .

Then in radius panel, I created new services with pool defined “pppoe_public_pool”. and assign this profile to users whom I wanted to get public ip. this way when these particular users connects, they gets public IP, and they don’t processed by NAT rules and straightforward goes via routing table 🙂

Z@ib

 


Regard’s

Syed Jahanzaib

MRTG graph 120M Limitation

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

mrtg


If you are using MRTG and have gigabit network, you may notice that mrtg graphs will not show you traffic above then 120mb.  This is a common problem caused by 16-bit counter rollover. By default MRTG polls the device every 5min using SNMPv1, then a traffic greater than 120 Mbps will cause the 16 bit counter to wraparound in this time window.

therefore , MRTG only displays the lower traffic as it cannot tell how many times the counter has rolled over.

There are two workarounds to over come this issue.

  1. SNMP V2
  2. RRD

Quick Workaround:

I used SNMPv2. This is the best option, if your device supports it (Mikrotik do support SNMP v2). If using SNMPv2, then you can use the 64bit counters, which will not wrap around.

To do this, add

:::::2

(5 semicolons and 2) as a suffix to your Target definition to specify SNMPv2.


Working Example:

To edit existing configuration file.

Target[10.0.0.1_eth0]: #eth0:public@10.0.0.1:::::2

or with cfgmaker

cfgmaker public@10.0.0.1:::::2

I will write on RRD later which is the best option in my opinion , specially for heavy networks.

Regard’s
Syed Jahanzaib

June 13, 2016

CRON examples ! Focus and save yourself from embarrassment !

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

cron examples

 Following commands are made for reference purposes only … zaib


To add scheduled job in linux/ubuntu, use

crontab -e

To view installed cron

crontab -l

Examples:

Run Script after every 5 seconds [Updated 8th Aug, 2016]

I had a UPS monitor script that should run after every 5 seconds to poll the KE input voltage / battery remaining time etc. but as we know that with CRON we can configure interval of minimum 1 minute or above, we cannot set time in seconds. so I make following workaround.

conrtab -e

and added script as follows

# UPMON.SH is my script to monitor UPS
# 10.0.0.1 is my UPS network card
* * * * * /temp/upsmon.sh 10.0.0.1
* * * * * sleep 5; /temp/kemon.sh 10.0.0.1
* * * * * sleep 10; /temp/kemon.sh 10.0.0.1
* * * * * sleep 15; /temp/kemon.sh 10.0.0.1
* * * * * sleep 20; /temp/kemon.sh 10.0.0.1
* * * * * sleep 25; /temp/kemon.sh 10.0.0.1
* * * * * sleep 30; /temp/kemon.sh 10.0.0.1
* * * * * sleep 35; /temp/kemon.sh 10.0.0.1
* * * * * sleep 40; /temp/kemon.sh 10.0.0.1
* * * * * sleep 45; /temp/kemon.sh 10.0.0.1
* * * * * sleep 55; /temp/kemon.sh 10.0.0.1
* * * * * sleep 60; /temp/kemon.sh 10.0.0.1

this way the script will run and wait for five seconds before next execution. 🙂 lalalala / zaib


Run Script after every 2 hours (daily basis* it helped me very much)

0 */2 * * * /temp/script.sh

Run Script at Every 1st day of Month [Every Month]

@monthly /temp/script.sh


Run Script on 1-am on every Monday

0 1 * * MON /temp/xdrive_noupdate.sh


Run Script Daily at 00:00 hours (midnight)

@daily /temp/script.sh

Run Script Daily at 09:00 hours (Morning 9am)

# m h dom mon dow command
00 09 * * * /temp/fullbackup.sh


Run Script every hour 

@hourly /temp/script.sh


Run Script every minute

* * * * * /temp/script.sh


Run Script after every 5 minutes

*/5 * * * * /temp/script.sh


Run Script on Specific Timings And Date of Months, Example run script on 10am and 11am  on 12th of every month.

00 10,11 12 * * /temp/script.sh


Run Script on Specific Hours of Every Month, Example run script on 9am of every month.

00 9 10 * * /temp/script.sh


Run Script on Specific Hours RANGES , Example run script on 10am to 8pm , means every hour from 10am-8pm

00 10-20 * * * /temp/script.sh


Shortcuts in CRON

         
@reboot        Run once, at startup.
@yearly        Run once a year, "0 0 1 1 *".
@annually      (same as @yearly)
@monthly       Run once a month, "0 0 1 * *".
@weekly        Run once a week, "0 0 * * 0".
@daily         Run once a day, "0 0 * * *".
@midnight      (same as @daily)
@hourly        Run once an hour, "0 * * * *".


Scheduled CON Job running but not producing results …

[Monday 27th Feb,2017]

I scheduled few scripts on my Ubuntu 14.x box that queries remote servers for storage and send customized reports via email. I can see its execution in /var/log/syslog but the script was not able to query the remote win server.

To solve it I had to add the PATH command in the script , sample as below…

#!/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

This solved my issue 🙂

These are little things that you learn on daily basis ,

One very useful crontab demonstration to check errors or make cron

https://crontab.guru/


Run script after X minute (just one time only, without using cron)

echo YOUR-SCIPT.SH | at now + 1 minute


Disabling CRON job Emails!

You may want to read this.

https://aacable.wordpress.com/2018/06/08/disabling-email-for-cron-jobs/


Regard’s
Syed Jahanzaib

Sending Email/SMS Alert to User for Service Change Event

Filed under: Radius Manager — Tags: — Syed Jahanzaib / Pinochio~:) @ 12:58 PM

srvchange

 

Screenshot_2016-06-13-12-56-48

SMS Alert


Reference Notes:

Requirements:

We want to send email/sms alert to user about his service package change with old/new package name details. Although this function is builtin in RM , but with customized scripts we can do other functions as well.


Solution:

We will create mysql trigger that will be executed every time srvid column will be changed in rm_users table. then we will create mysql table which will hold all these info. Then the trigger will add user info like old service id , new service id, user name, mobile etc in this table upon srvid change.

Neat & clean.


 

First create mySQL trigger which will be executed once there will be changes made in srvid column in rm_users table.

1- mySQL Trigger

Create file name srvchangetriggers.sql and paste following data

-- MySQL dump 10.13 Distrib 5.5.46, for debian-linux-gnu (i686)
-- Host: localhost Database: radius
-- Syed Jahanzaib
-- ------------------------------------------------------
-- Server version 5.5.46-0ubuntu0.12.04.2-log
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `myTrigger` BEFORE UPDATE ON `rm_users`
FOR EACH ROW BEGIN
IF NEW.srvid <> OLD.srvid THEN
INSERT INTO rm_usersrvchangehistory (datetime, username, newsrvid, oldsrvid, firstname, lastname, mobile) VALUES (NOW(), new.username, new.srvid, old.srvid, new.firstname, new.lastname, new.mobile);
END IF;
END */;;
DELIMITER ;
-- Dumping routines for database 'radius'
--

2- mySQL Table

Add mySQL table where records will be saved.

Create file name rmsrvchangetable.sql and paste following date

-- phpMyAdmin SQL Dump
-- version 3.4.10.1deb1
-- http://www.phpmyadmin.net
-- Syed Jahanzaib
-- Host: localhost
-- Generation Time: Jun 13, 2016 at 10:32 AM
-- Server version: 5.5.46
-- PHP Version: 5.3.10-1ubuntu3.21
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!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 */;
--
-- Database: `radius`
--
-- --------------------------------------------------------
--
-- Table structure for table `rm_usersrvchangehistory`
--
CREATE TABLE IF NOT EXISTS `rm_usersrvchangehistory` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`datetime` datetime NOT NULL,
`username` varchar(64) NOT NULL,
`newsrvid` varchar(64) NOT NULL,
`oldsrvid` varchar(64) NOT NULL,
`firstname` varchar(64) NOT NULL,
`lastname` varchar(64) NOT NULL,
`mobile` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=63 ;
--
-- Dumping data for table `rm_usersrvchangehistory`
--
/*!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 */;

Importing the .sql files in Radius DB / mySQL

Now import both files  in radius DB by command

mysql -uroot -pSQLPASS radius < rmsrvchangetable.sql
mysql -uroot -pSQLPASS radius < srvchangetriggers.sql

Test The Changes …

Now try to change any user service, and check rm_usersrvchangehistory by following command

root@ubuntu:/temp# mysql -u root -pSQLPASS -e "use radius; select * from rm_usersrvchangehistory;"
+----+---------------------+----------+----------+----------+-----------+-----------+-------------+
| id | datetime | username | newsrvid | oldsrvid | firstname | lastname | mobile |
+----+---------------------+----------+----------+----------+-----------+-----------+-------------+
| 71 | 2016-06-13 12:24:00 | test | 4 | 13 | syed | jahanzaib | 03333021909 |
+----+---------------------+----------+----------+----------+-----------+-----------+-------------+

Script to fetch data on scheduled basis and SMS/EMAIL…

Create a script that will be scheduled to run after every 5 minutes , it will check in table rm_usersrvchangehistory and will send sms to user about package change event.

mkdir /temp && cd /temp
touch /temp/srvchange.sh
chmod +x temp/srvchange.sh
nano temp/srvchange.sh

and paste following data…

the Script:

 

#!/bin/bash
# srvchange.sh
# Bash script which will run after every 5 minutes and will fetch info from mysqltable
# and will send SMS/Email alert for service change event.
# Created by SYED JAHANZAIB
# aacable@hotmail.com
# https://aacable.wordpress.com
# Created : 13-JUN-2016
#set -x
SQLUSER="root"
SLQPASS="SQLPASS"

# File where user info wil be hold temporary
TMPUSRINFO=/tmp/usersrvinfo.txt

# Interval in minutes to check user record
INTERVAL="5"

# Fetch user info from the table.
mysql -uroot -p$SQLPASS --skip-column-names -e "use radius; select * from rm_usersrvchangehistory WHERE datetime >= NOW() - INTERVAL $INTERVAL MINUTE;" > $TMPUSRINFO

# KANNEL DETAILS
KHOST="127.0.0.1:13013"
KID="kannel"
KPASS="kannelpass"

# Company Footer
COMPANY="JZ_ISP"

# Apply Count Loop Formula while deleting first line which have junk text
num=0
cat $TMPUSRINFO | while read users
do
num=$[$num+1]
username=`echo $users | awk '{print $4}'`
firstname=`echo $users | awk '{print $7}'`
lastname=`echo $users | awk '{print $8}'`
mobile=`echo $users | awk '{print $9}'`
date=`echo $users | awk '{print $2,$3}'`
newsrvid=`echo $users | awk '{print $5}'`
oldsrvid=`echo $users | awk '{print $6}'`

# Print Info on screen
# Fetch old/new Package Name
OLDPKGNAME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvname FROM radius.rm_services WHERE rm_services.srvid = '$oldsrvid';" |awk 'FNR == 2'`
NEWPKGNAME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvname FROM radius.rm_services WHERE rm_services.srvid = '$newsrvid';" |awk 'FNR == 2'`

# Print FINAL Fetched info
echo "Dear $firstname $lastname ,
Your internet package against your User ID: $username has been upgraded from $OLDPKGNAME to $NEWPKGNAME !

$COMPANY"

# Store Info for sending SMS in /tmp folder where we will call kannel to send customized SMS
echo "Dear $firstname $lastname ,
Your internet package against your User ID: $username has been upgraded from $OLDPKGNAME to $NEWPKGNAME !

$COMPANY" > /tmp/$username.srvchange.sms

# send sms using kannel gateway
curl "http://$KHOST/cgi-bin/sendsms?username=$KID&password=$KPASS&to=$mobile" -G --data-urlencode text@/tmp/$username.srvchange.sms

# If you send lot of SMS via local mobile SIM, then make sure you give enough delay so that your SIM may not get blocked by BULK SMS monitor by TELCOM authority like PTA.
#sleep 15
done
# once done, we should delete the .sms files to clear the garbage
rm -fr /tmp/*.sms


End Results !

Now execute the Script  and witness the Fun !

srvchange


Regard’s
Syed Jahanzaib

KANNEL not responding problem occasionally or after modem reset

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

kannellogo


Scenario:

We have Ubuntu 12.x installed and serial modem is attached (Teltonika G10). Kannel is installed and serving as SMS gateway.

Problem:

Sometimes modem stops responding and when we restart the modem, kannel starts giving error of Routing Failed. After restart of KANNEL service, the kannel starts responding to sms request and works fine.

Requirement:

This little issue can be really annoyed if you are doing remote administration OR if sms alerts are important for you to keep informed about various aspects of the network. We want some automatic mechanism that can detect this specific error and then act accordingly.

Example …

  • If KANNEL service is down , try to start it
  • If failed to start the service, then print error, and sends email about incident, (one email per incident), and exit script
  • If service starts successfully, then print info, and sends email about successful attempt. (one email per incident), and continue further
  • Test Kannel log last entries, and look for word Routing failed, if found, then restart kannel service and email. (one email per incident).
  • If it does not found any entry in LOG for specific word, then Print OK Result for all.

Solution!

 

Disclaimer:

This may or may not work for you. Because I made it for some very particular situation, and this script helped. So this script checks for two things only. 1st kannel service, 2nd kannel log error for routing failed. I am sure this may or may not help you in same situation because its possible that error can be due to something else. I carefully examined my situation and made script for it. You can get some idea by it and modify the script as required.

Schedule Script to run after every 15 minutes or whatever.

Cron Example

# Run KANNEL MONITOR Script after every 15 minutes
*/15 * * * * /temp/kanneltest.sh

 


the SCript !

mkdir /temp
cd /temp
touch /temp/kanneltest.sh
chmod +x /temp/kanneltest.sh
nano /temp/kanneltest.sh

Copy paste following script. Make sure you read it carefully and modify things accordingly.


#!/bin/bash
# No part of this script is copied from anywhere, you are free to use it, modify or distribute.
# Linux BASH script to check KANNEL service Status and internal component system (example kannel service is ok,but not responding to request giving error in logs
# If KANNEL not responding, then send EMail alert to admin (trigger one time for each status changed)
# Useful for remote admins, who want to be informed when KANNEL have any responding problem.
# Created: 9th-JUNE-2016
# Syed Jahanzaib
# aacable at hotmail dot com / https://aacable.wordpress.com
#set -x
# 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"
# Service name to monitor ...
KANNEL="kannel"
KANNEL_SERVICE_ERROR="kannel_main_service_down"
KANNEL_BEARERBOX="bearerbox"
# Text that will be checked in kannel logs output file,
# So we will simply use the grep to catch word **Routing failed**. You may use other technique that may work for you. Chill / zaib
TEXT_TO_CATCH="Routing failed"
# Hostname and other Variables
HOSTNAME=`hostname`
COMPANY="zaib (Pvt) Ltd."
FOOTER="Powered By Syed.Jahanzaib"
DATE=`date`
# Temporary file holder for KANNEL status
KANNEL_STATUS_TMP_HOLDER="/tmp/KANNELstatus.txt"
KANNEL_SERVICE_TMP_HOLDER="/tmp/kannelservice.txt"
KANNEL_SERVICE_TMP_HOLDER_ERR="/tmp/kannel_servic_error.txt"
KANNEL_QUERY_RESULT="/tmp/KANNEL_query_result.txt"
KANNEL_LOG_FILE="/var/log/kannel/bearerbox.log"
# Create temp file if not already present, usually for 1st time execution
touch $KANNEL_STATUS_TMP_HOLDER
touch $KANNEL_SERVICE_TMP_HOLDER
touch $KANNEL_SERVICE_TMP_HOLDER_ERR
touch $KANNEL_QUERY_RESULT
# EMAIL RELATED and KANNEL INFO
# for down status, we have to use GMAIL to send email
KANNELURL="127.0.0.1:13013"
KANNELID="kannel"
KANNELPASS="KANNELPASS"
CELL1="03333021909"
# GMAIL Section
GMAILID="YOURGMAILID@gmail.com"
GMAILPASS="YOURGMAILPASS"
ADMINMAIL1="aacableAThotmail.com"

###########################################################################################
# Testing KANNEL Service bearerbox status by its PID
echo -e "$COL_RED
Step 1:$COL_RESET INFO: Testing $KANNEL Service status , testing its PID ..."
PID=`pgrep $KANNEL_BEARERBOX`
if [ -n "$PID" ]; then
echo -e "$KANNEL Service Status = $COL_GREEN OK $COL_RESET with pid $PID"
sed -i "/$KANNEL_SERVICE_ERROR/d" "$KANNEL_SERVICE_TMP_HOLDER"
else
echo -e "$COL_RED$KANNEL Service = NOT RUNNING, trying to restarting it ...$COL_RESET"
service $KANNEL stop > /dev/null 2>&1
sleep 5
killall -9 $KANNEL_BEARERBOX > /dev/null 2>&1
sleep 2
service $KANNEL start > /dev/null 2>&1
sleep 5
# IF KANNEL MAIN SERVICE found DOWN, and email/SMS not already sent, then send it one time to $CELL1/x
PID=`pgrep $KANNEL_BEARERBOX`
if [ -z "$PID" ]; then
KSRVAFTRES="DOWN"
echo -e "Script tried to restart $KANNEL MAIN SERVICE and final status is = $COL_RED $KSRVAFTRES $COL_RESET..."
else
KSRVAFTRES="UP/OK"
echo -e "Script tried to restart $KANNEL MAIN SERVICE and final status is = $COL_GREEN $KSRVAFTRES .$COL_RESET..."
if [ $(grep -c "$KANNEL_SERVICE_ERROR" "$KANNEL_SERVICE_TMP_HOLDER") -eq 0 ]; then
echo -e "$COL_RED Sending SMS/EMAIL for KANNEL MAIN SERVICE...$COL_RESET"
PID=`pgrep $KANNEL_BEARERBOX`
if [ -n "$PID" ]; then
KSRVSTATUS="UP/OK"
echo -e "$KANNEL sevice was not running, After script attempt to restart it, its status is $COL_GREEN $KSRVSTATUS $COL_RESET with PID $PID"
KSRVUPMSG="/tmp/ksrvup.msg"
echo "$KANNEL sevice was not running, After script attempt to restart it, its status is $KSRVSTATUS with PID $PID" > $KSRVUPMSG
/temp/sendEmail-v1.56/sendEmail -u "ALERT: $KANNEL service was not working and finally its $KSRVSTATUS @ $DATE" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$KSRVUPMSG -o message-content-type=text
else
KSRVSTATUS="DOWN"
echo -e "After kannel restart attempt, its status is still $COL_RED $KSRVSTATUS, $COL_RESET... SMS/EMAIL Already sent ..."
#Updating its down log, so script should not repeat sms/email sending
echo "$KANNEL_SERVICE_ERROR" > $KANNEL_SERVICE_TMP_HOLDER
fi
fi
fi
fi

###########################################################################################
# Testing again if above step have started the service or not , if not EXIT the script with the error
# Because if kannel is not running, then checking modem is useless, kannel must be running first in order to proceed further
PID=`pgrep $KANNEL_BEARERBOX`
if [ -n "$PID" ]
then
echo ""
else
echo -e "$COL_REDALERT ALERT: $KANNEL service failed to respond on Service restart request .... please check$COL_RESET
$COL_RED Script cannot continue, exiting ...$COL_RESET"
#exit 0
fi

############## zaib final down
PID=`pgrep $KANNEL_BEARERBOX`
if [ -z "$PID" ]; then
if [ $(grep -c "$KANNEL_SERVICE_ERROR" "$KANNEL_SERVICE_TMP_HOLDER_ERR") -eq 0 ]; then
KSRVSTATUS="DOWN"
KSRVDOWNMSG="/tmp/ksrvdown.msg"
echo "After kannel restart attempt by the script, its status is still $KSRVSTATUS. You need to check it manualy. Now only Human can see what is going on ... "
echo "After kannel restart attempt by the script, its status is still $KSRVSTATUS. You need to check it manualy. Now only Human can see what is going on ... " > $KSRVDOWNMSG
/temp/sendEmail-v1.56/sendEmail -u "ALERT: $KANNEL service failed to restart. Status = $KSRVSTATUS @ $DATE / Check it manualy!" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$KSRVDOWNMSG -o message-content-type=text
#Updating its down log, so script should not repeat sms/email sending
echo "$KANNEL_SERVICE_ERROR" > $KANNEL_SERVICE_TMP_HOLDER_ERR
exit 0
fi
else
sed -i "/$KANNEL_SERVICE_ERROR/d" "$KANNEL_SERVICE_TMP_HOLDER_ERR"
fi

###########################################################################################
PID=`pgrep $KANNEL_BEARERBOX`
if [ -n "$PID" ]
then
echo ""
else
echo -e "$COL_REDALERT ALERT: $KANNEL service failed to respond on Service restart request .... please check$COL_RESET
$COL_RED Script cannot continue, exiting ...$COL_RESET"
exit 0
fi

###########################################################################################
# Run the script to test kannel internal processing ...
echo -e "$COL_RED
Step 2:$COL_RESET
Testing Kannel internal Service Status in LOGS for internal responding @DATE ..."
for KANNEL in $KANNEL
do
# Match $TEXT_TO_CATCH in the log file grep query result
STATUS=`tail -n 10 $KANNEL_LOG_FILE | grep "$TEXT_TO_CATCH"`
if [ -n "$STATUS" ]; then
# Print Result for information purposes
echo "ALERT: text ** $TEXT_TO_CATCH ** found in $KANNEL_QUERY_RESULT"
echo -e "$COL_RED
$KANNEL service is UP but internal system is not responding to SMS ... Trying to restarting $KANNEL service ...$COL_RESET"
# IF KANNEL found DOWN, and email/SMS not already sent, then send it one time to $CELL1/x
if [ $(grep -c "$KANNEL" "$KANNEL_STATUS_TMP_HOLDER") -eq 0 ]; then
# Restart the KANNEL service
service kannel stop > /dev/null 2>&1
sleep 5
killall -9 bearerbox > /dev/null 2>&1
sleep 2
service kannel start > /dev/null 2>&1
sleep 5
echo -e "$COL_RED
$KANNEL service is UP but internal system is not responding $DATE .. SENDING DOWN SMS/EMAIL for current incident / for one time only ...$COL_RESET"
# Update KANNEL down status in a file so that sms/email should not be sent again and again
echo "$KANNEL" > $KANNEL_STATUS_TMP_HOLDER
# Sending DOWN SMS via KANNEL
echo -e "$COL_REDSending SMS , if KANNEL SERVICE is down or internal system not responding, SMS will NOT be sent ...$COL_RESET
"
# Update down message to be sent via sms/email
echo "$MSG_DOWN" > $MSGDOWNHOLDER
# sen email using sendemail tool
cat $MSGDOWNHOLDER | curl "http://$KANNELURL/cgi-bin/sendsms?username=$KANNELID&password=$KANNELPASS&to=$CELL1" -G --data-urlencode text@-
######## EMAIL SECTION ##############
# 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 -e "$COL_REDSending EMAIL ALERT to $ADMINMAIL1 ...$COL_RESET
"
/temp/sendEmail-v1.56/sendEmail -u "ALERT: $KANNEL not responding @ $DATE" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$MSGDOWNHOLDER -o message-content-type=text
fi

###########################################################################################
# Else if $TEXT_TO_CATCH does not found in $KANNEL_QUERY_RESULT, then consider KANNEL is UP
else
echo -e "$COL_GREEN
INFO:$COL_RESET Word ** $TEXT_TO_CATCH ** was NOT found in $KANNEL_LOG_FILE
$COL_RED
FINAL RESULT:$COL_RESET
$KANNEL service = $COL_GREEN OK $COL_RESET
$KANNEL iternal system = $COL_GREEN OK $COL_RESET / All seems to be responding OK!
"
# If KANNEL found UP and last status was DOWN, then send UP msg one time
if [ $(grep -c "$KANNEL" "$KANNEL_STATUS_TMP_HOLDER") -eq 1 ]; then
echo "$KANNEL internal system is now responding at $(date)... SENDING UP SMS for current incident one time only ...
"
echo "$MSG_UP" > $MSGUPHOLDER
# Sending UP SMS via KANNEL
echo -e "Sending $COL_GREENUP$COL_RESET SMS , if KANNEL process is down, sms will not be sent ...
"
cat $MSGUPHOLDER | curl "http://$KANNELURL/cgi-bin/sendsms?username=$KANNELID&password=$KANNELPASS&to=$CELL1" -G --data-urlencode text@-
############## EMAIL SECTION ##############
# 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 SEMAIL UP/OK to $ADMINMAIL1 & $ADMINMAIL2 ...
"
/temp/sendEmail-v1.56/sendEmail -u "INFO: $KANNEL internal system is now responding OK @ $DATE" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$MSGDOWNHOLDER -o message-content-type=text
# Update KANNEL UP status in a file so that sms/email should not be sent again and again
sed -i "/$KANNEL/d" "$KANNEL_STATUS_TMP_HOLDER"
fi
fi
done
# Script Ends Here ...
# z@iB


Script Results:

1- script execute result

 

1- srv down and restarted ok up result

 

1- srv down failed to restart

1-upndown

3 -intok


Regard’s
Syed Jahanzaib

June 9, 2016

BASH Script to check modem status and alert by email/sms accordingly

Filed under: Linux Related — Tags: , , , , , , — Syed Jahanzaib / Pinochio~:) @ 12:31 PM

teltonika

wavewcom

 

Disclaimer:

This is a neat hack to query modem status and alert admin if found Down. This is no way a standard method to perform this task but I used it as it worked fine for some specific environment. I made this because first , I was unable to find any good solution for this, second, making our customized solution is always far better then ready made tools. because with customization, we can get our desired results by folding/molding and twisting about anything that gets in our way :~)


SCENARIO!

Serial modem is attached to Linux (Ubuntu) system for sending receiving sms via locally installed KANNEL as gateway. Rarely sometimes modem stops responding to sms requests and it require physical power off/on. if system is not very actively monitored by admin, or remote administration is being done, then we require a solution that can alert us by EMAIL if modem stopped working. and when it start working again, send us Email+SMS for the event. But it should not repeatedly send sms/email for same incident.Some checks must be there in order to prevent this.


REQUIREMENTS!

1- Make sure you have WVDIALCONF utility before executing this script. you can install wvidalconf in UBUNTU by following command

sudo apt-get install wvdial

2- for Email part, I used sendEmail which will use GMAIL account to send email.To install it use

TO Install sendEmail Tool …

mkdir /temp
cd /temp
wget http://caspian.dotconf.net/menu/Software/SendEmail/sendEmail-v1.56.tar.gz
tar zxvf sendEmail-v1.56.tar.gz
cd sendEmail-v1.56/

ADD SUPPORTING LIBRARY

for ubuntu

apt-get -y install libio-socket-ssl-perl libnet-ssleay-perl perl
for centos
yum -y install perl perl-Crypt-SSLeay perl-IO-Socket-SSL

SOLUTION!

the SCRIPT

Schedule (cron) the following to run after every hour or as required.

 

Use following script.

#!/bin/bash
# Linux BASH script to check MODEM Status (example wavecom or teltnokia serial modem or any other using wvdialconf)
# If Modem not responding, then send SMS/EMail alert to admin (trigger one time for each status changed)
# Useful for remote admins, who want to be informed when modem have any responding problem.
# Created: 9th-JUNE-2016
# Syed Jahanzaib
# aacable at hotmail dot com / https://aacable.wordpress.com
# set -x

# If you have kannel configured, then STOP KANNEL SERVICE first, so that it should not conflict with the wvdialconf results / locked pid / syed.jahanzaib
# If you dont have kannel, remote these entries
echo "Stopping Kannel Service to test modem results and sleep for 10 seconds so that kannel service can be shutdown gracefully..."
service kannel stop > /dev/null 2>&1
sleep 10
killall -9 bearerbox > /dev/null 2>&1
echo "Kannel Service Stopped . Processing further ..."

# You can name your modem, MAKE SURE THAT YOU ***DONOT*** ADD SPACES IN IT
MODEM1="SERIAL_MODEM"

# Text that will be checked in wvdialconf output file, if wvdialconf does not detects any modem, it usually add the following line
# Sorry, no modem was detected! Is it in use by another program?
# So we will simply use the grep to catch word **no modem**. You may use other technique that may work for you. Chill / zaib
TEXT_TO_CATCH="no modem"

# Hostname and other Variables
HOSTNAME=`hostname`
COMPANY="zaib (Pvt) Ltd."
FOOTER="Powered By Syed.Jahanzaib"
DATE=`date`

# Temporary file holder for MODEM status
MODEM1_STATUS_TMP_HOLDER="/tmp/modemstatus.txt"
MODEM_QUERY_RESULT="/tmp/modem_query_result.txt"
# Create temp file if not already present, usually for 1st time execution
touch $MODEM1_STATUS_TMP_HOLDER
touch $MODEM_QUERY_RESULT

# SMS RELATED and KANNEL INFO
# KANNEL SMS Gateway Info, will not be useful when modem is down, however it will be ok when moem will respond,
# for down status, we have to use GMAIL to send email
KANNELURL="127.0.0.1:13013"
KANNELID="kannel"
KANNELPASS="KANNELPASS"
CELL1="03333021909"

# GMAIL Section
GMAILID="YOURGMAILID@gmail.com"
GMAILPASS="YOURGMAILPASS"
ADMINMAIL1="TO@hotmail.com"

# sms/email message temporary holder
MSGDOWNHOLDER="/tmp/$MODEM1_down.msg"
MSGUPHOLDER="/tmp/$MODEM1_up.msg"

## SMS/EMAIL Messages for DOWN
MSG_DOWN="ALERT: SMS $MODEM1 not responding @ $DATE. Try to restart it by power off/on.

$COMPANY
$FOOTER"

## SME/EMAIL Messages for UP
MSG_UP="INFO: SMS $MODEM1 is now responding @ $DATE OK.

$COMPANY
$FOOTER"

# RUN WVDIALCONF utility to output modem query result in $MODEM_QUERY_RESULT file
wvdialconf > $MODEM_QUERY_RESULT

# Run the script
echo "Checking $MODEM1 Current Status @$DATE..."
for MODEM in $MODEM1
do

# Match $TEXT_TO_CATCH in the query result
STATUS=`cat $MODEM_QUERY_RESULT | grep "$TEXT_TO_CATCH"`
if [ -n "$STATUS" ]; then

# Print Result for information purposes
echo "ALERT: text ** $TEXT_TO_CATCH ** found in $MODEM_QUERY_RESULT"
echo "$MODEM seems to be DOWN ..."

# IF modem found DOWN, and email/SMS not already sent, then send it one time to $CELL1/x
if [ $(grep -c "$MODEM" "$MODEM1_STATUS_TMP_HOLDER") -eq 0 ]; then
echo "$MODEM is down at $(date) .. SENDING DOWN SMS/EMAIL for current incident / for one time only ..."

# Update down message to be sent via sms/email
echo "$MSG_DOWN" > $MSGDOWNHOLDER

# Update Modem down status in a file so that sms/email should not be sent again and again
echo "$MODEM1" > $MODEM1_STATUS_TMP_HOLDER

#cat $MSGDOWNHOLDER

# Sending DOWN SMS via KANNEL
echo "Sending SMS , if modem is down, sms will not be sent ..."
cat $MSGDOWNHOLDER | curl "http://$KANNELURL/cgi-bin/sendsms?username=$KANNELID&password=$KANNELPASS&to=$CELL1" -G --data-urlencode text@-

######## EMAIL SECTION ##############
# 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 SEMAIL ALERT to $ADMINMAIL1  ..."
/temp/sendEmail-v1.56/sendEmail -u "ALERT: $MODEM1 not responding @ $DATE" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$MSGDOWNHOLDER -o message-content-type=text
fi

# Else if $TEXT_TO_CATCH does not found in $MODEM_QUERY_RESULT, then consider MODEM is UP
else
echo "INFO: Word ** $TEXT_TO_CATCH ** was NOT found in $MODEM_QUERY_RESULT"
echo "$MODEM seems to be responding OK!"

# If modem found UP and last status was DOWN, then send UP msg one time
if [ $(grep -c "$MODEM" "$MODEM1_STATUS_TMP_HOLDER") -eq 1 ]; then
echo "$MODEM is now responding at $(date)... SENDING UP SMS for current incident one time only ..."
echo "$MSG_UP" > $MSGUPHOLDER
#cat $MSGUPHOLDER

# START KANEL SERVICE SO THAT UP INFO SMM CAN BE SENT to ADMIN
echo "Starting Kannel Services so that UP sms can be sent via kannel (with 5 seconds delay) ..."
service kannel start > /dev/null 2>&1
sleep 5

# Sending UP SMS via KANNEL
echo "Sending UP SMS , if modem is down, sms will not be sent ..."
cat $MSGUPHOLDER | curl "http://$KANNELURL/cgi-bin/sendsms?username=$KANNELID&password=$KANNELPASS&to=$CELL1" -G --data-urlencode text@-

############## EMAIL SECTION ##############
# 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 SEMAIL UP/OK to $ADMINMAIL1 & $ADMINMAIL2 ..."
/temp/sendEmail-v1.56/sendEmail -u "INFO: $MODEM1 responding now OK @ $DATE" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$MSGDOWNHOLDER -o message-content-type=text

# Update Modem UP status in a file so that sms/email should not be sent again and again
sed -i "/$MODEM1/d" "$MODEM1_STATUS_TMP_HOLDER"
fi
fi
done

# START KANEL SERVICE IN END, because we stopped it in the beginning
echo "Starting Kannel Services that we stopped in beginning of this script ..."
service kannel start > /dev/null 2>&1

# Script Ends Here ...
# z@iB

RESULT!

1- DOWN result

Screenshot_2016-06-09-11-24-08

 

June 8, 2016

Mikrotik: Script to re-connect wan pppoe-outx after X hours

Filed under: Mikrotik Related — Tags: , — Syed Jahanzaib / Pinochio~:) @ 12:36 PM

link

Example of Mikrotik Script to execute customized function IF condition matches !


Requirements:

  • Mikrotik should disconnect wan client pppoe-out1 , only if its uptime is above 8 hours for that single session. There should be several checks like the script should check for following (to avoid errors)
  • Check for valid interface name , if interface not found, print error
  • Check for Running Status, if the pppoe-out interface is not connected to ppp server, print error
  • If the pppoe-out client uptime is above then the defined maximum uptime limit, it should disable interface, wait for 5 seconds, and then re-connect
  • Check if interface is in running status, print its IP address an strip its subnet

Solution:

the Script !

 

# Mikrotik Script to monitor UPTIME of the PPPOE-OUT(x) WAN interface, and act accordingly
# Useful when you want to disconnect your wan interface for any reason if it uptime crosses max up time limit defined. 
# Maybe to hide router form Remote PPP Servers uptime Monitoring System

# Syed Jahanzaib / aacable @ hotmail . com / https://aacable.wordpress.com
# Tested with Mikrotik 6.35.2 / on RB3011UiAS (arm)
# Created: 8-JUN-2016

# Define which interface uptime you want to monitor, in this example, i used pppoe-out1, you may change it as required
:local PPPINT pppoe-out1;

# Define the UPTIME Limit that will be matched with uptime, Example is 8 hours
:local MAXUPTIMELIMIT 08:00:00;

# Define how long it should wait before re-connecting / re-enable wan interface, example 5 seconds
:local DELAY 5s;

###############################
# SCRIPT FUNCTIONS STARTS HERE ...
###############################

# Check if interface is available or not, IF NOT THEN EXIT
:if ([:len [/interface find name=$PPPINT]] = 0 ) do={ :log error "WARNING: No interface named $PPPTINT, please check configuration." }

# Check for Interface Running Status, if its not connected then give error
:global PPPINTSTATUS;
/interface pppoe-client monitor $PPPINT once do={ :set PPPINTSTATUS $status}
:if ($PPPINTSTATUS != "connected") do={
:log error "$PPPINT NOT CONNECTED TO THE REMOTE SERVER YET, NOT READY";
}

# Define variable to hold current uptime value of the interface
:local wanuptime;
/interface pppoe-client monitor $PPPINT once do={
:set $wanuptime $uptime;

# Print Uptime , just for testing
#:log warning "$PPPINT UP Time is > $uptime";
}

# Match interface current uptime with maximum uptime limit defined in MAXUPTIMELIMIT variable
# Greater then forumla / zaib
:if ($wanuptime>$MAXUPTIMELIMIT) do={
:log error "ALERT: $PPPINT UP Time have crossed $MAXUPTIMELIMIT Hours Limit ... Disconnecting it and will Re-Connect after 5 Seconds";

# Disable $PPPINT interface
/interface disable $PPPINT
delay $DELAY;
/interface enable $PPPINT
:log warning "$PPPINT have been enabled , check if its connected properly."
:delay $DELAY
:local PPPINTIP [ /ip address get [/ip address find interface=$PPPINT] address ]
:for i from=( [:len $PPPINTIP] - 1) to=0 step=-1 do={ 
:if ( [:pick $PPPINTIP $i] = "/") do={ 
:set PPPINTIP [:pick $PPPINTIP 0 $i]
:log warning "$PPPINT ip address is > $PPPINTIP / Script ENDS here ..."
}
}

# Show under limit message. if all ok

:if ([/interface get $PPPINT value-name=running]) do={
:if ($wanuptime<$MAXUPTIMELIMIT) do={
:log warning "$PPPINT UP-Time $wanuptime is under $MAXUPTIMELIMIT Hours Limit / Script ENDS here ...";

:local PPPINTIP [ /ip address get [/ip address find interface=$PPPINT] address ]
:for i from=( [:len $PPPINTIP] - 1) to=0 step=-1 do={ 
:if ( [:pick $PPPINTIP $i] = "/") do={ 
:set PPPINTIP [:pick $PPPINTIP 0 $i]
:log warning "$PPPINT ip address is > $PPPINTIP"
} 
}
}
}
}

Regard’s
Syed Jahanzaib

June 2, 2016

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

Filed under: Linux Related, Mikrotik Related, Radius Manager — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 4:52 PM

codes


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

mikrotik log error full pool

 


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.

mtdhcplog

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 !

5- table result


EMAIL ALERT SAMPLE #1

 

2- dhcp alert amil sub

EMAIL ALERT SAMPLE #2


3- dhcp billing alert full mail

 


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.

 

Older Posts »

%d bloggers like this: