Syed Jahanzaib Personal Blog to Share Knowledge !

June 26, 2015

RADIUS Redundancy by using MYSQL Master-Master Replication

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

master-master

In this Guide, I will show you howto create replica of your radius server so that in case of any server failure , you can instantly switch to backup server with the latest data available. In this model we will use MYSQL master-master concept in which whatever changes / records you make on any server, it will replicate to other as well. Also in mikrotik we can use primary and secondary radius server entries OR we can make a script to detect both radius status and act accordingly, all depend on your network requirements & infrastructure.

Scenario:

In this example we have RADIUS MANAGER billing system which uses freeradius and MYSQL DB as its backend engine,  installed (with basic level of installation) on two servers. Now we want to create redundancy by replicating radius DB to each other so that in case of one server failure, second server should come to rescue.

Requirements:

  • I assume that you have working radius manager installed on both PC and tested its working by creating users in it.

Components Used:

  • SERVER1 NAME = MASTER-RADIUS
    OS = Centos 6.5 32bit
    IP = 101.11.11.241
  • SERVER2 NAME = REPLICA-RADIUS
    OS = Centos 6.5 32bit
    IP = 101.11.11.245
  • MIKROTIK PPPOE SERVER = Mikrotik
    OS = Mikrotik 5.xx
    IP = 101.11.11.255

Let’s Start

 

Step – 1

Server1 = ‘master-radius’ Configuration

Open mysql config file

nano /etc/my.cnf

and add following under [mysqld] section

log-bin=mysql-bin
binlog-do-db=radius
server-id=1
auto_increment_increment = 2
auto_increment_offset = 1

SAVE and EXIT.

Now restart mysqld service so changes can take effect.

service mysqld restart

Now we need to create a user that will be used by mysql for replicating data between our two radius (or mysql) servers. As an example I am using id “zaib”. Replace “password” with the password you wish to use for replication.

create user 'zaib'@'%' identified by 'password';
grant replication slave on *.* to 'zaib'@'%'; 

Now we need to get some information about the current MySQL instance which we will later provide to server2 (replica).

The following command will output a few pieces of important information, which we will need to make note of:

show master status;

The output will look similar to the following, and will have two pieces of critical information: [file and position note it down)

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      336 | radius       |                  |
+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

We need to make a note of the file and position which will be used in the next step.


 

Step – 2

Server2 = ‘replica-radius’ Configuration

 

Open mysql config file

nano /etc/my.cnf

and add following under [mysqld] section

log-bin=mysql-bin
binlog-do-db=radius
server-id=2
auto_increment_increment = 2
auto_increment_offset = 2

Make sure server-id is different then primary server

SAVE and EXIT.

Now restart mysqld service so changes can take effect.

service mysqld restart

Here we are going to create the user which will be responsible for the replication. Replace “password” with the password you wish to use.

create user 'zaib'@'%' identified by 'password';
grant replication slave on *.* to 'zaib'@'%'; 

The next step involves taking the information that we took a note of earlier and applying it to our mysql instance. This will allow replication to begin. The following should be typed at the mysql shell:

slave stop;

CHANGE MASTER TO MASTER_HOST = '101.11.11.241', MASTER_USER = 'zaib', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 336;

slave start; 

Your values for MASTER_LOG_FILE and MASTER_LOG_POS may differ than those above. You should copy the values that “SHOW MASTER STATUS” returns on Server-1.

 

The last thing we have to do before we complete the mysql master-master replication is to make note of the master log file and position to use to replicate in the other direction (from Server 2 to Server 1).

We can do that by typing the following:

SHOW MASTER STATUS; 

The output will look similar to the following:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      125 | radius       |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Take note of the file and position, as we will have to enter those on server 1, to complete the two-way replication.

The next step will explain how to do that.

 

Step – 3

Completing Replication on Server1 [Master-radius]

Back on Server 1, we need to finish configuring replication on the command line.

Running this command will replicate all data from Server 2.

slave stop;
CHANGE MASTER TO MASTER_HOST = '101.11.11.245', MASTER_USER = 'zaib', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000002', MASTER_LOG_POS = 125;
slave start; 

Keep in mind that your values may differ from those above. Please also replace the value of MASTER_PASSWORD with the password you created when setting up the replication user.

The output will look similar to the following:

Query OK, 0 rows affected (0.01 sec)

 

Now test the status by issuing command to mysql cli

show slave status\G

and you should see something similar to this. [don’t get confused with different numbers of log file file and position number, as this snap was taken in another lab]

replica-status


 

TEST

The last thing to do is to test that replication is working on both servers.

Open server1 radius panel, and try to create new user, after creation, it will be automatically replicated to server2 : )

As showed in the images below …

At a moment no users have been created.

server1-empty

 

Now create test user

server1-users-create

 

After creation, Goto Server2 (Replica) and check Users List, and you will find the user replicated.

server2-new0user0replicate-ok

and when you will create any user , it will replicate back to server1.


Adding both Radius Server entries in Mikrotik

Add both radius server

add-radius

and at radius manager, add the NAS (mikrotik)

add-nas

Don’t forget to rebuild clients.conf (from the menu) at secondary radius as well.

Now test by connecting any client , once successful, disconnect the primary radius, and try to connect the client again, once mikrotik will be unable to find primary entry, it will auto contact secondary server. as showed in the images below …

2radius

I will add few more details later….

 

Regard’s
Syed Jahanzaib

 

June 18, 2015

Freeradius/mysql Account Expiry SMS notification Script using ‘itelservices.net’ bulk SMS Gateway

Filed under: Linux Related, Radius Manager — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 1:09 PM

sms

This post is somewhat very specific to PK base bulk sms provider API. Its a simple bash script made on someone’s request [who had a custom billing system based on freeeradius/mysql] and it can be used to send account expiry notifications to users using freeradius/mysql account query  , BUT specifically using HTTP base SMS Gateway services from http://itelservices.net/

However this specific SMS gateway was a bit different as compared to our KANNEL base gw.

  1. It requires ‘Unique transaction ID’ for each sms, therefore i used current time/seconds with username as Transaction ID
  2. The number should be in international format like 923333021909 and the problem was that the operator had simple format for mobile numbers like 03333021909 is all accounts, and it was not acceptable from the API provider, therefore as a workaround, I used awk/sed tools to remove 0 and then in curl added 92 before every number.

At the moment there are two scripts

1- SMS for account expiry notification
2- SMS for new account creation with user details if possible

You must modify the script as required. This is just a simple way to achieve this task, however there are more sophisticated method like using php or other programing language, I just prefer to select the BASH route !

 

Posting it for   H U M A S   as I love them, They’re Amazing ! :)


1- SMS for account expiry notification

 

mkdir /temp
touch /temp/sms.sh
chmod +x /temp/sms.sh
nano /temp/sms.sh

Now paste the following code.

#!/bin/sh
# set -x
# BASH base SMS script for sending expiry notification for Freeradius/mysql users
# the simple logic can be applied for about any other task as well.
# I tried to make it as simple as it can be
# By Syed Jahanzaib
# Created on : 8th June, 2015
# Modified on : 18th june, 2015
# This script was specially modified for APITEL http sms gateway services
# which requires unique transaction ID each time, so i used datetimesecond feature as jugaar
# made for KHI

# MYSQL root id and password
SQLUSER="root"
SQLPASS="sqlpass"
DB="radiusdb"

# APITEL User Name & Password, must be filled
APIUSER="xxxx"
APIPASS="xxxx"
API="YOURSENDERNAME"

# Date functions to find current date, month year and Transaction id using seconds ; ) jugaar way ; )
NOW=$(date)
TID=$(date +"-%s")

# Interval before alert which should be sent to user before this number days
EXPIRY=3

# Export usernames and mobile from the mysql table in a file,  which Expiry is after 3 days
mysql -u$SQLUSER -p$SQLPASS -e "use $DB; SELECT login,mobile FROM users WHERE expirydate = DATE_ADD(CURDATE(), INTERVAL $EXPIRY DAY);"
mysql -u$SQLUSER -p$SQLPASS -e "use $DB; SELECT login,mobile FROM users WHERE expirydate = DATE_ADD(CURDATE(), INTERVAL $EXPIRY DAY);" > /tmp/list

# Remove 0 if any in mobile number and export it to final list
cat /tmp/list | awk '{gsub("^0","",$2); print $1,$2}' > /tmp/finallist

# Add DATE TIME in sms.log to separate date wise entries / zaib
echo ====================================================== >> /var/log/sms.log
echo $NOW >> /var/log/sms.log
echo ====================================================== >> /var/log/sms.log

# Add DATE TIME in smsapi.log to separate date wise entries WITH API STATUS for cross verification / zaib
echo ====================================================== >> /var/log/smsapi.log
echo $NOW >> /var/log/smsapi.log
echo ====================================================== >> /var/log/smsapi.log

# Apply Count Loop Formula while deleting first line which have simple text, and also any line which dont have mobile number [in second column]
num=0
cat /tmp/finallist |sed '1d' |awk 'NF > 1' | while read users
do
num=$[$num+1]
username=`echo $users |awk '{print $1}'`
mobile=`echo $users | awk '{print $2}'`

# SMS Body
BODY="Soft+Reminder:+Dear+$username,+Your+Internet+Service++Will+Expire+after+$EXPIRY+days++++zaibisp"

echo "$NOW ! Expiry Notification have been sent to $username, on cell number 0$mobile"
echo "$NOW ! Expiry Notification have been sent to $username, on cell number 0$mobile" >> /var/log/sms.log

# Add action like send sms or email as per required or designed / zaib
# Sending sms via APITEL API SMS Gatewy / syed jahanzaib / aacable@hotmail.com

curl "http://api1.itelservices.net/send.php?transaction_id=$TID$username&user=$APIUSER&pass=$APIPASS?&number=%2B92$mobile&text=$BODY&from=$API" >> /tmp/smsapi.log
done

sed 's/\(Status\)/\n\1/g' /tmp/smsapi.log >> /var/log/smsapi.log
echo ======================================================
echo Result for SMSAPI , so that you can verify that how much sms are actually sent with the status codes
cat  /var/log/smsapi.log



 

CRON JOB TO RUN IT DAILY IN NIGHT

Now set cron job to run it daily in night

@daily /temp/sms.sh


 LOGS

you can view log files in following location
/var/log/sms.log

Sample:

Thu Jun 18 11:43:20 PKT 2015 ! Expiry Notification have been sent to USER1, on cell number 033333333333
Thu Jun 18 11:43:20 PKT 2015 ! Expiry Notification have been sent to USER2, on cell number 0333132121211

/var/log/smsapi.log

Results with status from api gateway services (Useful to track the messages are actually sent or having errors from provider like server down, credit finished etc etc)

Sample:

Status: 013, Id: -1434609800USER1, Number: +923452266605
Status: 013, Id: -1434609800USER2, Number: +923222656143


2- SMS for NEW Account Creation

1

mkdir /temp
touch /temp/sms-new-account.sh
chmod +x /temp/sms-new-account.sh
nano /temp/sms-new-account.sh

#!/bin/sh
# set -x
# BASH base SMS script for NEW ACCOUNTnotification for Freeradius/mysql users
# the simple logic can be applied for about any other task as well.
# I tried to make it as simple as it can be
# By Syed Jahanzaib
# CREATED on : 19th june, 2015
# This script was specially modified for APITEL http sms gateway services
# which requires unique transaction ID each time, so i used datetimesecond feature as jugaar
# made for KHI/PK

# MYSQL root id and password
SQLUSER="root"
SQLPASS="pass"
DB="radius-db"

# APITEL User Name & Password
APIUSER="APIUSER"
APIPASS="APIPASS"
API="SENDERID"

# Date functions to find current date, month year and Transaction id using seconds ; ) jugaar way ; )
NOW=$(date)
TID=$(date +"-%s")

# Check Account which are created before this number of MINUTES
CREATION=5

touch /tmp/sms-new-account.log
touch /tmp/sms-new-account-api.log
> /tmp/sms-new-account.log
> /tmp/sms-new-account-api.log

# Export usernames and mobile from the mysql table in a file,  which Expiry is after 3 days
USRVALID=`mysql -u$SQLUSER -p$SQLPASS -e "use $DB; select creationdate,login,package,expirydate,mobile from users WHERE creationdate >= NOW() - INTERVAL $CREATION MINUTE;"`
mysql -u$SQLUSER -p$SQLPASS -e "use $DB; select creationdate,login,package,expirydate,mobile from users WHERE creationdate >= NOW() - INTERVAL $CREATION MINUTE;" > /tmp/newact

# Check User Validation, if not found exit with error , else continue
echo
if [ "$USRVALID" == "" ]; then
echo -e "No new user created in last minutes, so nothign to do , zaib !"
else
echo -E "user Created found , proceeding..."

# Remove 0 if any in mobile number and export it to final list
cat /tmp/newact | awk '{gsub("^0","",$7); print $1,$2,$3,$4,$5,$6,$7}' > /tmp/newactfinal

# Add DATE to separate entries in sms-new-account.log
echo ================================ >> /var/log/sms-new-account.log
echo $NOW >> /var/log/sms-new-account.log
echo ================================ >> /var/log/sms-new-account.log

echo ================================ >> /var/log/sms-new-account-api.log
echo $NOW >> /var/log/sms-new-account-api.log
echo ================================ >> /var/log/sms-new-account-api.log

# Apply Count Loop Formula while deleting first line which have simple text, and also any line which dont have mobile number [in second column]
# Apply Count Loop Formula while deleting first line which have simple text, and also any line which dont have mobile number [in second column]
num=0
cat /tmp/newactfinal |sed '1d' |awk 'NF > 6' | while read users
do
num=$[$num+1]
username=`echo $users |awk '{print $3}'`
mobile=`echo $users | awk '{print $7}'`
pkg=`echo $users | awk '{print $4}'`
exp=`echo $users | awk '{print $5}'`
#echo "Welcome to MYNET Broadband Services! Your account details are as follow...
#Username = $username
#Package = $pkg
#Expiry = $exp
#Cell No = $mobile"

# SMS Body
BODY="Welcome+to+MYISP+Services,+Your+account+details+are:++id=$username+/+Package=+$pkg+/+Expiry=+$exp+/+Cell=+0$mobile++++MYISP+BROADBAND"

echo "$NOW ! New Acount Creation Notification have been sent to $username, on cell number 0$mobile"
echo "$NOW ! New Acount Creation Notification have been sent to $username, on cell number 0$mobile" >> /var/log/sms-new-account.log

# Add action like send sms or email as per required or designed / zaib
# Sending sms via APITEL API SMS Gatewy / syed jahanzaib / aacable@hotmail.com

curl "http://api1.itelservices.net/send.php?transaction_id=$TID$username&user=$APIUSER&pass=$APIPASS?&number=%2B92$mobile&text=$BODY&from=$API" >> /tmp/sms-new-account-api.log
sed 's/\(Status\)/\n\1/g' /tmp/sms-new-account-api.log >> /var/log/sms-new-account-api.log
echo
echo Result for SMSAPI , so that you can verify that how much sms are actually sent with the status codes
#cat  /var/log/sms-new-account.log
done

fi

Cron it to run after every 5 minutes

*/5 * * * * /temp/sms-new-account.sh


 3- SMS for ALL users (I deployed it for Webmin usage)


#!/bin/bash
# set -x
# Script to send GENERAL SMS via WEBMIn
# Syed Jahanzaib
# aacable @ hotmail.com
# https://aacable.wordpress.com
# Created on 24th June, 2015

SQLUSER="root"
SQLPASS="mysqlpassword"
DB="radiusdb"

# APITEL User Name & Password
APIUSER="xxxx"
APIPASS="xxxxx"
API="xxxx"

######################
# ACCOUNT EXPIRY CHECK
######################

# Date functions to find current date, month year and Transaction id using seconds ; ) jugaar way ; )
NOW=$(date)
TID=$(date +"-%s")

# Adding files
touch /tmp/smspanel.log
touch /tmp/smapanel-api.log
> /tmp/smspanel.log
> /tmp/smapanel-api.log

mysql -uroot -pgatewayb3 -e "use mynet; SELECT login,mobile FROM users;"  > /tmp/smspanellist

# Remove 0 if any in mobile number and export it to final list
cat /tmp/smspanellist | awk '{gsub("^0","",$2); print $1,$2}' > /tmp/smspanellistfinal

# Add DATE TIME in /tmp/smspanel.log to separate date wise entries / zaib
echo ====================================================== >> /var/log/smspanel.log
echo $NOW >> /var/log/smspanel.log
echo ====================================================== >> /var/log/smspanel.log

# Add DATE TIME in /tmp/smspanel-api.log to separate date wise entries WITH API STATUS for cross verification / zaib
echo ====================================================== >> /var/log/smspanel-api.log
echo $NOW >> /var/log/smspanel-api.log
echo ====================================================== >> /var/log/smspanel-api.log

# Apply Count Loop Formula while deleting first line which have simple text, and also any line which dont have mobile number [in second column]
num=0
# remove first line which have simple text, then remove dash in second column which is mobile numbers
cat /tmp/smspanellistfinal |sed '1d' |awk 'NF > 1' | awk '{gsub("-","",$2)}1' | while read users
do
num=$[$num+1]
username=`echo $users |awk '{print $1}'`
mobile=`echo $users | awk '{print $2}'`

# SMS Body in local file and remove new lines and replace spaces with plus sign for api acceptance
BODY=`cat /tmp/smspanelmsg.txt  |tr '\r\n' ' ' | sed -e "s/\s\{1,\}/+/g"`

#echo "$NOW ! $BODY ---- MSG was sent to $username, on cell number 0$mobile"
echo "$NOW ! Your MSG was sent to $username, on cell number 0$mobile" >> /var/log/smspanel.log

# Sending sms via APITEL API SMS Gatewy / syed jahanzaib / aacable@hotmail.com

curl "http://api1.itelservices.net/send.php?transaction_id=$TID$username&user=$APIUSER&pass=$APIPASS?&number=%2B92$mobile&from=$API&text=$BODY" >> /tmp/smspanel-api.log
sed 's/\(Status\)/\n\1/g' /tmp/smspanel-api.log >> /var/log/smspanel-api.log
done

 

ITELSERVICES.NET related information

Sample of URL to send SMS

http://api1.itelservices.net/send.php?transaction_id=message1&user=bilal&pass=bilal2015?&number=%2B923333021909&text=hello&from=MyNet

Please note that the transaction id must be unique for each sms, example message1, message2 and so on any word is acceptable, i used date time as transaction id, you may use your own.

 

INFORMATION AND ERROR CODES related to API

For the information/error codes

 

1

 

2

 

3


 

Regard’s
Syed Jahanzaib

June 15, 2015

Sending Email on Router Reboot with Logs and LASTSEEN time !

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

email


 

Task:

To send email when the router reboots because of any power or other issue, the email should include the time of router failure and its restore time in the email subject, [for quick viewing] . Also it should include last few logs entries in the email body for diagnostic purposes.

Disclaimer: Main script was taken from Mikrotik WIKI
http://wiki.mikrotik.com/wiki/Send_email_about_reboot , and later I modified it to meet local requirement. Also I created second script which keep updates of time in a file to track the time when router got lost,

Following are two scripts which does the following

Script#1 – Update-last-seen-time-on-each-minute
This script will update date+time in a local file called lastseen.txt so that even if a router reboot, the time preserves in this file to print the lastseen time in email or where ever required.

Script#2 – email-on-reboot
This script will run after router reboot/restarts. It will first disable the script-1 schedule to prevent overwritten of lastseen time in the file lastseen.txt, then it will fetch the data from this file and will also get current time and send email with last 60 Seconds logs in the email body.

Script#2 is taken from WIKI and modified it as per my requirement. (to display Last seen time as well in the email so that I don’t have to go through whole logs in search of —–)

Exported Version of both scripts. for copy/pasting, donot go blindly, read them before deploying


####################################
#1st Script: update-last-seen-time-on-each-minute
####################################

/system script
add name=email-on-reboot policy=ftp,reboot,read,write,policy,test,winbox,password,sniff,sensitive,api source="# Disable LASTSEEN script schduler to prevent overwritten of time\"\r\
\n:log error \"Disabling Lastseen Update Time Scheudle to preserve Last REBOOT time\"\r\
\n/sys scheduler disable update-last-seen-time-on-each-minute\r\
\n\r\
\n:delay 30s\r\
\n:global gmailsmtp\r\
\n:set gmailsmtp [:resolve \"smtp.gmail.com\"];\r\
\n\r\
\n# Set your GMAIL Account Password\r\
\n:local gmailpass\r\
\n:set gmailpass GMASILPASS\r\
\n\r\
\n# Set your email where you want to receieve the alert \r\
\n:local mailsendto\r\
\n:set mailsendto aacable@hotmail.com\r\
\n\r\
\n# Wait for NTP Time Synchronization to pritn current timestamp in mail subject, NTP is required in RB's \r\
\n:while ( [/system ntp client get status]!=\"synchronized\" ) do={ :delay 1s }\r\
\n\r\
\n# Set Email Subject\r\
\n:local es \"\$[/system identity get name] got LOST at \$[/file get lastseen.txt contents] & back online at \$[/system clock get date] \$[/system clock get time]\"\r\
\n\r\
\n# Load Last LOGS Entries of 60 seconds\r\
\n:delay 60s\r\
\n:local eb \"Mikrotik Log contents (with 60 seconds delay):\\r\\n\"\r\
\n:foreach le in=[/log print as-value] do={\r\
\n  :set eb (\$eb.[:tostr [(\$le->\"time\")]].\" \".[:tostr [(\$le->\"topics\")]].\": \".[:tostr [(\$le->\"message\")]].\"\\r\\n\")\r\
\n}\r\
\n\r\
\n# Finally send email \r\
\n/tool e-mail send to=\$mailsendto subject=\$es body=\$eb server=\$gmailsmtp tls=yes password=\$gmailpass\r\
\ndelay 5s\r\
\n\r\
\n# Enable back the update time schdule for lastseen value \r\
\n:log warning \"Email Done! Enabling Lastseen Update Time Scheudle ...\"\r\
\n/sys scheduler enable update-last-seen-time-on-each-minute\r\
\n\r\
\n# Syed Jahanzaib\r\
\n# aacable [at] hotmail.com"

####################################
#2nd Script: update-last-seen-time-on-each-minute
####################################

add name=update-last-seen-time-on-each-minute policy=ftp,reboot,read,write,policy,test,winbox,password,sniff,sensitive,api source=":local date;\r\
\n:local time;\r\
\n:set date [/system clock get date];\r\
\n:set time [/system clock get time];\r\
\n/file print file=lastseen.txt where name=lastseen.txt;\r\
\n/file set lastseen.txt contents=\"\$date \$time\"\r\
\n#:local lastdt ([/file get lastseen.txt contents])\r\
\n#:log warning \"Updaintg Last Seen > \$lastdt\"\r\
\n}"

Print View of Both Scripts


/sys scr pr

0   name="email-on-reboot" owner="admin" policy=ftp,reboot,read,write,policy,test,winbox,password,sniff,sensitive,api last-started=jun/15/2015 13:17:47 run-count=39
source=
# Disable LASTSEEN script schduler to prevent overwritten of time"
:log error "Disabling Lastseen Update Time Scheudle to preserve Last REBOOT time"
/sys scheduler disable update-last-seen-time-on-each-minute

:delay 30s
:global gmailsmtp
:set gmailsmtp [:resolve "smtp.gmail.com"];

# Set your GMAIL Account Password
:local gmailpass
:set gmailpass GMAILPASS

# Set your email where you want to receive the alert
:local mailsendto
:set mailsendto aacable@hotmail.com

# Wait for NTP Time Synchronization to pritn current timestamp in mail subject, NTP is required in RB's
:while ( [/system ntp client get status]!="synchronized" ) do={ :delay 1s }

# Set Email Subject
:local es "$[/system identity get name] got LOST at $[/file get lastseen.txt contents] & back online at $[/system clock get date] $[/system clock get time]"

# Load Last LOGS Entries of 60 seconds
:delay 60s
:local eb "Mikrotik Log contents (with 60 seconds delay):\r\n"
:foreach le in=[/log print as-value] do={
:set eb ($eb.[:tostr [($le->"time")]]." ".[:tostr [($le->"topics")]].": ".[:tostr [($le->"message")]]."\r\n")
}

# Finally send email
/tool e-mail send to=$mailsendto subject=$es body=$eb server=$gmailsmtp tls=yes password=$gmailpass
delay 5s

# Enable back the update time schdule for lastseen value
:log warning "Email Done! Enabling Lastseen Update Time Scheudle ..."
/sys scheduler enable update-last-seen-time-on-each-minute

# Syed Jahanzaib
# aacable [at] hotmail.com

#########################
##### Second Script View
########################
1   name="update-last-seen-time-on-each-minute" owner="admin" policy=ftp,reboot,read,write,policy,test,winbox,password,sniff,sensitive,api last-started=jun/15/2015 13:49:18 run-count=307
source=
:local date;
:local time;
:set date [/system clock get date];
:set time [/system clock get time];
/file print file=lastseen.txt where name=lastseen.txt;
/file set lastseen.txt contents="$date $time"
#:local lastdt ([/file get lastseen.txt contents])
#:log warning "Updaintg Last Seen > $lastdt"
}

 

Schedule both scripts

/system scheduler
# First script which will run once only upon system restart
add disabled=no interval=0s name=email-on-reboot on-event=email-on-reboot policy=ftp,reboot,read,write,policy,test,winbox,password,sniff,sensitive,api start-time=startup

# Second script which will run after each minute to update the date/time in local file
add disabled=no interval=1m name=update-last-seen-time-on-each-minute on-event=update-last-seen-time-on-each-minute policy=ftp,reboot,read,write,policy,test,winbox,password,sniff,sensitive,api \
start-date=jun/15/2015 start-time=05:40:08

Configure Email Example

Also don’t forget to set email tools. like

/tool e-mail
set address=173.194.69.109 from=YOURGMAILID@gmail.com password=YOURGMAILPASS port=587 starttls=no user=YOURGMAILID

 


 

SCRIPTS iN ACTiON

Now power off the router , and after few minutes power it on.

After a while, you should see the scripts in action.
As showed in the image below …

email-mikrotik-reboot-emial

 

email

.

Regard’s
Syed Jahanzaib

June 8, 2015

Send Expiry Alert via SMS/Email For Freeradius/Mysql Users

Filed under: Linux Related, Radius Manager — Tags: , , , , — Syed Jahanzaib / Pinochio~:) @ 5:00 PM

 

 

sms-alert

As some one asked me on howto send sms (or possibly email) to users whose expiry is after XX days in freeradius/mysql base billing system, Here is a simple script to do the task. It’s not very elegant way to achieve the task but since I donot have any programming level experience so this is how achieve it some Desi style coding :) & the good part is , It’s doing the job and you can at least get some ideas from the code.

So basically this post is just another Sharing Idea’s Series


 

Requirements:

  • You must have working billing system in freeradius/mysql with the appropriate tables like radius, username, expiration etc.

 

In this example I used Radius Manager base system which also uses FREERADIUS/MYSQL as its backend DB.Radius Manager already have expiry alerts notification in its core configurable via web panel, but its a 3rd party paid application. So I am showing you a way howto achieve the same with your own billing system.

So basically what I did was to simply ran mysql query which pulled user name and mobile number from the table [mobile number column must be be created with appropriate values] and exported it to local file. Then I applied a simple ‘Loop‘ formula to go through this file and then applied appropriate action in the end like send SMS via mobile / usb modem attached , use any external http Gateway , or send EMAIL.

You can use this logic to achieve the results on about any other billing system (which is open source or readable) OR any other purposes as well.

Just Go through this script ,its very simple, modify it as per your network and setup. If you manage to add some enhancements, do post here for the sake of every one. :~)

I will add some more details later.

Happy Alerting !

Syed Jahanzaib


Create SMS Script

mkdir /temp
touch /temp/sms.sh
chmod +x /temp/sms.sh
nano /temp/sms.sh

Now paste the following script

#!/bin/sh
# BASH base SMS script for sending expiry notification for Freeradius/mysql users
# the simple logic can be applied for about any other task as well.
# I tried to make it as simple as it can be
# By Syed Jahanzaib
# Created on : 8th June, 2015

SQLUSER="root"
SQLPASS="sqlpassword"
# Interval before alert which should be sent to user before this number days
EXPIRY="3"

# Export usernames and mobile from the mysql table in a file,  which Expiry is after 3 days
mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT username,mobile FROM radius.rm_users  WHERE expiration = DATE_ADD(CURDATE(), INTERVAL $EXPIRY DAY);" > /tmp/list

# Apply Count Loop Formula while deleting first line which have simple text, and also any line which dont have mobile number [in second column]
num=0
cat /tmp/list |sed '1d' |awk 'NF > 1' | while read users
do
num=$[$num+1]
username=`echo $users |awk '{print $1}'`
mobile=`echo $users | awk '{print $2}'`

# Add action like send sms or email as per required or designed / zaib
# Here I am just echoing , You must change this if you want some action liek sms or mail as showed in the end
echo "Dear $username, Your account will expire after 3 days. Your cell is $mobile"

# GAMMU SENDMS Example
# gammu sendsms TEXT $mobile -text "Dear $username, Your account will expire after 3 days / ABC ISP"

# KANNEL SMS HTTP GATEWAY Example, 192.168.1.1 is kannel server ip
# curl "http://192.168.1.1:13013/cgi-bin/sendsms?username=kannel&password=KANNELPASS&to=$mobile&text=Dear+$username+Your+account+will+expire+after+3+days++ABC+ISP

# Email Example using -mail- tool
# mail -s 'Dear $username, Your account will expire after 3 days / ABC ISP' $email

done

 

OUTPUT:

[Just echoing in this example]

Run the script manually for test purposes and you should then be able to see something like if you already have proper billing configured with enough data. Below example is a working radius system showing accounts with mobile numbers which will expire in next 3 days. We can show more info if required.

 

sms-alert-list


 

Schedule to run it DAILY

You can schedule it to run on daily basis so it can check for accounts expiring on next xx days and take appropriate action as required.

Example of scheduled job bycrontabcommand:

crontab -l

@daily /temp/sms.sh

With above code, this script will run daily at 00:00 hours [in night] daily. Then it will search for accounts whose account will expire after 3 days, then it will take defined action.

Jz!

 

June 5, 2015

Gnuplot = The DADA ABBU (Grandfather) of Graphing done via CLI

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

1-data-downloaded-in-year


 

Whatis Gnuplot:

As defined the Wikipedia. …

Gnuplot is a command-line program that can generate two- and three-dimensional plots of functions, data, and data fits. It is frequently used for publication-quality graphics as well as education. The program runs on all major computers and operating systems (GNU/Linux, Unix, Microsoft Windows, Mac OS X, and others).

I remember when I got in love with the MRTG and I spent many nights in mastering this giant. MRTG is overall a very good graphing too graph about any device but it usually works with snmp (and in some cases with shell scripts too). But what if I have data in a file with simple human readable format and I want to plot different columns in it? MRTG will not help in such cases, Gnuplot will come to rescue :)

I used Gnuplot to graph user download for the current month, In this example user data is taken from MYSQL radius DB and then graphed/plotted with Gnuplot.

As always being a duffer , dumber and incompetent, It took me 2-3 Days of continuous efforts to make it as a single script to make it bundled package.

Requirements for this script:

[You can modify it as per your requirements very easily, I just made it as per my own requirements : D ]

  1. Linux / Ubuntu
  2. Mysql with Radius DB
  3. Gnuplot

What this script will do ?

This script will take accounting data for the specified users for the current month by auto detecting the month/year.The file will look something like following

2015-03-01   1688961371   937706875
2015-03-02   2989190965   2974464964
2015-03-04   534479492   31747041
2015-03-05   809968366   170112567
2015-03-06   2189812711   1555484772

First column is DATE
Second column is user DOWNLOADED data in bytes
Third column is user UPLOADED data in bytes
Then it will save this accounting data in /tmp/USERNAME.TXT  (Username is what supplied by the user)
Then gnuplot will start its magic and will graph the data based on the supplied data.


 

To install Gnuplot on Ubuntu , issue following command

apt-get install -y gnuplot

Now create bash script as follows

mkdir /temp
touch /temp/usergraph.sh
nano /temp/usergraph.sh

and paste following. Make sure to change things according to your network

#!/bin/sh
# Freeradius / Mysql user graph ON THE FLY using GNUPLOT
# It will also detect current year and current month and will pull only current time data
# You can modify this function by providing $2 function in the sql command
# By Syed Jahanzaib / aacable [at] hotmail.com
# Last modified on 5th June, 2015

# Defining BASH Variables
SQLUSER="root"
SQLPASS="sqlpassword"
SQLHOST="localhost"

# Date functions to find current date, month year
NOW=$(date)
MONTH=$(date +"-%m")
CMONTH=`echo $MONTH  | sed -e 's/-//g'`
YEAR=$(date +"-%Y")
CYEAR=`echo $YEAR  | sed -e 's/-//g'`
FMONTH=$(date +"%B")
FULLMONTH=`echo $FMONTH # | sed -e 's/-//g'`

# Name of file in which mysql will dump the user accounting data for the current month
TMP="/tmp/$1.txt"

# Fetch Accounting Data from MYSQL Freeradius radius DB, by using current Year/Month using username provide with the script , and output to file
mysql -u$SQLUSER -p$SQLPASS -h$SQLHOST -e "use radius; SELECT SQL_CALC_FOUND_ROWS date, SUM(allbytesdl) - COALESCE(SUM(specbytesdl), 0), SUM(allbytesul) - COALESCE(SUM(specbytesul), 0), SUM(alltime) - COALESCE(SUM(spectime), 0)
FROM (  SELECT LEFT(radacct.acctstarttime, 10) AS date,  acctoutputoctets AS allbytesdl, SUM(dlbytes) AS specbytesdl,  acctinputoctets AS allbytesul, SUM(ulbytes) AS specbytesul,
radacct.acctsessiontime AS alltime, SUM(rm_radacct.acctsessiontime) AS spectime  FROM radacct  LEFT JOIN rm_radacct ON rm_radacct.radacctid = radacct.radacctid
WHERE LEFT(radacct.acctstarttime, 7) LIKE '$CYEAR-$CMONTH%' AND radacct.username LIKE '$1' AND  FramedIPAddress LIKE '%' AND CallingStationId LIKE '%'   GROUP BY radacct.radacctid
) AS tmp GROUP BY date LIMIT 0, 50;" |awk '{print $1,$2,$3}' > $TMP
sed '1d' -i $TMP

# Run GNUPLOT SCRIPT on the FLY / by zaib
gnuplot << EOF
reset
set terminal jpeg size 1600,600
# Set output according to your requirement, like you can create file with the username for easier identification
set output "/var/www/radius.jpg"
set xdata time
set timefmt "%Y-%m-%d"
set format x "%d/%m"
set xtics 86400
set xtics rotate by -45
set xlabel "Date (day/month)"
set ylabel "Data Downloaded in GB"
set title "$1 - Download/Upload Report $FULLMONTH $YEAR\nThis report was created on $NOW\nPowered by Syed Jahanzaib / aacable@hotmail.com"
set key outside
set grid
set style data histogram
set style histogram cluster gap 1
set style fill solid
set boxwidth 0.9

plot "$TMP" using 1:(\$2/2**30):(sprintf("%.2f", \$2/2**30)) w boxes title "Download" lw 10, \
"$TMP" using 1:(\$3/2**30):(sprintf("%.2f", \$3/2**30)) w boxes lw 6 title "Upload", \
"$TMP" using 1:(\$2/2**30):(sprintf("%.2f", \$2/2**30)) w labels notitle tc rgb 'red', \
"$TMP" using 1:(\$3/2**30):(sprintf("%.2f", \$3/2**30)) w labels notitle tc rgb 'green'

EOF
# GNUPLOT Script ends here
# Thank you : )

 

Running the SCRIPT

Now execute the script by

/temp/usergraph.sh USERNAME

(like usergraph.sh zaib)

If everything goes well and you dont’ see any errors after executing this script, then you can view the output by

http://yourip/radius.jpg

gnuplot


That’s it …

I showed the very basic usage of Gnuplot. Very Very Basic Level of it. This is only what I have learned so far. But Gnuplot can do things beyond your imagination. Look at this gallery.

http://commons.wikimedia.org/wiki/Category:Gnuplot_diagrams

Gnuplot is a very good and customizable tool which is used all over the world to create simple OR very complex graphs in a go. Above all good part is that it can take data from local files and all can be done via scripting or terminal.

You should give it a try :)


Another version which takes year from your input and then create graph for the whole year usage for the network (overall)

This is another version which input year from you and then create graph for the whole year for overall network usage,


root@radius:/temp# cat year.sh
#!/bin/sh
# MYSQL USER NAME AND PASSOWRD Variables
SQLUSER="root"
SQLPASS="SQLPASS"

# Date functions to find current date, month year
NOW=$(date)
MONTH=$(date +"-%m")
CMONTH=`echo $MONTH  | sed -e 's/-//g'`
YEAR=$(date +"-%Y")
CYEAR=`echo $YEAR  | sed -e 's/-//g'`
FMONTH=$(date +"%B")
FULLMONTH=`echo $FMONTH # | sed -e 's/-//g'`

mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT SQL_CALC_FOUND_ROWS
date,
SUM(allbytesdl) - COALESCE(SUM(specbytesdl), 0),
SUM(allbytesul) - COALESCE(SUM(specbytesul), 0),
SUM(alltime) - COALESCE(SUM(spectime), 0)
FROM (
SELECT LEFT(radacct.acctstarttime, 7) AS date,
acctoutputoctets AS allbytesdl, SUM(dlbytes) AS specbytesdl,
acctinputoctets AS allbytesul, SUM(ulbytes) AS specbytesul,
radacct.acctsessiontime AS alltime, SUM(rm_radacct.acctsessiontime) AS spectime
FROM radacct
LEFT JOIN rm_radacct ON rm_radacct.radacctid = radacct.radacctid
WHERE LEFT(radacct.acctstarttime, 4) LIKE '$1%' AND radacct.username LIKE '%' AND
FramedIPAddress LIKE '%' AND CallingStationId LIKE '%'
GROUP BY radacct.radacctid
) AS tmp
GROUP BY date
LIMIT 0, 50;"  |awk '{print $1,$2,$3}' >  /tmp/raw

sed '1d' -i /tmp/raw
awk '{ print $1, $2 + $3; }' /tmp/raw > /tmp/final
echo DONE
# Name of file in which mysql will dump the user accounting data for the current month
TMP="/tmp/final"

# Run GNUPLOT SCRIPT on the FLY / by zaib
gnuplot << EOF
reset
set terminal jpeg size 1600,600
# Set output according to your requirement, like you can create file with the username for easier identification
set output "/var/www/radius.jpg"
set xdata time
set timefmt "%Y-%m"
set format x "%Y/%m"
#set ytics 1
set xtics rotate by -45
set xlabel "Date (month/year)"
set ylabel "Data Downloaded in GB"
set title "Download/Upload Report for $1\nThis report was created on $NOW\nPowered by Syed Jahanzaib / aacable@hotmail.com"
set key outside
set grid
set style data histogram
set style histogram cluster gap 1
set style fill solid
set boxwidth 0.9

plot "$TMP" using 1:(\$2/2**30):(sprintf("%.0f", \$2/2**30)) w boxes title "Download" lw 10, \
"$TMP" using 1:(\$2/2**30):(sprintf("%.0f", \$2/2**30)) w labels title "Data in GB" center offset 0,1 tc rgb 'red'

EOF
# GNUPLOT Script ends here
# Thank you : )

Now execute script as follows

./year.sh 2015

you ahve to supply year o it will generate overall graph which will look odd as we are graphing details for 1 year only,

Sample of above script will generate graph as follows

1-data-downloaded-in-year

Regard’s
Syed Jahanzaib

June 2, 2015

Mikrotik with Cisco VLAN made easy

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

kick1


vlan
~!~ Mikrotik with Cisco VLAN made easy ~!~
 ~!~ For Beginners ~!~

Virtual LANs (VLANs) are a solution to allow you to separate users into individual network segments for security and other reasons. VLAN membership can be configured through software instead of physically relocating devices or connections. VLANs allow you to break up devices on your network regardless of their location.

The main advantage of VLAN are

  • Broadcast Control
  • Security / Client Isolation
  • DMZ
  • Controlled Network Management
  • Costing

It is very useful for any network including large/small offices, ISP’s, Cable.Internet services providers etc. The main problem of any large network is broadcast and specially for network operators when any single user swap his wan router LAN cable to wan cable thus broadcast his router DHCP to operator network, or single user effected with virus/trojans broadcast to whole network. VLAN can help you in many situations like these or others.


 

TASK:

To avoid broadcasting/flooding and above all for better better management +security and monitoring, we want to break the network in smaller segments.

Scenario:

Consider the following scenario

We have Mikrotik Router which is acting as a DHCP and PPPoE Server as well. and we want to isolate the different network areas by breaking them in smaller segments. Each area will get different IP series from the mikrotik dhcp server.

In this example following ports are used for

  • Mikrotik = Port 1 [as TRUNK port]
  • Dealer-1 = Port 2
  • Dealer-2 = Port 3
  • Dealer-3 = Port 4

Hardware Used in this Guide:

  1. Mikrotik RB2011
  2. Cisco 3750-E Series
  3. Two Laptops for testing

As showed in the image below …

2015-06-02 15.16.32


 

MIKROTIK CONFIG

# Create VLAN Interfaces and provide them name and VLAN#ID
/interface vlan
add interface=LAN l2mtu=1594 name=DEALER-1 vlan-id=10
add interface=LAN l2mtu=1594 name=DEALER-2 vlan-id=20
add interface=LAN l2mtu=1594 name=DEALER-3 vlan-id=30

# Assign IP addresses to the interfaces
/ip address
add address=192.168.1.1/24 interface=LAN network=192.168.1.0
add address=192.168.10.1/24 interface=DEALER-1 network=192.168.10.0
add address=192.168.20.1/24 interface=DEALER-2 network=192.168.20.0
add address=192.168.30.1/24 interface=DEALER-3 network=192.168.30.0

# Create DHCP Server and assign different Pools for the dealers
# You can DHCP wizard as well if CLI is a bit hectic
/ip dhcp-server
add address-pool=DEALER-1-POOL disabled=no interface=DEALER-1 lease-time=6h name=dhcp1
add address-pool=DEALER-2-POOL disabled=no interface=DEALER-2 lease-time=6h name=dhcp2
add address-pool=DEALER-3-POOL disabled=no interface=DEALER-3 lease-time=6h name=dhcp3

/ip dhcp-server network
add address=192.168.10.0/24 dns-server=192.168.10.1 gateway=192.168.10.1
add address=192.168.20.0/24 dns-server=192.168.20.1 gateway=192.168.20.1
add address=192.168.30.0/24 dns-server=192.168.30.1 gateway=192.168.30.1

Some screenshots for the reference purpose …

mt

 

Done. Now we have to create VLANs at CISCO Switch…



CISCO VLAN CONFIGURATION

I assume that you have Cisco switch with any IP address for the management purposes.

Telnet to the switch

telnet 192.168.0.1

# Enter your Cisco switch password
User Access Verification
Password: xxxxxx

# Switch to change mode
enable
Password: xxxxxxxxx

# Enter in Config mode
config t

# Select Port number which will be connected with the Mikrotik and change encapsulation method

interface gigabitEthernet 1/0/1
switchport trunk encapsulation dot1q
switchport mode trunk

switchport trunk allowed vlan all
switchport nonegotiate

#Create VLAN id and name for dealers/areas
vlan 10
name DEALER-1
vlan 20
name DEALER-2
vlan 30
name DEALER-3
exit

# Now Select Dealer1 interface , like port 2 and assign it with the vlan id

interface gigabitEthernet 1/0/2
# OR RANGE like interface range gigabitEthernet 1/0/1-4 (Port Range 1 to 4)
switchport mode access
switchport access vlan 10

interface gigabitEthernet 1/0/3
switchport mode access
switchport access vlan 20

interface gigabitEthernet 1/0/4
switchport mode access
switchport access vlan 30

exit
exit

# SAVE the configuration you just made above
wr

Some screenshots for reference…

1

 

3-vlan ports

 

2-VLAN

Done!

 


TEST!

Now connect your PC with the port 2 and port3 , 4

You will get different IP in each port.
As showed in the image below from mikrotik dhcp server’s

dhcp-dealers

 

Test From Dealer-1 System

dealer-1

Dealer-1 have receive 192.168.10.x series ip, exactly the one we configured in mikrotik.

Now try to ping any dealer-2 series and to the internet as well. You will see that you will be able to ping the internet and mikrotik LAN ip, but not with dealer-2 subnet or likewise.

ping result

NOTE: For the client isolation / to block communication between all VLAN’s , you must create FILTER rule as explained in TIP’s n TRICK section below …


TIP’S n TRICKS

 

1- Block communication between all or specific VLAN Subnet

It is usually required to block all communication between specific or all VLAN subnets for security or other reasons. By default mikrotik will allow communications between all vlan. You can block them by creating FILTER rules. Example is we want that users of all vlans can access internet via WAN interface but should not be able to communicate with any other VLAN subnet. use following as an example

Note: there are many other ways to achieve this either at switch level or mikrotik, i am showing just an example only here.


/ip firewall filter
add chain=forward comment="Accept traffic from VLAN subnets to WAN" out-interface=WAN

add action=reject chain=forward comment="Block Communication between all vlan subnets" reject-with=icmp-net-prohibited src-address=\
192.168.0.1-192.168.255.255

# Masquerade rule to allow internet , wan link interface
/ip firewall nat
add action=masquerade chain=srcnat out-interface=WAN


 

Snapshots of Working VLAN config with pppoe server

live-vlan-pppoe-server-config

 


 

Pending work:

– Add pppoe or hotspot configuration
– Firewall configuration for isolation in pppoe/hotpost
– Few more tips n tricks with VLAN

 

Regard’s
Syed Jahanzaib

May 28, 2015

Modem Status and SIM Balance check with Mail Alert

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

dlink


 

Task:

Requirement of daily reporting for the Modem status and SIM balance via email.
In this example I had DLINK DWM-156 USB Modem attached with Linux base system [Ubuntu 12.4].
This system had kannel/playsms installed which sends sms alerts for various events and can trigger different scripts upon receiving SMS with specific codes or instructions. I found that this modem was cheap and had many issues like not responding or likewsise, so I created this script which runs few times a day and sends email alerts for any issue related to modem and sim balance. on internet there might be some better guides or methods to do the same task, but this one suites my custom need. at a moment its on a very basic level, later we will modify it if required further :)
Sharing this script, maybe some one will get benefit from it or improve it on a pro level.


 

Requirement:

GAMMU must be installed and must be configured with the modem. you should run gammu –identify to make sure its detecting  the modem.

 


 

Bash Script to do the job:

######################
cat /temp/checkbal.sh
######################

#!/bin/bash
# Script to check Modem status and SIM balance via GAMMU and send email alerts accordingly.
# Syed Jahanzaib / aacable @ hotmail.com
# http//aacable.wordpress.com
# Version 1.0 / Modified on 28-5-2015

## START
# Check for temporary files that might be created by earlier, upto you : )
echo -e "Checking for old garbage files created earlier tby this script"
#[ -f /tmp/modeminfo ] && rm -f /tmp/modeminfo > /dev/null
#[ -f /tmp/mobilebal ] && rm -f /tmp/mobilebal > /dev/null

# Identify MODEM and output its result in tmp file
echo -e "Checking MODEM status"
gammu --identify > /tmp/modeminfo

# Look for Modem string , if not found give error and exit. MTK1 is my dlink modem string, you should change it with your modem string
A=`grep MTK1 "/tmp/modeminfo"`
if [ "$A" == "" ]; then
echo -e "Modem NOT Responding. Now scheduling USBRESET.SH file which will RESET the USB device & then re-run this script again"
echo -e "Modem NOT Responding. Now scheduling USBRESET.SH file which will RESET the USB device & then re-run this script again" > /tmp/mail.txt
# Email the 'modem not responding' result to ADMIN
mail -s 'Galaxy USB Modem NOT responding' aacable-hotmail-com < /tmp/mail.txt

# Running usb reset SCRIPT ,upto you, i made this usbreset.sh which reset the USB device and the modem start working again if its in HANG position, works for me
# /temp/resetusb.sh

#Scheduling current script to run after one minute again to see if modem is detectable after reset.
#echo /temp/checkbal.sh | at now + 1 min
else

# If modem found, then execute USSD code to inquire Mobile SIM Balance in tmp file, in this case I am using ZONG mobile sim
echo -e "Modem seems to be responding OK"
echo -e "================================="
echo -e "Now executing ZONG USSD code via GAMMU to inquire balance "
gammu --getussd *222# > /tmp/mobilebal

# Look for word insufficient in above output file, if not found then all ok.
B=`grep insufficient "/tmp/mobilebal"`
if [ "$B" == "" ]
then
# Print Mobile Balance, modify it accordingly
BAL=`cat /tmp/mobilebal |awk 'FNR == 3 {print $5}' `
echo -e "Balance & Modem both OK, Alhamdolillah"
echo -e "Balance & Modem both OK, Alhamdolillah" > /tmp/mail.txt
echo -e "Balance is $BAL PKR"
echo -e "Balance is $BAL PKR" >> /tmp/mail.txt
mail -s 'Daily Report: Galaxy USB Modem & SIM Balance is OK' aacable-hotmail-com < /tmp/mail.txt

# ELSE print modem and sim status
else
echo -e "Modem Responding but SIM have not enough balance"
echo -e "Modem Responding but SIM have not enough balance" > /tmp/mail.txt
mail -s 'Daily Report: Galaxy USB Modem OK BUT SIM have no Balance' aacable-hotmail-com < /tmp/mail.txt

fi
fi


Result:

CLI result:

Modem responding, but SIM have no balance

 

modem-not-balance

 

Modem responding, & SIM also have enough balance

report3-cli

 

Email Result:

 

MODEM-BAL-OK

Report1

 

 

 


 

Its just rough notes, you must modify things according to your network and requirements.

Regard’s
Syed Jahanzaib

May 26, 2015

May 25, 2015

May 22, 2015

Give your hardware some freedom of Speech ;)

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

userinf-2

 

MT-REPORT-SMS

playSMS_logo_full

kick


 CHANGE LOG:

  • 23-05-2015 – Added few functions in mt.sh to avoid SSH delay bug with mikrotik and to avoid false result
  • 08/06-2015 – Added individual dealer’s online users details according to VLAN, and user current online status with quota limits showing like data assigned, used and remained.

 

Recently I was working on a SMS reporting system to somehow receive some specific information for the Mikrotik health & Radius server information via sending an SMS and it should reply back the required information in some user friendly format. In the beginning it seemed fairly an easy task to accomplish by simply ssh (pass-wordless) to mikrotik and get the required info, BUT … executing the script from the terminal was working flawless, but executing this script from the playSMS was returning command_output EMPTY for ssh $variables. It took almost 15-16 hours wall banging efforts and finally I took another long route in order to achieve my task. You may find many other short routes to achieve this task, but I managed to get it working this way. Share your thoughts please.

TASK:

Send an sms with specific keyword to playSMS server, and it should then SSH to Mikrotik, and get some information (as described in the script) and send back that info (after making it user friendly) to the sender. so in short it’s a very good and handy way to get the server’s health report via sms Quickly ;)

Requirements:

This task is for people with some firm knowledge on Mikrotik and specially with Linux BASH scripting. But even if you are  a simple beginner, you can learn a lot from it. This is how you begin your journey by seeing far objects and then eventually reach to them by finding ways :) and that’s the exact way I started my astronomy journey as well :D

jupiter-first-modified-pic-24-3-2015

My First Picture of ‘Jupiter’ planet taken with 10″ manual Dobsonian with AFOCAL method, It’s an amazing experience when you see the planet from your Eyes Live !

 

 

1- Mikrotik with SSH/FTP services enabled (I changed the ports for security reasons)
2- Linux base System, I used my existing radius server for this purpose. It already have Kannel+playSMS configured with Dlink USB DWM-156 Modem. It can also ssh to mikrotik without password using per-configured public keys.

So you should read following guides before proceeding further.


 

Linux Section:

Here is the main script which will actually perform all the required actions and will gather information from mirkotik and radius itself.


#root@radius:/var/lib/playsms/sms_command/1# cat mt.sh

#!/bin/bash
# Script by Syed Jahanzaib / aacable@hotmail.com
# https://aacable.wordpress.com
# This script can inquire some specific Information from mikrotik and other server
# and send it back to sender via SMS on demand
# Version 2.0 -

MIKROTIK="192.168.1.2"
MTPORT="10001"
SERVER4="10.0.0.200"
SERVICE="radiusd"
DATE=`date`

# Check if Mikrotik is accessible or not, if not then EXIT immediately with error / zaib
if [[ $(ping -q -c 1 $MIKROTIK) == @(*100% packet loss*) ]]; then
echo "ALERT ..... MIKROTIK $MIKROTIK  is DOWN"
exit
else
echo "Galaxy Mikrotik Information SMS @ $DATE ..."
fi

# Execute Internetstatus script on mikrotik which will set the envrionment variable
ssh -q -p $MTPORT admin@$MIKROTIK /sys script run smsnetstatus > /tmp/pingresult

# Print PPP Active user in a file at Mikrotik
ssh -q -p $MTPORT admin@$MIKROTIK /ppp active print file=pppactive

# Print Internet Status Environment variables that we will use later
ssh admin@$MIKROTIK -p $MTPORT /sys script environment print file=netstatus

# Print Mikrotik Systems Resources in a file
ssh admin@$MIKROTIK -p $MTPORT /sys resource print file=resources

# Download pppactive file from mikrotik to local system in /tmp
scp -q -P $MTPORT admin@$MIKROTIK:pppactive.txt /tmp/pppactive.txt

# Verify if file is downloaded from mikrotik or not, if not dueo to ssh delay bug, then print error and exit :) Security Check by zaib
{
if [ ! -f /tmp/pppactive.txt ]; then
echo -e "ERROR: Mikrotik is live but it's SSH not accessible. Its a Mikrotik level BUG, You MUST upgrade with latest version. zaib"
exit 0
fi
}

# Download pppactive file from mikrotik to local system in /tmp
scp -q -P $MTPORT admin@$MIKROTIK:netstatus.txt /tmp/netstatus.txt

# Verify if file is downloaded from mikrotik or not, if not dueo to ssh delay bug, then print error and exit :) Security Check by zaib
{
if [ ! -f /tmp/netstatus.txt ]; then
echo -e "ERROR: Mikrotik is live but it's SSH not accessible. Its a Mikrotik level BUG, You MUST upgrade with latest version. zaib"
exit 0
fi
}

# Download Mikrotik System Resources file from mikrotik to local system in /tmp
scp -q -P $MTPORT admin@$MIKROTIK:resources.txt /tmp/resources.txt

# Verify if file is downloaded from mikrotik or not, if not dueo to ssh delay bug, then print error and exit :) Security Check by zaib
{
if [ ! -f /tmp/resources.txt ]; then
echo -e "ERROR: Mikrotik is live but it's SSH not accessible. Its a Mikrotik level BUG, You MUST upgrade with latest version. zaib"
exit 0
fi
}

# Print/Extract Uptime & CPU load only
cat /tmp/resources.txt  | awk 'NR==4' | sed -e 's/^[ \t]*//'
cat /tmp/resources.txt  | awk 'NR==11' | sed -e 's/^[ \t]*//'

# Extract Netstaus value from the csript we ran earlier on mt.
NETSTATUSVALUE=`grep "smsInternetStatus" /tmp/netstatus.txt | awk '{print $4}'`
echo "NET STATUS = $NETSTATUSVALUE"

# Print/Extract PPP Active Number of users
ACTIVE=`cat /tmp/pppactive.txt | wc -l`
echo "Active PPP users = $ACTIVE"

# Check RADIUS or whatever service PID status
pid=`pidof $SERVICE`
if [ "$pid" == "" ]; then
echo -e "$SERVICE service is NOT running, trying to start it ..."
service $SERVICE start
else
echo "$SERVICE = OK"
fi

# Check status of sharing media server
if [[ $(ping -q -c 1 $SERVER4) == @(*100% packet loss*) ]]; then
echo "ALERT ..... SERVER4 $SERVER4 is DOWN"
exit
else
echo "SERVER4 = OK"
fi
echo "Powered by Syed.Jahanzaib ..."

# Delete Files on Mikrotik so that false reproting may not occur / zaib
ssh -q -p $MTPORT admin@$MIKROTIK /file remove pppactive.txt
ssh -q -p $MTPORT admin@$MIKROTIK /file remove netstatus.txt
ssh -q -p $MTPORT admin@$MIKROTIK /file remove resources.txt

# delete files download from the local tmp folder in linux
rm -fr /tmp/netstatus.txt
rm -fr /tmp/pppactive.txt
rm -fr /tmp/resources.txt

exit 0

You should save this file mt.sh (or whatever name you decide) in following location

/var/lib/playsms/sms_command/1

 


Mikrotik Section

At mikrotik you should create and script which will actually check the internet status and update environment variable which we will fetch and use later via cat/awk.

Mikrotik Script name: smsnetstatus


# Modified few contents to suite local requirements and added descriptions
# Regard's / Syed Jahanzaib / https://aacable.wordpress.com

# Script Starts here...
# Internet Host to be checked You can modify them as per required, JZ
# We are using DOUBLE hosts to avoid FALSE REPORTING : ) / zaib

:local host1   "8.8.8.8"
:local host2   "192.0.78.13"

# Do not modify data below without proper understanding.
:local i 0;
:local F 0;
:local date;
:local time;
:global smsInternetStatus;
:global smsInternetLastChange;

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

# If both links are down and all replies are timedout, then link is considered down
:if (($F=10)) do={
:if (($smsInternetStatus="UP")) do={
:log error "smsRESULT:  - WARNING : The INTERNET link seems to be DOWN. Please Check";
:set smsInternetStatus "DOWN";

:set date [/system clock get date];
:set time [/system clock get time];
:set smsInternetLastChange ($time . " " . $date);
} else={:set smsInternetStatus "DOWN";}
} else={

##      If reply is received , then consider the Link is UP
:if (($smsInternetStatus="DOWN")) do={
:log warning "smsRESULT: WARNING :The INTERNET link have been restored";
:set smsInternetStatus "UP";

##      MAKE SURE TO CHANGE PPPO-OUT1 TO MATCH YOUR WAN INTERFACE
:local currentIP
:local externalInterface "pppoe-out1"

# get the current IP address from the external interface
:set currentIP [/ip address get [find interface="$externalInterface"] address]
# Strip netmask
:for i from=( [:len $currentIP] - 1) to=0 step=-1 do={
:if ( [:pick $currentIP $i] = "/") do={
:set currentIP [:pick $currentIP 0 $i]

:log warning "smsRESULT: PTCL LINK RE - CONNECTED with new WAN IP = $currentIP, Please check and confirm / zaib"

}
}

:set date [/system clock get date];
:set time [/system clock get time];
:set smsInternetLastChange ($time . " " . $date);
} else={:set smsInternetStatus "UP";}
}

# Script Ends Here.
# Thank you

Done!


Now you should create an SMS COMMAND in playSMS as showed below …

sms-command-fro-mt-report


 

TESTING …

Now test by sending an sms with word “mikrotik” to the playsms server mobile number. and you will shortly receive the reply :) Allah Shuker

MT-REPORT-SMS


 

playSMS LOG

here is the playsms log when it will receive the SMS with word mikrotik

127.0.0.1 localhost 2015-05-22 11:40:59 PID555ecf7b2f24c - L2 kannel__call # start load:/var/www/playsms/plugin/gateway/kannel/geturl.php
127.0.0.1 localhost 2015-05-22 11:40:59 PID555ecf7b2f24c - L3 kannel__incoming # addr:127.0.0.1 host:localhost t:2015-05-22 06:42:03 q:+923333021909 a:Mikrotik Q:13013 smsc:[]
127.0.0.1 localhost 2015-05-22 11:40:59 PID555ecf7b2f24c - L3 recvsms # isrecvsmsd:1 dt:2015-05-22 06:42:03 sender:+923333021909 m:Mikrotik receiver:13013 smsc:
127.0.0.1 localhost 2015-05-22 11:40:59 PID555ecf7b2f24c - L2 kannel__call # end load geturl
- - 2015-05-22 11:41:00 PID555ebd2ae9681 admin L3 recvsmsd # id:195 dt:2015-05-22 06:42:03 sender:+923333021909 m:Mikrotik receiver:13013 smsc:
- - 2015-05-22 11:41:00 PID555ebd2ae9681 admin L3 setsmsincomingaction # dt:2015-05-22 06:42:03 sender:+923333021909 m:Mikrotik receiver:13013 smsc:
- - 2015-05-22 11:41:00 PID555ebd2ae9681 admin L3 sms__command # command_exec:/var/lib/playsms/sms_command/1/mt.sh
- - 2015-05-22 11:41:00 PID555ebd2ae9681 admin L3 sms__command # command_output:Galaxy Mikrotik Information SMS @ Fri May 22 11:41:00 PKT 2015 ... uptime: 17h54m55s cpu-load: 13% NET STATUS = UP Active PPP users = 295 radiusd = OK SERVER4 = OK Powered by Syed.Jahanzaib ...
- - 2015-05-22 11:41:00 PID555ebd2ae9681 admin L2 sendsms # start uid:1 sender_id:[1234] smsc:[]
- - 2015-05-22 11:41:00 PID555ebd2ae9681 admin L3 sendsms # maxlen:15140 footerlen:7 footer:[ @admin] msglen:193 message:[Galaxy Mikrotik Information SMS @ Fri May 22 11:41:00 PKT 2015 ... uptime: 17h54m55s cpu-load: 13% NET STATUS = UP Active PPP users = 295 radiusd = OK SERVER4 = OK Powered by Syed.Jahanzaib ...]
- - 2015-05-22 11:41:00 PID555ebd2ae9681 admin L2 sendsms_queue_create # saving queue_code:60c45bad522ae25332bf3b37c33ea19f src:1234 scheduled:2015-05-22 11:41:00
- - 2015-05-22 11:41:00 PID555ebd2ae9681 admin L2 sendsms_queue_create # saved queue_code:60c45bad522ae25332bf3b37c33ea19f id:163
- - 2015-05-22 11:41:00 PID555ebd2ae9681 admin L3 sendsms_manipulate_prefix # before prefix manipulation:[+923333021909]
- - 2015-05-22 11:41:00 PID555ebd2ae9681 admin L3 sendsms_manipulate_prefix # after prefix manipulation:[+923333021909]
- - 2015-05-22 11:41:00 PID555ebd2ae9681 admin L3 simplerate_hook_rate_getbyprefix # rate not found to:+923333021909 default_rate:1
- - 2015-05-22 11:41:00 PID555ebd2ae9681 admin L3 simplerate_hook_rate_getcharges # uid:1 u:admin len:200 unicode:0 to:+923333021909 enable_credit_unicode:0 count:2 rate:1 charge:2
- - 2015-05-22 11:41:00 PID555ebd2ae9681 admin L2 sendsms # dst:1 sms_count:2 total_charges:2
- - 2015-05-22 11:41:00 PID555ebd2ae9681 admin L2 sendsms_queue_push # saving queue_code:60c45bad522ae25332bf3b37c33ea19f dst:+923333021909
- - 2015-05-22 11:41:01 PID555ebd2ae9681 admin L2 sendsms_queue_push # saved queue_code:60c45bad522ae25332bf3b37c33ea19f smslog_id:157
- - 2015-05-22 11:41:01 PID555ebd2ae9681 admin L2 sendsms # end queue_code:60c45bad522ae25332bf3b37c33ea19f queue_count:1 sms_count:2 failed_queue:0 failed_sms:0
- - 2015-05-22 11:41:01 PID555ebd2ae9681 admin L3 setsmsincomingaction # feature:sms_command datetime:2015-05-22 06:42:03 sender:+923333021909 receiver:13013 keyword:MIKROTIK message: raw:Mikrotik smsc:
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L2 sendsmsd # start processing queue_code:60c45bad522ae25332bf3b37c33ea19f sms_count:2 scheduled:2015-05-22 11:41:00 uid:1 gpid:0 sender_id:1234
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L2 sendsmsd # sending queue_code:60c45bad522ae25332bf3b37c33ea19f smslog_id:157 to:+923333021909 sms_count:2 counter:1
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L3 sendsms_intercept # msgtemplate modified sms_sender:[1234] sms_footer:[@admin] sms_to:[+923333021909] sms_msg:[Galaxy Mikrotik Information SMS @ Fri May 22 11:41:00 PKT 2015 ... uptime: 17h54m55s cpu-load: 13% NET STATUS = UP Active PPP users = 295 radiusd = OK SERVER4 = OK Powered by Syed.Jahanzaib ...] uid:[1] gpid:[0] sms_type:[text] unicode:[0] queue_code:[60c45bad522ae25332bf3b37c33ea19f] smsc:[]
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L3 outgoing_hook_sendsms_intercept # no SMSC found uid:1 parent_uid:0 from:1234 to:+923333021909
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L2 sendsms_process # start
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L3 simplerate_hook_rate_getbyprefix # rate not found to:+923333021909 default_rate:1
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L3 simplerate_hook_rate_getcharges # uid:1 u:admin len:200 unicode:0 to:+923333021909 enable_credit_unicode:0 count:2 rate:1 charge:2
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L2 simplerate__cansend # allowed user uid:1 sms_to:+923333021909 credit:55324.000 count:2 rate:1 charge:2 balance:55322
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L2 sendsms # saving smslog_id:157 u:1 g:0 gw:kannel smsc:kannel s:1234 d:+923333021909 type:text unicode:0 status:0
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L2 sendsms_process # saved smslog_id:157 id:157
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L3 sendsms # final smslog_id:157 gw:kannel smsc:kannel message:Galaxy Mikrotik Information SMS @ Fri May 22 11:41:00 PKT 2015 ... uptime: 17h54m55s cpu-load: 13% NET STATUS = UP Active PPP users = 295 radiusd = OK SERVER4 = OK Powered by Syed.Jahanzaib ... @admin len:200
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L3 kannel_hook_sendsms # enter smsc:kannel smslog_id:157 uid:1 to:+923333021909
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L3 kannel_hook_sendsms # URL: http://localhost:13013/cgi-bin/sendsms?username=kannel&password=KANNELPASSWORD&from=1234&to=%2B923333021909&dlr-mask=31&dlr-url=http%3A%2F%2Fclick.onmypc.net%3A1235%2Fplaysms%2Findex.php%3Fapp%3Dcall%26cat%3Dgateway%26plugin%3Dkannel%26access%3Ddlr%26type%3D%25d%26smslog_id%3D157%26uid%3D1&account=admin&text=Galaxy+Mikrotik+Information+SMS+%40+Fri+May+22+11%3A41%3A00+PKT+2015+...%0Auptime%3A+17h54m55s%0Acpu-load%3A+13%25%0ANET+STATUS+%3D+UP%0AActive+PPP+users+%3D+295%0Aradiusd+%3D+OK%0ASERVER4+%3D+OK%0APowered+by+Syed.Jahanzaib+...+%40admin
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L3 kannel__outgoing # smslog_id:157 response:0: Accepted for delivery
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L3 dlr # isdlrd:1 smslog_id:157 p_status:0 uid:1
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L3 kannel__outgoing # end smslog_id:157 p_status:0
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L2 simplerate__deduct # enter smslog_id:157
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L3 simplerate_hook_rate_getbyprefix # rate not found to:+923333021909 default_rate:1
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L3 simplerate_hook_rate_getcharges # uid:1 u:admin len:200 unicode:0 to:+923333021909 enable_credit_unicode:0 count:2 rate:1 charge:2
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L2 credit_hook_rate_setusercredit # saving uid:1 balance:55322
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L2 credit_hook_rate_setusercredit # saved uid:1 balance:55322
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L2 simplerate__deduct # user uid:1 parent_uid: smslog_id:157 msglen:200 count:2 rate:1 charge:2 credit:55324.000 balance:55322
- - 2015-05-22 11:41:01 PID555ecf7da2f5f - L2 simplebilling__post # saving smslog_id:157 rate:1 credit:55324.000 count:2 charge:2
- - 2015-05-22 11:41:02 PID555ecf7da2f5f - L2 simplebilling__post # saved smslog_id:157 id:156
- - 2015-05-22 11:41:02 PID555ecf7da2f5f - L3 simplerate__deduct # deduct successful uid:1 parent_uid: smslog_id:157
- - 2015-05-22 11:41:02 PID555ecf7da2f5f - L3 simplerate__deduct # credit_lowest_limit:0 balance:55322 charge:2
- - 2015-05-22 11:41:02 PID555ecf7da2f5f - L2 sendsms_process # end
- - 2015-05-22 11:41:02 PID555ecf7da2f5f - L2 sendsmsd # result queue_code:60c45bad522ae25332bf3b37c33ea19f to:+923333021909 flag:1 smslog_id:157
- - 2015-05-22 11:41:02 PID555ecf7da2f5f - L2 sendsmsd # finish processing queue_code:60c45bad522ae25332bf3b37c33ea19f uid:1 sender_id:1234 sms_count:2
- - 2015-05-22 11:41:02 PID555ebd2ae3e02 - L3 dlrd # id:157 smslog_id:157 p_status:0 uid:1


Another version of Mikrotik Info Script with Dealers online users details [in VLAN]

Another version which have some extended details like each dealer online users count which is helpful for dealers who can send sms to query there online users. script can be modified with security code or individual dealer by adding directives.


#!/bin/bash
MIKROTIK="192.168.1.1"
MTPORT="50001"
SERVER4="192.168.1.10" # Any other Server you may want to monitor in the report
SERVICE="radiusd" # Service you want to monitor like radius
DATE=`date`

# 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 "ALERT ..... MIKROTIK $MIKROTIK  is DOWN"
exit
else
echo "Galaxy Mikrotik Information SMS @ $DATE ..."
fi

# Execute Internetstatus script on mikrotik which will set the envrionment variable
ssh -q -p $MTPORT admin@$MIKROTIK /sys script run smsnetstatus > /tmp/pingresult

# Print PPP Active user in a file at Mikrotik
ssh -q -p $MTPORT admin@$MIKROTIK /ppp active print file=pppactive

# Print Internet Status Environment variables that we will use later
ssh admin@$MIKROTIK -p $MTPORT /sys script environment print file=netstatus

# Print Mikrotik Systems Resources in a file
ssh admin@$MIKROTIK -p $MTPORT /sys resource print file=resources

# Download pppactive file from mikrotik to local system in /tmp
scp -q -P $MTPORT admin@$MIKROTIK:pppactive.txt /tmp/pppactive.txt

# Verify if file is downloaded from mikrotik or not, if not dueo to ssh delay bug, then print error and exit :) Security Check by zaib
{
if [ ! -f /tmp/pppactive.txt ]; then
echo -e "ERROR: Mikrotik is live but it's SSH not accessible. Its a Mikrotik level BUG, You MUST upgrade with latest version. zaib"
exit 0
fi
}

# Download pppactive file from mikrotik to local system in /tmp
scp -q -P $MTPORT admin@$MIKROTIK:netstatus.txt /tmp/netstatus.txt

# Verify if file is downloaded from mikrotik or not, if not dueo to ssh delay bug, then print error and exit :) Security Check by zaib
{
if [ ! -f /tmp/netstatus.txt ]; then
echo -e "ERROR: Mikrotik is live but it's SSH not accessible. Its a Mikrotik level BUG, You MUST upgrade with latest version. zaib"
exit 0
fi
}

# Download Mikrotik System Resources file from mikrotik to local system in /tmp
scp -q -P $MTPORT admin@$MIKROTIK:resources.txt /tmp/resources.txt

# Verify if file is downloaded from mikrotik or not, if not dueo to ssh delay bug, then print error and exit :) Security Check by zaib
{
if [ ! -f /tmp/resources.txt ]; then
echo -e "ERROR: Mikrotik is live but it's SSH not accessible. Its a Mikrotik level BUG, You MUST upgrade with latest version. zaib"
exit 0
fi
}

# Print/Extract Uptime & CPU load only
cat /tmp/resources.txt  | awk 'NR==4' | sed -e 's/^[ \t]*//'
cat /tmp/resources.txt  | awk 'NR==11' | sed -e 's/^[ \t]*//'

# Extract Netstaus value from the csript we ran earlier on mt.
NETSTATUSVALUE=`grep "smsInternetStatus" /tmp/netstatus.txt | awk '{print $4}'`
echo "NET STATUS = $NETSTATUSVALUE"

# Extract PPP Active Number of users ACCORDING TO vlan
# These are pools assigned to pppoe users for different vlan's
VLAN9=`grep 10.0.9  /tmp/pppactive.txt | wc -l`
VLAN10=`grep 10.0.10  /tmp/pppactive.txt | wc -l`
VLAN13=`grep 10.0.13  /tmp/pppactive.txt | wc -l`
VLAN14=`grep 10.0.14  /tmp/pppactive.txt | wc -l`
VLAN15=`grep 10.0.15  /tmp/pppactive.txt | wc -l`
VLAN16=`grep 10.0.16  /tmp/pppactive.txt | wc -l`
VLAN17=`grep 10.0.17  /tmp/pppactive.txt | wc -l`
VLAN18=`grep 10.0.18  /tmp/pppactive.txt | wc -l`
VLAN19=`grep 10.0.19 /tmp/pppactive.txt | wc -l`

# Trim excess lines inlcuding license detgails and un-necessary stuff
TOTACTIVE=`cat /tmp/pppactive.txt |sed '1,5d' | wc -l`

# Print total and each vlan users according to above
echo "Total Active Users = $TOTACTIVE"
echo "Dealer = No. Of Online Users"
echo "DEALER-1 = $VLAN9"
echo "DEALER-2 = $VLAN10"
echo "DEALER-3 = $VLAN13"
echo "DEALER-4 = $VLAN14"
echo "DEALER-5 = $VLAN15"
echo "DEALER-6 = $VLAN16"
echo "DEALER-7 = $VLAN17"
echo "DEALER-8 = $VLAN18"
echo "DEALER-9 = $VLAN19"

# Check RADIUS or whatever service PID status
pid=`pidof $SERVICE`
if [ "$pid" == "" ]; then
echo -e "$SERVICE service is NOT running, trying to start it ..."
service $SERVICE start
else
echo "$SERVICE = OK"
fi

# Check status of sharing media server
if [[ $(ping -q -c 1 $SERVER4) == @(*100% packet loss*) ]]; then
echo "ALERT ..... SERVER4 $SERVER4 is DOWN"
exit
else
echo "SERVER4 = OK"
fi
echo "Powered by Syed.Jahanzaib ..."

# Delete Files on Mikrotik so that false reporting may not occur / zaib
ssh -q -p $MTPORT admin@$MIKROTIK /file remove pppactive.txt
ssh -q -p $MTPORT admin@$MIKROTIK /file remove netstatus.txt
ssh -q -p $MTPORT admin@$MIKROTIK /file remove resources.txt

# delete files download from the local tmp folder in linux
rm -fr /tmp/netstatus.txt
rm -fr /tmp/pppactive.txt
rm -fr /tmp/resources.txt

exit 0

Results of above script can be seen here


Galaxy Mikrotik Information SMS @ Mon Jun  8 12:55:49 PKT 2015 ...
uptime: 1d15h38m41s
cpu-load: 24%
NET STATUS = UP
Total Active Users = 414
Dealer = No. Of Online Users
DEALER-1 = 46
DEALER-2 = 42
DEALER-3 = 117
DEALER-4 = 6
DEALER-5 = 22
DEALER-6 = 17
DEALER-7 = 11
DEALER-8 = 53
DEALER-9 = 96
radiusd = OK
SERVER4 = OK
Powered by Syed.Jahanzaib ...

 


 

Regard’s
Syed Jahanzaib

 

Older Posts »

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 2,797 other followers