RADIUS Redundancy by using MYSQL Master-Master Replication

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]

---

 

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.

 

Now create test user

 

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

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

---

Adding both Radius Server entries in Mikrotik

Add both radius server

and at radius manager, add the NAS (mikrotik)

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 …

I will add few more details later….

 

Regard’s
Syed Jahanzaib

 

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

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 N  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

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

 

 

 

---

 

Regard’s
Syed Jahanzaib

Sending Email on Router Reboot with Logs and LASTSEEN time !

---

 

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 …

–

 

.

Regard’s
Syed Jahanzaib

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

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/near_expiry_alert.sh
chmod +x /temp/near_expiry_alert.sh
nano /temp/near_expiry_alert.sh

Now paste the following script

#!/bin/sh
#set -x
# BASH base SMS script for sending expiry notification for radius Manager 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
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
##############
# CHANGE these
##############
COMPANY="YOUR_COMPANY_NAME"
SQLID="root"
SQLPASS="SQLPASS"
DB="radius"
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLID --skip-column-names -s -e"
#Table which contain main users information
USER_TABLE="rm_users"
SRV_TABLE="rm_services"
# Interval before alert which should be sent to user before this number days
EXPIRY_DAYS_REMAIN="3"
# Kannel Server IP, if its local leave it as it is
KURL="http://127.0.0.1:13013"
KID="kannel"
KPASS="KANNELPASS"

###################
#Script starts here
###################
TMP="/tmp/$EXPIRY_DAYS_REMAIN.days.expiring_users_list.txt"
> $TMP
#DATE TIME FUNCTIONS
currenttime=$(date +%H:%M:%S)
# Add Script start execution entry in the /var/log/syslog to see if the script got executed or not
logger "Checking & sending SMS to near $EXPIRY_DAYS_REMAIN days expiry users, a reminder - Last executed @ $currenttime by the CRON scheduler ... Powered by SYED.JAHANZAIB"

# Export usernames and mobile from the mysql table in a file, which Expiry is after X days
$CMD "use $DB; SELECT username FROM $USER_TABLE WHERE expiration = DATE_ADD(CURDATE(), INTERVAL $EXPIRY_DAYS_REMAIN DAY);" > $TMP
TOTUSER=`cat $TMP | wc -l`
if [ ! -s $TMP ]
then
endtime=$(date +%H:%M:%S)
echo "- WARNING: No users found near expiration (that is $EXPIRY_DAYS_REMAIN Days)
- Script Ends Here
- EXITING peacefully...
"
exit 1
fi

TOTUSR=`cat $TMP | wc -l`
echo "- INFO: Total number of users with near expiry = $TOTUSR / No.s ...

"

# 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 | while read users
do
num=$[$num+1]
USERNAME=`echo $users |awk '{print $1}'`
SRVID=`$CMD "use $DB; SELECT srvid from $USER_TABLE where username = '$USERNAME';"`
CREDITS=`$CMD "use $DB; select credits from $USER_TABLE where username ='$USERNAME';" | cut -f1 -d"."`
MOBILE=`$CMD "use $DB; select mobile from $USER_TABLE where username ='$USERNAME';"`
EMAIL=`$CMD "use $DB; select email from $USER_TABLE where username ='$USERNAME';"`
EXPIRATION_DATE=`$CMD "use $DB; select expiration from $USER_TABLE where username ='$USERNAME';" |sed 's/00:00:00//g'`

# If there is balance/credit in user account then dont send alert,
if [ "$CREDITS" -gt 1 ];then
echo "$USERNAME - This user will expire on $EXPIRATION_DATE BUT he/she have deposit/credit in his/her account, therefore No need to send alert ..."
# Else Send him Email/SMS alert
else
# Store message in temporary holder
echo "$USERNAME - This user will expire on $EXPIRATION_DATE & he/she have NO deposit/credit, therefore sending email/sms alerts ..."
echo "Dear $USERNAME, Your internet account will expire on $EXPIRATION_DATE
$COMPANY" > /tmp/$USERNAME.$EXPIRY_DAYS_REMAIN.days.near.expiry.sms.alert.txt
#cat /tmp/$USERNAME.$EXPIRY_DAYS_REMAIN.days.near.expiry.sms.alert.txt
#echo "Sending EMAIL & SMS ALERT to mobile - $MOBILE / email - $EMAIL ..."

# Send SMS Alert via KANNEL
#curl "$KURL/cgi-bin/sendsms?username=$KID&password=$KPASS&to=$MOBILE" -G --data-urlencode text@/tmp/$USERNAME.$EXPIRY_DAYS_REMAIN.days.near.expiry.sms.alert.txt
# Send EMAIL message
#sendemail -u "$USERNAME account will expire on $EXPIRATION_DATE " -o tls=yes -s smtp.gmail.com:587 -t $EMAIL -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=/tmp/$USERNAME.$EXPIRY_DAYS_REMAIN.days.near.expiry.sms.alert.txt -o message-content-type=text
# Remove leftover entries
rm /tmp/$USERNAME.$EXPIRY_DAYS_REMAIN.days.near.expiry.sms.alert.txt
fi
done
# Script Ends here 

---

 

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.

 

---

 

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 by ‘crontab‘ command:

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!

 

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

---

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 for 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 / CENTOS
2. MySQL with Radius DB
3. Gnuplot Application

---

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 Generation 'ON THE FLY' using GNUPLOT
# you can use it like ./usergraph.sh 2016 01 (for Jan, 2016)
# By Syed Jahanzaib / aacable [at] hotmail.com
# https://aacable.wordpress.com
# Created: May, 2015
# Last Modified on  29th-JAN-2016

# Defining BASH Variables FOR MYSQL CREDENTIALS and host
SQLUSER="root"
SQLPASS="SQLPASSWORD"
SQLHOST="localhost"

# Date functions to find current date, month year, can be used in auto detect month type script
# you can modify it to auto detect as showed in other examples / zaib
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"
echo "$1 $2 $3"
# 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 '$2-$3%' 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/$1.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 "User Account - $1\n Download/Upload Report for Month $3 / Year $2\nTotal Data Used = $TOTAL GB\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 center offset 0,2 tc rgb 'red', \
"$TMP" using 1:(\$3/2**30):(sprintf("%.2f", \$3/2**30)) w labels notitle center offset 0,1 tc rgb 'green'

EOF
# GNUPLOT Script ends here
# Thank you : ) / zaib

---

Running the SCRIPT

Now execute the script by

/temp/usergraph.sh USERNAME 2016 01

(Like usergraph.sh zaib 2016 01) this will create graph on the fly for use ‘zaib’ for January, 2016 month

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

http://yourip/username.jpg

Note: the jpg file will be placed in /var/www folder under the username format, for ubuntu.

---

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 in few days. 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 PASSWORD 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 …

---

 

Regard’s
Syed Jahanzaib

Mikrotik with Cisco VLAN made easy

---

~!~ 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 …

---

 

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 …

 

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…

 

 

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

 

Test From Dealer-1 System

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.

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

 

---

 

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