Syed Jahanzaib Personal Blog to Share Knowledge !

July 30, 2015

playSMS Send SMS via Script/CLI using webservices token and PHP

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

playSMS_logo_full

If you want to send SMS via playSMS using bash script or CLI, use the WEB SERVICES options in playSMS to facilitate this function. Its very useful in many scenarios.

Example:

I wanted to schedule a bash script which should run daily twice , this script collects various information of different servers, routers and then summarize it and send to admin via SMS. I could do it with GAMMU or other sms tools too, but the issue was that the server already have KANNEL with gsm modem configured and (in general standard) Kannel dont accept message with special characters, spaced, and strange formats that this script output, (with urlencoding possible but i dont want to do it) or I had to use sed awk and other tools to proper format them which was overhead work. and I wanted to utilized KANNEL or playSMS.

Mr. Anton from playSMS suggested to use web-services of playSMS and it worked beautifully :)

This is how it was done.


 

1- Enable WEB SERVICES & GENERATE TOKEN

Goto My Account > User Configuration

Select YES from drop down menu for “Enable webservices” and “Renew webservices token

Click on SAVE.

As showed in the following image below …

 

1- token


 

Now on same page you will now see the TOKEN number as showed in the following image below …

2 token

Copy / note down this this number, It will serve as a kind of password to let us send sms via php script.


 

2- Create & schedule  PHP script to execute your shell script

Now we have to create a PHP script which will use the above TOKEN to let us run the bash script and send it’s output to admin mobile number :)

mkdir /temp
touch /temp/dailysms.php
chmod +x /temp/dailysms.php
nano /temp/dailysms.php

now paste the following date , but be sure to modify the token number as per your own setup, and the script which you want to execute via php.

#!/usr/bin/php -q
# Script provided by mr.anton
# https://forum.playsms.org/t/schedule-message-to-run-sms-command/194/2
# https://aacable.wordpress.com
# Dated: 30th July, 2015
# Syed Jahanzaib

<?php

/**
 * cli2sms.php by Anton Raharja (antonrd@gmail.com)
 * Example script to get data from shell script and send it as SMS via playSMS
 * You need to have a configured and working playSMS
 * In this example playSMS is accessible from http://localhost/playsms
 * Don't forget to chmod +x cli2sms.php to use it from Linux shell
 *
 * You may modify this script to suit your needs
 *
 * Example usage:
 * - get stat data (eg: uptime) and send it periodically (using cron) to admin's mobile phones
 * - https://aacable.wordpress.com/2015/07/30/playsms-send-sms-via-scriptcli-using-webservices-token/
 */

// suppress error message
error_reporting(0);

// playSMS username/account for sending SMS
$username = 'admin';

// Webservices token for above username
$token = '1194df9e20d06c3790f0c6fef49f174a';

// playSMS Webservices URL
$playsms_ws = 'http://localhost/playsms/index.php?app=ws';

// destination numbers, comma seperated or use #groupcode for sending to group
// $destinations = '#devteam,+6200123123123,+6200456456456';
// $destinations = '+6200123123123,+6200456456456'; # for multiple recipients</pre>
<pre>$destinations = '03333021909';</pre>
<pre>
// get message to send from another shell script or Linux command, for example 'uptime'
// $message = trim(shell_exec('uptime'));
$message = trim(shell_exec('/temp/dailysms.sh'));</pre>
<pre>
// send via playSMS HTTP API
if ($message) {
    $ws = $playsms_ws . '&u=' . $username . '&h=' . $token . '&op=pv';
    $ws .= '&to=' . urlencode($destinations) . '&msg='.urlencode($message) . '&nofooter=1';
    $ret = @file_get_contents($ws);

    // echo $ret;
    echo "OK: message sent" . PHP_EOL;
} else {
    echo "ERROR: message is empty" . PHP_EOL;
}

// end of script

 

 


TEST

Now test by running the php file we just created above.

/temp/dailysms.php

and you should see something like below if every thing goes smoothly as planned

root@radius:/temp# ./dailysms.php

OK: message sent

 

and you will soon receive the SMS on your Mobile.

3- mobile


Update: / TIPS
Later I found howto submit sms via kannel CLI as well. Described in the below link.

https://aacable.wordpress.com/2012/11/26/howto-configure-your-local-http-gateway-using-kannel-on-ubuntu/

How to Send SMS via HTTP command

Open your browser and paste the following command.

http://101.11.11.250:13013/cgi-bin/sendsms?username=kannel&password=kannel&to=03333021909&text=ZAIB+KANNEL+WORK

Howto send SMS via GAMMU CLI

gammu sendsms TEXT 03333021909 -text "Test Msg from GAMMU  .."

Howto send USSD CODES via GAMMU CLI

For balance check (below example is for zong balacne check)

gammu --getussd *222#

Howto send sms via CLI using KANNEL

curl "http://localhost:13013/cgi-bin/sendsms?username=kannel&password=KANNELPASS&to=03333021909&text=Welcome+Jahanzaib+Test+Message"

Sending mesage with new line using %0A code using KANNEL

curl "http://localhost:13013/cgi-bin/sendsms?username=kannel&password=KANNELPASS&to=03333021909&text=Welcome+Jahanzaib+Test+Message%0ANew+line"

Howto send sms using FILE via CLI using KANNEL

curl "http://127.0.0.1:13013/cgi-bin/sendsms?username=kannel&password=kannelpassword&to=03333333333" -G --data-urlencode text@/temp/message.txt

Howto send command output  sms via CLI using KANNEL

ls -l | curl "http://127.0.0.1:13013/cgi-bin/sendsms?username=kannel&password=kannelpassword&to=03333333333" -G --data-urlencode text@-

 

Regard’s

Syed Jahanzaib

July 24, 2015

Rough Notes on Sending Email Alert Upon Service(s) Failure in Linux

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

1

[Not for all as its just a messy type of temporary work] This script was made for an operator who was facing strange problem that in random timings , the mysql or some times radiusd service stopped automatically, unfortunately the error was not sorted out as it occurs in awkward timings when no one is at NOC, so I made an workaround in the form of this script so that we can diagnose the issue later in details and in the meanwhile manually startup of services should not be required. The script was scheduled to run after every 10 minutes.

Note: This is not a solution, Just a workaround for very small period of time.

This script will check one or more services status, if found stopped, it will try to re-start them, and then check there status again and output there status in email.

In this example I took MYSQL and RADIUS services.

Sections:

  1. Create Required Scripts
  2. Download sendEmail tool to send email alerts
  3. Check Service status
  4. Another version of script [spicework] with prevention of repeated email in case of scheduler

 

1- Create Required Scripts

Create New  script checkstatus.sh which will be check the required services status and will output there result in text file which will be later used to send email (only if services found stopped)

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

Now add following contents in it

#!/bin/bash
SRV1="radiusd"
SRV2="mysqld"
SRV2D="mysql"
pid1=`pidof $SRV1`
pid2=`pidof $SRV2`
CURDATE=`date`

# Delete existing mail file
rm -fr  /temp/radiusstatus

# Check for SRV1 - RADIUSD - If not running then add its entry to local file for mail
if [ "$pid1" == "" ]; then
echo -e "$SRV1 service is NOT running, Trying to start . . ."
echo -e "$CURDATE $SRV1 service is NOT running, Trying to start . . ." >> /temp/radiusstatus
var1=$SRV1
service $SRV1 restart

# If SRV1 is running then Ignore and check for next service
else
echo $SRV1 service is Running OK , no further action required, EXITING. Script by Syed Jahanzaib
fi

# Check for SRV2 - MYSQL - If not running then add its entry to local file for mail
if [ "$pid2" == "" ]; then
echo -e "$SRV2D service is NOT running, Trying to start . . ."
echo -e "$CURDATE $SRV2D service is NOT running, Trying to start . . ." >> /temp/radiusstatus
var2=$SRV2
service $SRV2D restart

# If SRV2 is running then Ignore and check for next service
else
echo $SRV2 service is Running OK , no further action required, EXITING. Script by Syed Jahanzaib
fi

# Emal the results to admin if file exists
if [ ! -f /temp/radiusstatus ]
then
echo -e "$SRV1 and $SRV2D Services seems to be running ..."
else
echo -e "ALERT ... Services seems to be stopped, check email "

# Check Services status after starting them by calling external script
echo -e "=========================================" >> /temp/radiusstatus
echo -e "Current Status After running this script" >> /temp/radiusstatus
/temp/checkservice.sh $SRV1 >> /temp/radiusstatus
/temp/checkservice.sh $SRV2 >> /temp/radiusstatus

#Finally send email with all the data gathered
/temp/sendEmail-v1.56/sendEmail -t aacable@hotmail.com -u "$CURDATE / Billing Alert: $var1 $var2 Services found stopped and restarted now" -o tls=yes -s smtp.gmail.com:587 -xu YOURGMAIL_ID@gmail.com -xp YOURGMAILPASSWORD -f YOUTGMAIL_ID@gmail.com -o message-file=/temp/radiusstatus  -o message-content-type=text
fi

Save and Exit.


 

Now create another script checkservice.sh which will be called by above parent script to get the final status of services.

touch /temp/checkservice.sh
chmod +x /temp/checkservice.sh
nano /temp/checkservice.sh

Now add following contents in it
#!/bin/bash
PID=`pidof $1`
if [ `pgrep $1` ]
then
echo "$1 = OK   with pid $PID"
else
echo "$1 = NOT RUNNING"
fi

Save & Exit.


 

 

2- Download sendEmail tool to send email alerts

Install sendEmail Tool

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

ADD SUPPORTING LIBRARY

for ubuntu

apt-get -y install libio-socket-ssl-perl libnet-ssleay-perl perl

for centos

yum -y install perl perl-Crypt-SSLeay perl-IO-Socket-SSL

TESTING SEND EMAIL

Try to send email using command line: Example

/temp/sendEmail-v1.56/sendEmail -t YOURMAIL@hotmail.com -u "Test Email" -s smtp.gmail.com:587 -xu YOURMGAILID@gmail.com -xp YOURGMAILPASSWORD -f  YOURMGAILIDgmail.com -o tls=yes

if you get message something like “sendEmail[xxxx]: Email was sent successfully!”, then you are good to GO LIVE !


 

3- Check Service status

Now run the script like (make sure mysql and radiusd services are running)

/temp/checkstatus.sh

and you will see following result if servies are already running

3- ok

and if any service found to be stopped, it will try to start it and will send you the result.
(If one service found stopped it will only try to start that service, if both services found stopped it will try to start them both)

now for test stop the mysql or radiusd service and run the script again, this time you will see the result as follows

1- statu

and at email you will see the following result

2- result

You can then schedule the service to run after every xx minutes in CRON

crontab -l

# Run after every 10 minutes , Check for stopped services of mysql and radius
*/10 * * * * /temp/checksrv.sh

4- Another version of script [spicework] with prevention of repeated email in case of scheduler

http://unix.stackexchange.com/questions/218117/howto-prevent-service-down-repeated-email-sent-alerts-via-bash

#!/bin/bash
if pgrep "mysql" > /dev/null
then
    echo "MYSQL Running"
    rm -f /var/run/.mysql_mail_sent
else
    echo "mysqld ALERT Stopped"
    if [ ! -f /var/run/.mysql_mail_sent ]; then
echo -e "Send your mysql mail here"
        date > /var/run/.mysql_mail_sent
    fi
fi
if pgrep "radiusd" > /dev/null
then
    echo "radiusd Running"
    rm -f /var/run/.radiusd_mail_sent
else
    echo "radiusd ALERT Stopped"
    if [ ! -f /var/run/.radiusd_mail_sent ]; then
echo -e "Send your radiusd mail here"
        date > /var/run/.radiusd_mail_sent
    fi
fi

 

To DO Task:

Add check to prevent re-sending of email alerts if service have permanent failure or cannot be started in any case, the script will keep sending the email :D , so a check is required. Will add it later in some free time.

 

Regard’s
Syed Jahanzaib

 

 

 

July 16, 2015

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

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,851 other followers