Syed Jahanzaib Personal Blog to Share Knowledge !

February 27, 2017

Radius Manager Theme/Skin Collection

Filed under: Radius Manager — Tags: , — Syed Jahanzaib / Pinochio~:) @ 3:33 PM

Following are few customized themes for DMASOFTLAB Radius manager , Designed by various web developers.

  1. Digital Theme [Responsive as well]
  2. Green Theme
  3. Blue Theme

 


General Steps to install theme

I assume you have DMASoftlab Radius Manager 4.1.x version installed and must be fully functional.

  • Download the theme file, unrar it in any temporary folder. you can use `tar zxvf filename.tar.gz /destination_folder`
  • Copy (or let’s say Overwrite) all the contents including files/folders from the (unzipped) folder name radiusmanager¬†in /var/www/radiusmanager (or for CENTOS, its /var/www/html/radiusmanager). You can cp -vr syntax /source /target
  • Clear the browser cache, and reload the ACP administration control panel) page.

.


1- Digital Theme

Download Link: Radius Manager Theme Official Link

ūüôā or use email …. ykwim

Sample Images:

1

2

3

4


2- Green Theme

Green Theme Download Link

green-1

 


Will update more later

 

Regard’s
Syed Jahanzaib

December 19, 2016

Incorrect key file for table ‚Äė./conntrack/xxxx@xxxxxx@xxxxxx.MYI‚Äô; try to repair it

Filed under: Radius Manager, Uncategorized — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 1:05 PM

corruption

Incorrect key file for table ‚Äė./conntrack/2016@002d12@002d16.MYI‚Äô; try to repair it

If you see the above error while accessing¬†Connection Tracking Reports in Radius Manager then …

Recommendations:

  • Make sure you aave plenty of Disk Space. Storage drive should be fast for high load servers preferably SSD or RAID based storage ,
  • Plenty of Memory is usually a good idea for DB systems ,
  • If its a heavy production server, try to move connection tracking to separate host as it takes lots of space and it adds good amount of payload to the CPU as well.
  • Perform tuning to enhance the mysql engine

To repair table …

Goto the conntrack folder [Ubuntu]
cd /var/lib/mysql/conntrack/
service mysql stop
# Change the file number as shown in the error 
myisamchk -r -v -f 2016@002d12@002d16.MYI

Once repair is done, start mysql / restart apache2 services , and test the desired tracking report again.

Regard’s
~Syed Jahanzaib~

August 24, 2016

Radius Manager Connection Tracking System for Mikrotik

Filed under: Mikrotik Related, Radius Manager — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 10:35 AM

trackme

As requested by few OP, following is a short technical reference guide¬†on how you can enable TCP/UDP connections in Mikrotik to be stored in Radius manager connection tracking database so that you can view user base connection tracking report. In my personal view, it’s not much useful and at low end hardware it creates bottleneck, but if you have good resources with some fast storage (like SSD or RAID-10) it is better to set it up.

CTS is best described in the manual by dmasoftlab manual as mentioned below

Radius Manager has a special feature: the Connection Tracking System. It is available only in Radius Manager CTS version or higher. With the help of it the system can track and log all the TCP and UDP connections for all registered (online) users.

By default when You install the CTS enabled version of Radius Manager, it will use the default CTS database (CONNTRACK). It is strongly recommended to use a separate database host for the CONNTRACK database, due to the enormous amount of data stored daily. It can be even a 100-500 MegaBytes (and in my personal experience it can grow as much as 3-5 GB on busy network, ZAIB) per day. Fast disks (like SSD in RAID 10 mode,  zaib) are also recommended to be able to seek and store the data in real time. Radius Manager periodically stores the traffic data to CONNTRACK database (typically in
every 5‚Äď60 seconds).


Mikrotik (6.x) Configuration to enable Firewall Logging to remote server (RM)

If you have already configured the radius manager, then the conntrack database also get configured via the installation script. Next step is to enable the firewall logging in the mikrotik router so that mikrotik can send the categorized TCP/UDP data to radius manager conntrack database.

In mikrotik, open terminal and issue following commands …

In below example, we have following IP scheme.

PPPoE users ip pool = 172.16.0.1-172.16.0.255
Radius Manager IP  = 101.11.11.254

/ip firewall filter add chain=forward src-address=172.16.0.1-172.16.0.255 protocol=tcp connection-state=new action=log

/ip firewall filter add chain=forward src-address=172.16.0.1-172.16.0.255 protocol=udp connection-state=new action=log

/system logging action add name=rmctszaib remote=101.11.11.254 target=remote remote-port=4950

/system logging add topics=firewall action=rmctszaib

If you don’t see any errors , you are good to Go.


RADIUS MANAGER SECTION:

1. Radius Manager should be licensed with CTS level. Basic license wont gonna work ..

2. Make sure that ¬†RMCONNTRACK service is UP and running.To make sure it’s running , get its process by

ps aux |grep rmconntrack

and you should get result something like following

root@radius:/# ps aux |grep rmconntrack
root xxxx 0.0 0.0 xxxxxx xxx ? Ssl 13:22 0:00 /usr/local/bin/rmconntrack

if you see its running, proceed further , otherwise you may start it manually by

rmconntrack ‚Äďx

Now login to RM ACP  >  Reports  >  Connection Report

Here you can get report for all or individual user.

As showed in the image below …

c1

 

c2

 


Regard’s
Syed Jahanzaib

 

 

1

June 28, 2016

RM: Auto Renew User if Deposit available

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

AutoRenewal

Revision History:
24-Jun-2015 / Added Base Script
29-Jun-2016 / Added Invoice function / Fixed bug if user quote is under 1 GB limit / few minor tweaks

As requested by an Valenzuela client.

In radius manager, there are few options to purchase credits via online payment gateways like paypal or others. If the user account is expired and he purchase service online, it adds the deposit into user account but it does not auto renew the service (as per my assumption, as paypal doesn’t works here in pakistan, so i have very little to no knowledge on it).

Example:

err

 

To make a workaround for this issue, I made a script that does the following.

  1. Scheduled to run after every 10 minutes
  2. Fetch users lists from rm_users table who have DEPOSIT available (credits above then 0)
  3. Check user account status , if Active Ignore it ,
  4. Else if expires, check the current service price and match it with the available deposit/credits,
  5. If deposit is not sufficient, then print error and exit,
  6. if deposit is enough, renew the service , add 30 days to service, add quota if any and sends email/sms to user about the renewal done by deposit : )

Disclaimer: The script can further be customized according to the requirements. No part of this script is copied from anywhere. You are free to use it, modify it as you like.This is my own idea Just to share with anyone who is in similar need or just for learning purposes !


SCRIPT!

#!/bin/bash
# Script to renew user account via check deposit and act accordingly
# For Radius Manager 4.1.x
# Created by Syed Jahanzaib
# https://aacable.wordpress.com / aacable@hotmail.com
# 24th Jun, 2016 , 18 Ramazan, 1437 Hijri
#set -x
# Colors Config . . . [[ JZ . . . ]]
ESC_SEQ="\x1b["
COL_RESET=$ESC_SEQ"39;49;00m"
COL_RED=$ESC_SEQ"31;01m"
COL_YELLOW=$ESC_SEQ"33;01m"
COL_GREEN=$ESC_SEQ"32;01m"
SQLUSER="root"
SQLPASS="zaib1234"
USERLIST="/tmp/deposituserlist.txt"
#Create list of users which ahve deposite more then 0.00 value, means valid deposite
mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT username, credits FROM rm_users where credits > '0.00';" > $USERLIST
#LOOK FOR VALID USER IN FILE, IF EMPTY THEN EXIT
USRVALID=`cat $USERLIST`
if [ -z "$USRVALID" ]; then
echo -e "ERROR: NO USER FOUND for matching ... exiting!"
exit 0
fi
# Apply Formula to read the file in which users list and act accordingly.
num=0
cat $USERLIST | while read users
do
num=$[$num+1]
USR=`echo $users | awk '{print $1}'`
DEPOSIT=`echo $users | awk '{print $2}' | sed 's/\..*$//'`
######################
# ACCOUNT EXPIRY CHECK and other variables
######################
TODAY=$(date +"%Y-%m-%d")
TODAYDIGIT=`echo $TODAY | sed -e 's/-//g'`
MONTH=$(date +"-%m")
CMONTH=`echo $MONTH | sed -e 's/-//g'`
MONTHYEAR=$(date +"%B-%Y")
ALPHAMONTHYEAR=`echo $MONTHYEAR #| sed -e 's/-//g'`
SRVEXPIRYFULL=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT expiration FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2'`
FULLNAME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT firstname, lastname FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2'`
MOBILE=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT mobile FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2'`
COUNTRY=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT country FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2'`
STATE=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT state FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2'`
ADDRESS=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT address FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2'`
SRVEXPIRYFULLD=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT expiration FROM radius.rm_users WHERE username = '$USR';" |awk '{print $1}' | sed 's/expiration//'`
SRVEXPIRY=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT expiration FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2' | sed -e 's/-//g' | sed 's/00:.*//'`
NEXTEXPIRYADD=$(date +"%Y-%m-%d" -d "+30 days")
LOGOFFDATE=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT lastlogoff FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2 {print $1,$2}'`
SRVID=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvid FROM radius.rm_users WHERE rm_users.username = '$USR';" |awk 'FNR == 2 {print $1}'`
SRVPRICE=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT unitprice FROM radius.rm_services WHERE rm_services.srvid = $SRVID;" |awk 'FNR == 2 {print $1}' | cut -f1 -d"."`
#LOOK FOR USER ACTUAL SERVICE NAME
PKGNAME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvname FROM radius.rm_services WHERE rm_services.srvid = '$SRVID';" |awk 'FNR == 2'`
# Look for Pakacge Quota trafficunitcomb
PKGQUOTA=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT trafficunitcomb FROM rm_services WHERE srvid= '$SRVID';" |awk 'FNR == 2'`
PKGQUOTAB=$(($PKGQUOTA / 1024))
PKGQUOTABYTES=$(($PKGQUOTA * 1024 * 1024))
LASTUSRBAL=$(($DEPOSIT - $SRVPRICE))

TIMEUNITEXP=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT timeunitexp FROM radius.rm_services WHERE srvid = '$SRVID';"`
TIMEBASEEXP=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT timebaseexp FROM radius.rm_services WHERE srvid = '$SRVID';"`

if [ "$TIMEBASEEXP" == "2" ]; then
EXPERIOD="$TIMEUNITEXP Days"
#echo "$EXPERIOD"
fi
# Set Quota Limit variable which will be used in the end
if [ "$TIMEBASEEXP" == "3" ]; then
EXPERIOD="$TIMEUNITEXP Month"
#echo "$EXPERIOD"
fi

#######zzzzzzzz#timebaseexp
# Set Expiry Date/Month Unit
if [ $PKGQUOTA -eq 0 ]
then
QT="UNLIMITED"
else
QT="$PKGQUOTA MB"
fi


# Check Service Expiry Date, if Active then ignore
if [ $SRVEXPIRY -gt $TODAYDIGIT ]; then
echo -e "$COL_GREEN User Account = $USR | ALREADY ACTIVE | TIMEUNIT = $EXPERIOD | Expiry Date = $SRVEXPIRYFULLD | User Deposit Available = $DEPOSIT | Pacakge Price = $SRVPRICE PKR | Next Expiry = $NEXTEXPIRYADD | Quota = $QT $COL_RESET
"
else
########### ACCOUNT STATUS EXPIRED BUT NOT ENOUGH DEPOSIT to RENEW ACTION ############
if [ "$DEPOSIT" -lt "$SRVPRICE" ]; then
echo -e "$COL_RED User Account = $USR | TIMEUNIT = $EXPERIOD | ERROR: Account was expired on $SRVEXPIRYFULLD but user $USR DOES NOT HAVE ENOUGH DEPOSIT IN USER ACCOUNT! Current Deposite is $DEPOSIT and Required is $SRVPRICE $COL_RESET
"
else
########### ACCOUNT STATUS EXPIRED and DEPOSIT IS ENOUGH TO RENEW ACTION ############
if [ $SRVEXPIRY -lt $TODAYDIGIT ] || [$SRVEXPIRY -eq $TODAYDIGIT ]; then

# RENEW USERS IF ALL CONDITIONS MATCHED / PRINT FETCHED VALUES , JUST FOR INFO / ZAIB
echo -e "$COL_YELLOW User Account = $USR ** RENEWING NOW ** | TIMEUNIT = $EXPERIOD | Expiry Date = $SRVEXPIRYFULLD | User Deposite Available = $DEPOSIT | Pacakge Price = $SRVPRICE PKR | Next Expiry = $NEXTEXPIRYADD | Quota = $QT $COL_RESET
Now Balance is = $LASTUSRBAL PKR"

# ADD 30 DAYS VALUE TO EXPIRED USER ACCOUNT
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET expiration = '$NEXTEXPIRYADD' WHERE username = '$USR';"
# ADD COMMENTS
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET comment = 'This account was last refresh from DEPOSIT $DATE' WHERE username = '$USR';"
# ADD SYSLOG ENTRY
mysql -u$SQLUSER -p$SQLPASS -e "use radius; INSERT INTO rm_syslog (datetime, ip, name, eventid, data1) VALUES (NOW(), 'n/a', 'DEPOSIT_$USR', '$USR', '$USR renewd service > $PKGNAME');"
# UPDATE User Balance
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET credits = '$LASTUSRBAL' WHERE username = '$USR';"
# ADD INVOICE
mysql -u$SQLUSER -p$SQLPASS -e "use radius; INSERT INTO rm_invoices (managername, username, date, bytesdl, bytesul, bytescomb, downlimit, uplimit, comblimit, time, uptimelimit,
 days, expiration, capdl, capul, captotal, captime, capdate, service, comment, transid, amount, invnum,
 address, city, zip, country, state, fullname, taxid, paymentopt, paymode, invtype, paid, price, tax, remark,
 balance, gwtransid, phone, mobile, vatpercent )
 VALUES
 ('admin', '$USR', NOW(), '0', '0', '$PKGQUOTABYTES', '0', '0', '$PKGQUOTABYTES', '0', '0', '30', '$NEXTEXPIRYADD', '0', '0', '1', '0', '1', '$PKGNAME', 'This user service renewed by Deposit/Payment', '577343812eee0', '1', '2016-0021', '$ADDRESS', '$CITY', '00000', '$COUNTRY', '$STATE', '$FULLNAME', 'n/a',
 DATE_ADD(CURDATE(), INTERVAL '10' DAY), '0', '0', '$TODAY', '$SRVPRICE', '0.000000', '', '$LASTUSRBAL', '', '$MOBILE', '$MOBILE', '0.00' );"

mysql -u$SQLUSER -p$SQLPASS -e "use radius; INSERT INTO rm_invoices (managername, username, amount, price, tax, vatpercent, balance,
 date, service, paymode, invgroup, paymentopt, transid)
 VALUES ('admin', 'admin', 1, '-$SRVPRICE', '0', '0.00',
 '', NOW(), 'Ref.: C-$TODAY', '2', '1', DATE_ADD(CURDATE(), INTERVAL '10' DAY),
 '577343812eee0' );"

# UPDATE Quota limitations if any, else ignore
if [ "$PKGQUOTA" -ne 0 ]; then
echo "Adding $PKGQUOTA MB Quota Limit for $USR as well
"
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET comblimit = '$PKGQUOTABYTES' WHERE username = '$USR';"
else
echo "No need to add quota"
fi
fi
fi
fi
done
# THE END SCRIPT ENDS HERE #
# SYED JAHANZAIB

RESULT!

1


 

June 22, 2016

Retrieve User Old/Original Password in RM

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

lostpass

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


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

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

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

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

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

the Script ! [SAMPLE]

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

Your Password is $PASS

Regard's
$COMPANY"

Execute the script and see the result.

shpass


PHP Form base method using Shell Script !

f1

f2

 

Sample php/shell files uploaded to

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

Make sure you add good security measurements first !

 

Regard’s
Syed Jahanzaib

 

June 13, 2016

Sending Email/SMS Alert to User for Service Change Event

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

srvchange

 

Screenshot_2016-06-13-12-56-48

SMS Alert


Reference Notes:

Requirements:

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


Solution:

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

Neat & clean.


 

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

1- mySQL Trigger

Create file name srvchangetriggers.sql and paste following data

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

2- mySQL Table

Add mySQL table where records will be saved.

Create file name rmsrvchangetable.sql and paste following date

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

Importing the .sql files in Radius DB / mySQL

Now import both files  in radius DB by command

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

Test The Changes …

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

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

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

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

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

and paste following data…

the Script:

 

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

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

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

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

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

# Company Footer
COMPANY="JZ_ISP"

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

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

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

$COMPANY"

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

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

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

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


End Results !

Now execute the Script  and witness the Fun !

srvchange


Regard’s
Syed Jahanzaib

June 2, 2016

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

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

codes


DISCLAIMER:

JUST AN EXAMPLE SAMPLE !

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

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

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

BASH is Fun !

Regard's
Syed Jahanzaib

Scenario:

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

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

mikrotik log error full pool

 


Requirements

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


Solution

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

mtdhcplog

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

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

 


Mikrotik Section #

Add following script in mikrotik script section …


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

:local criticalthreshold 85
:local warnthreshold 50

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

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

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

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

:set line (" " . $poolname)

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

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

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

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

# if len array $minaddress = $maxaddress
}

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

# foreach r
}

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

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

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

# foreach p
}
# /ip pool
}


Create Tables in DB first !

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

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

mysql -u root -prootpassword radius < mikrodhcp.sql 


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

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

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

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

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

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

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

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

IMPORTANT ! TEST THE TABLE !

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

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

 

and you may get following result if ALL is OK !

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

Now you can use following bash script …

the BASH SCRIPT !


#!/bin/bash
#set -x
# Script to fetch dhcp ip pool results from the mikrotik
# then update these results in mysql table, and email accordingly
# No portion of this script is copied from the internet.
# You are free to copy, modify, distribute it as you like
# Make sure you change all the variables as required like mysql id, tables etc.
# Created by : Syed Jahanzaib / aacable @ hotmail dot com
# https://aacable.wordpress.com
# Created: 2nd-MAY-2016

clear

# Colors Config . . . [[ JZ . . . ]]
ESC_SEQ="\x1b["
COL_RESET=$ESC_SEQ"39;49;00m"
COL_RED=$ESC_SEQ"31;01m"
COL_GREEN=$ESC_SEQ"32;01m"

#Temporary Holder for DHCP Status from Mikrotik
RESULT="/tmp/dhcpstatus.txt"
> $RESULT

#Mikrotik Details
MIKROTIK="1.2.3.4"
MTPORT="8291"
MTDHCPSCRIPT="dhcpstatus"

# DATE TIME
DATE=`date`
TODAYTIME=$(date +"%Y-%m-%d %T")

#MYSQL INFO
SQLUSER="MYSQL-ROOT"
SQLPASS="MYSQL-PASSWPORD"
DB="radius"
TABLE="mikrodhcp"
MAINTABLE="rm_users"
ALERTPERCENTAGE="50"

#EMAIL SECTION
GMAILID="YOURGMAILID@gmail.com"
GMAILPASS="GMAILPASS"
ADMINMAIL1="YOURADMINMAIL@hotmail.com"
COMPANY="YOUR COMPANY (Pvt) LTD"
FOOTER="Powered by Syed Jahanzaib"
# Create mikrodhcp table if not exists
DBCHECK=`mysql -u$SQLUSER -p$SQLPASS -e " SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '$DB';"`
if [ ! -z "$DBCHECK" ];
then
echo -e "Step-1# Checking $DB DB ... $DB database Found OK, proceeding further ... $COL_GREEN OK $COL_RESET"
#sleep 3
else
echo -e "$COL_RED ERROR: $DB database does NOT exists in mysql. it is required to store dhcp pool status data ...$COL_RESET"
exit 0
fi
# Create mikrodhcp table if not exists
TABLECHECK=`mysql -u$SQLUSER -p$SQLPASS -e " SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '$DB' AND TABLE_NAME = '$TABLE';"`
if [ ! -z "$TABLECHECK" ];
then
echo -e "Step-2# Checking $TABLE table ... $TABLE TABLE Found OK, proceeding further ... $COL_GREEN OK $COL_RESET"
#sleep 3
else
echo -e "$COL_RED ERROR: $TABLE does NOT exists in $MAINTABLE. it is required to store mikroptik dhcp pool status data ...$COL_RESET"
exit 0
fi
# Check if Mikrotik is accessibel or not, if not then EXIT immediately with error / zaib
if [[ $(ping -q -c 1 $MIKROTIK) == @(*100% packet loss*) ]]; then
echo -e "$COL_RED ALERT ..... MIKROTIK $MIKROTIK is DOWN$COL_RESET"
exit
else
echo -e "Step-3# Mikroik is Accessible, now proceeding further ... $COL_GREEN OK $COL_RESET"
fi

# Execute script on mikrotik which will get the required results liek dhcp ip pool status
ssh -q -p $MTPORT admin@$MIKROTIK /sys script run $MTDHCPSCRIPT > $RESULT

# VERIFY $RESULT FILE
A=`cat $RESULT`
B="no such item"
if [ "$A" == "$B" ];
then
echo -e "$COL_RED Mikrotik Script name '$MTDHCPSCRIPT' not found on Mikrotik. Please verify script name, test it on mikrotik first .... $COL_RESET"
exit 0
fi
echo -e "Step-4# Mikroik script fetched is Accessible, now proceeding further ... $COL_GREEN OK $COL_RESET"

# Verify if file is downloaded from mikrotik or not, if not dueo to ssh delay bug or other , then print error and exit ūüôā Security Check by zaib
{
if [ ! -f $RESULT ]; then
echo -e "$COL_RED ERROR: Mikrotik $MIKROTIK is live but it's SSH not accessible !!! $COL_RESET"
exit 0
fi
}
echo -e "Step-5# Mikroik $MIKROTIK SSH is accessible, now proceeding further ... $COL_GREEN OK $COL_RESET"

echo -e "Showing Results fetched from Mikrotik script ... $COL_GREEN OK $COL_RESET
"

echo -e "[POOL-NAME] [IP-USED-IN-POOL] [TOTAL-IP-IN-POOL] [POOL-USED-PERCENTAGE-%]" | awk '{printf "%-30s %-40s %-40s %-40s\n",$1,$2,$3,$4}'
echo ""
# Run Loop Formula
# Apply Formula to read the file in which dismissed users list and act accordingly.
num=0
cat $RESULT | while read data
do
num=$[$num+1]
POOLNAME=`echo $data | awk '{print $1}'`
POOLSTATUS=`echo $data | awk '{print $2}'`
POOLUSEDPERC=`echo $data | awk '{print $3}'`
POOLIPTOTAL=`echo $data | awk '{print $2}' | sed 's/\(\[\|\]\)//g' | sed 's#/#\ #g' | awk '{print $2}'`
POOLIPUSEDNO=`echo $data | awk '{print $2}' | sed 's/\(\[\|\]\)//g' | sed 's#/#\ #g' | awk '{print $1}'`

# Adding POOL names in table, so they can be updated according to teh usage in later stage ... zaib
mysql -u$SQLUSER -p$SQLPASS -e "use $DB; INSERT INTO $TABLE (mikrotikip, poolname) SELECT * FROM (SELECT '$MIKROTIK', '$POOLNAME') AS tmp WHERE NOT EXISTS (
SELECT poolname FROM $TABLE WHERE poolname = '$POOLNAME') LIMIT 1;"
# If percentage is high, ALERT in RED
if [ "$POOLUSEDPERC" -gt $ALERTPERCENTAGE ]
then
#echo -e "$COL_RED ALERT: $POOLNAME have consumed $POOLIPUSEDNO ips from $POOLIPTOTAL Total IPs / Percetnage Used = $POOLUSEDPERC % $COL_RESET"
echo -e "$COL_RED$POOLNAME $POOLIPUSEDNO $POOLIPTOTAL $POOLUSEDPERC Crossed $ALERTPERCENTAGE% $COL_RESET" | awk '{printf "%-40s %-40s %-40s %-5s %-5s %-5s *** ALERT ***\n",$1,$2,$3,$4,$5,$6}'

# UPDATE pool status with ALERT Status and other info
mysql -u$SQLUSER -p$SQLPASS -e "use $DB; UPDATE $TABLE SET mikrotikip = '$MIKROTIK' , poolipusedno = '$POOLIPUSEDNO' , pooliptotal = '$POOLIPTOTAL' , percentage = '$POOLUSEDPERC' , status = '1' , lastupdate = '$TODAYTIME' WHERE poolname = '$POOLNAME';"

else

# If percentage is low, Show result and update mysql table as well
#echo -e "$COL_GREEN NORMAL USAGE: $POOLNAME have consumed $POOLIPUSEDNO ips from $POOLIPTOTAL Total IPs / Percentage Used = $POOLUSEDPERC % $COL_RESET"
echo -e "$COL_GREEN$POOLNAME $POOLIPUSEDNO $POOLIPTOTAL $POOLUSEDPERC $COL_RESET" | awk '{printf "%-40s %-40s %-40s %-40s\n",$1,$2,$3,$4}'

# UPDATE pool status with normal values
mysql -u$SQLUSER -p$SQLPASS -e "use $DB; UPDATE $TABLE SET mikrotikip = '$MIKROTIK' , poolipusedno = '$POOLIPUSEDNO' , pooliptotal = '$POOLIPTOTAL' , percentage = '$POOLUSEDPERC' , status = '0' , mailsent = '0' , lastupdate = '$TODAYTIME' WHERE poolname = '$POOLNAME';"
fi

# Testing if email is required to be sent, if not alreasy sent
MAILSENT=`mysql -uroot -pView*pak --skip-column-names -e "use radius; select mailsent from mikrodhcp where poolname = '$POOLNAME';"`
if [[ $POOLUSEDPERC -gt $ALERTPERCENTAGE && $MAILSENT -eq 0 ]]
then
echo "Sending email for $POOLNAME ..."
mysql -u$SQLUSER -p$SQLPASS -e "use $DB; UPDATE $TABLE SET mailsent = '1' where poolname = '$POOLNAME';"

##################### START SENDING EMAIL
# create temporary holder where EMAIL will be stored
EMAILFILE="/tmp/$POOLNAME.dhcp.email"
> $EMAILFILE

echo "$COMPANY DHCP ALERT:

$POOLNAME pool in Mikrotik DHCP have crossed $ALERTPERCENTAGE % Limit

$POOLNAME have consumed $POOLIPUSEDNO ips from $POOLIPTOTAL Total IPs
$POOLNAME Percetnage Used = $POOLUSEDPERC %

Regard's

$COMPANY
$FOOTER" > $EMAILFILE

# Make sure you install sendEMAIL tool and test it properly before using email section.
# SEND EMAIL Alert As well using sendEMAIL tool using GMAIL ADDRESS.
# If you want to send email , use below ...

echo "Sending EMAIL ALERT to $ADMINMAIL1  ..."
/temp/sendEmail-v1.56/sendEmail -u "$COMPANY DHCP ALERT: $POOLNAME have consumed $POOLUSEDPERC %." -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$EMAILFILE -o message-content-type=text
fi
##################### EMAIL SENT DONE

fi

if [[ $POOLUSEDPERC -gt $ALERTPERCENTAGE && $MAILSENT -eq 1 ]]
then
echo "Email alert already sent for $POOLNAME to $ADMINMAIL1..."
#mysql -u$SQLUSER -p$SQLPASS -e "use $DB; UPDATE $TABLE SET mailsent = '1' where poolname = '$POOLNAME';"
fi
done

###### LOOP DONE ########
#Reset Terminal Color to Default
tput sgr0

POOLIPTOTAL=`cat $RESULT | awk '{print $2}' | sed 's/\(\[\|\]\)//g' | sed 's#/#\ #g' | awk '{print $2}'`
POOLIPUSEDNO=`cat $RESULT | awk '{print $2}' | sed 's/\(\[\|\]\)//g' | sed 's#/#\ #g' | awk '{print $1}'`

TOTALIP=`echo "$POOLIPTOTAL" | awk '{ sum+=$1} END {print sum}'`
USEDIP=`echo "$POOLIPUSEDNO" | awk '{ sum+=$1} END {print sum}'`

echo "
Total USED IPs = $USEDIP
Total IPs in POOL = $TOTALIP"
echo -e "Updating MYSQL Table on Billing @ $DATE ... $COL_GREEN OK $COL_RESET"
echo "Powered by Syed Jahanzaib"


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

 

SCRIPT EXECUTION RESULT #1

1-dhcp-alert-on-bash-screen

 

SCRIPT EXECUTION RESULT #2

 

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

 

TABLE RESULTS AFTER SCRIPT UPDATE !

5- table result


EMAIL ALERT SAMPLE #1

 

2- dhcp alert amil sub

EMAIL ALERT SAMPLE #2


3- dhcp billing alert full mail

 


Next Tasks: ¬†To be continued …

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

 

May 31, 2016

May 24, 2016

Disconnect deleted user from the NAS ACTIVE list using RADCLIENT

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

disconnectimage


SCENARIO:


Problem:

[As required by an specific OP]

When the OP deleted any user account from the Radius Billing system (example Radius manager) AND if his session is ACTIVE on the NAS , he will not disconnect automatically from the active users list [on the NAS] and he will continue to use the internet as long as his old session is connected. If the network is stable enough, the user can continue to use internet for days . So ultimately the user will become blood sucking vampire : ) ~


Solution:

We can schedule following script to run every 5 minutes. It will fetch the deleted users from the rm_syslog events, and will display the list, and then sends DISCONNECT request to the NAS to remove those users.

We can also use SSH or API method [preferred] , but it requires additional steps and skills. and It largely depends on the OP requirements and his skills to manage things as desired.

If there are multiple paths to reach the destination,
Select one with the least complications !
/ zaiB


Requirements:

radclient , utility which will send the disconnect requests.


the SCript !

 


#!/bin/bash
# set -x
# SCRIPT to fetch data of users removed manually from teh radius and disconnect them from the mikrotik active list.
# Syed Jahanzaib / aacable @ hotmail.com / https://aacable.wordpress.com
# 24-MAY-2016

# Setting FILE Variables
TMPFILE="/tmp/disconusers.txt"
> $TMPFILE

# Mikrotik NAS Details
NAS="192.168.0.1"
NASPORT="1700"
SECRET="PUT_RADIUS_SECRET_HERE"
CURDATE=`date`

#MYSQL INFO
SQLUSER="root"
SQLPASS="zSQL_PASSWORD"

#Interval in minutes
INTERVAL="5"

# Mysql query to fetch users whoes accounts are deleted from radius database.

# Print info
#mysql -u$SQLUSER -p$SQLPASS -e "use radius; select data1 from rm_syslog where eventid = '2' AND datetime >= NOW() - INTERVAL $INTERVAL MINUTE;"
# store in file
mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; select data1 from rm_syslog where eventid = '2' AND datetime >= NOW() - INTERVAL $INTERVAL MINUTE;" > $TMPFILE
# Check if no user is deleted in DEFINED interval
# Echo this info for admin info purposes.

if [ -s $TMPFILE ]
then
echo "Following Users have Found for disconnection at Mikrotik $NAS..."

echo "DATE | USERNAME | NAS"

# Apply Formula to read the file in which dismissed users list and act accordingly.
num=0
cat $TMPFILE | while read users
do
num=$[$num+1]
USERNAME=`echo $users | awk '{print $1}'`

# Send Disconnection Packet to Mikrotik/NAS in order to disconnect user now
echo "$CURDATE | $USERNAME | $NAS"
done
echo ""
echo "Holding 10 seconds so you can review the list then it will start disconnecting the users from NAS $NAS"
sleep 10

# Applying Formula again to DISCONNECT users from the NAS
num=0
cat $TMPFILE | while read users
do
num=$[$num+1]
USERNAME=`echo $users | awk '{print $1}'`

# SEND DISCONNECT REQUEST TO NAS FOR SPECIFIC USERS
echo user-name=$USERNAME | radclient -x $NAS:1700 disconnect $SECRET
done

else

echo "No user have found deleted. Nothing to do..."
fi

# Script End
# Regard's / zaib


Results:

disc

 


Regard’s
Syed Jahanzaib

May 19, 2016

An Example of Sending SMS Alert for Daily Quota Users

Filed under: Radius Manager — Tags: — Syed Jahanzaib / Pinochio~:) @ 5:07 PM

 

Screenshot_2016-05-19-17-04-06

alert1

 

Scenario:

We have daily quota users as described here.

https://aacable.wordpress.com/2012/11/20/mikrotik-radius-manager-quota-base-service/

OP want to send alert when daily quota users crosses 70% of there allowed daily traffic quota. Since RM sends alert for  TOTAL traffic only , not for daily, therefore I made following workaround.

The purpose of this script is to send SMS/Email alert to user who have consumed 70% of there daily allowed download/upload quota [value must be set in combined unit]. Once the user will use 70% of his allowed traffic, an SMS alert will be sent using local KANNEL SMS gateway and it will update flag in rm_users table which will prevent repetitive sms. only one sms alert will be sent in one day. once the date will be changed, the script will update the flags to 0, so that it will send sms alert again once the quota crosses the line again.

It may be scheduled to run after every 10 minutes or whatever the suitable interval according to your billing load.

Disclaimer:

Following is an LAB test version. It will generate many queries and may put burden on heavily production server. So make sure if you are using it, trim it and remove junk data before deploying in production.

Plus I know that its¬†not an elegant way to perform this task. If it could be done via php/rm itself that would be best, but since RM is a protected system and we cannot modify it, therefore i was forced to take the ‘dirty workaround’ route to achieve the task. in production i will trim it to make sure it put minimum payload on the server. It took almost 3 days to make it work.

Copyright:

No part of this post is copied from any where. Its all made by myself. You are free to use/modify/share it as you like.

~ Syed Jahanzaib ~


#!/bin/bash
#set -x
TODAY=$(date +"%Y-%m-%d")
TODAYTIME=$(date +"%Y-%m-%d %T")
SQLUSER="root"
SQLPASS="YOUR-SQL-PASSWORD"
TMPUSERINFO="/tmp/username.txt"
QUOTAPERCLIMIT="70"

# Kannel SMS Gateway Details
KHOST="YOUR-KANNEL-SMS-GW-IP"
KID="kannel"
KPASS="KANNEL-PASSWORD"

> /tmp/username.txt
> /tmp/tempuser.txt

# Create QMAIL table if not exists
QMAILCHECK=`mysql -uroot -p$SQLPASS -e " SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'radius' AND TABLE_NAME = 'rm_users' AND COLUMN_NAME = 'qmail';"`
if [ ! -z "$QMAILCHECK" ];
then
echo "Step-1 Check QMAIL Column in rm_users ...
QMAIL Column Found OK, proceeding further ..."
else
echo "QMAIL Column does NOT exists in rm_users table. it is required to prevent repeating email being sent to users, creating one NOW ..."
mysql -uroot -p$SQLPASS -e "use radius; ALTER TABLE rm_users ADD qmail TINYINT(1) NOT NULL;"
mysql -uroot -p$SQLPASS -e "use radius; ALTER TABLE rm_users ADD qmailtime DATETIME NOT NULL;"
fi

# Qurty Active Users list and store in it file
mysql -uroot -p$SQLPASS -e "use radius; SELECT SQL_CALC_FOUND_ROWS username, firstname, lastname, address, city, zip, country, state, phone, mobile,
 email, company, taxid, srvid, downlimit, uplimit, comblimit, expiration, uptimelimit, credits, comment,
 enableuser, staticipcpe, staticipcm, ipmodecpe, ipmodecm, srvname, limitdl, limitul, limitcomb, limitexpiration,
 limituptime, createdon, verifycode, verified, selfreg, acctype, maccm, LEFT(lastlogoff, 10)
 , IF (limitdl = 1, downlimit - COALESCE((SELECT SUM(acctoutputoctets) FROM radacct
 WHERE radacct.username = tmp.username) -
 (SELECT COALESCE(SUM(dlbytes), 0) FROM rm_radacct
 WHERE rm_radacct.username = tmp.username), 0), 0),

 IF (limitul = 1, uplimit - COALESCE((SELECT SUM(acctinputoctets) FROM radacct
 WHERE radacct.username = tmp.username) -
 (SELECT COALESCE(SUM(ulbytes), 0) FROM rm_radacct
 WHERE rm_radacct.username = tmp.username), 0), 0),

 IF (limitcomb =1, comblimit - COALESCE((SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct
 WHERE radacct.username = tmp.username) -
 (SELECT COALESCE(SUM(ulbytes + dlbytes), 0) FROM rm_radacct
 WHERE rm_radacct.username = tmp.username), 0), 0),

 IF (limituptime = 1, uptimelimit - COALESCE((SELECT SUM(acctsessiontime) FROM radacct
 WHERE radacct.username = tmp.username) -
 (SELECT COALESCE(SUM(acctsessiontime), 0) FROM rm_radacct
 WHERE rm_radacct.username = tmp.username), 0), 0)

 FROM
 (
 SELECT username, firstname, lastname, address, city, zip, country, state, phone, mobile, email, company,
 taxid, rm_users.srvid, rm_users.downlimit, rm_users.uplimit, rm_users.comblimit, rm_users.expiration,
 rm_users.uptimelimit, credits, comment, enableuser, staticipcpe, staticipcm, ipmodecpe, ipmodecm, srvname, limitdl,
 limitul, limitcomb, limitexpiration, limituptime, createdon, verifycode, verified, selfreg, acctype, maccm,
 mac, groupid, contractid, contractvalid, rm_users.owner, srvtype, lastlogoff
 FROM rm_users
 JOIN rm_services USING (srvid)

 ORDER BY username ASC
 ) AS tmp
 WHERE 1
 AND (tmp.acctype = '0' OR tmp.acctype = '2' OR tmp.acctype = '6' )
 AND tmp.enableuser = 1 AND
 (limitdl = 0 OR IF (limitdl =1, downlimit -
 (SELECT COALESCE(SUM(acctoutputoctets), 0)
 FROM radacct WHERE radacct.username = tmp.username) -
 (SELECT COALESCE(SUM(dlbytes), 0)
 FROM rm_radacct WHERE rm_radacct.username = tmp.username) , 1) > 0)
 AND
 (limitul = 0 OR IF (limitul =1, uplimit -
 (SELECT COALESCE(SUM(acctinputoctets), 0)
 FROM radacct WHERE radacct.username = tmp.username) -
 (SELECT COALESCE(SUM(ulbytes ), 0)
 FROM rm_radacct WHERE rm_radacct.username = tmp.username) , 1) > 0)
 AND
 (limitcomb = 0 OR IF (limitcomb =1, comblimit -
 (SELECT COALESCE(SUM(acctinputoctets + acctoutputoctets), 0)
 FROM radacct WHERE radacct.username = tmp.username) +
 (SELECT COALESCE(SUM(ulbytes + dlbytes), 0)
 FROM rm_radacct WHERE rm_radacct.username = tmp.username) , 1) > 0)
 AND
 (limituptime = 0 OR IF (limituptime=1, uptimelimit -
 (SELECT COALESCE(SUM(acctsessiontime), 0)
 FROM radacct WHERE radacct.username = tmp.username) - (SELECT COALESCE(SUM(acctsessiontime), 0)
 FROM rm_radacct WHERE rm_radacct.username = tmp.username) , 1) > 0)
 AND
 (limitexpiration = 0 OR IF (limitexpiration=1, UNIX_TIMESTAMP(expiration) - UNIX_TIMESTAMP(NOW()), 1) > 0);" | awk '{print $1}' |awk 'NR > 1 { print }' > /tmp/tempuser.txt

# REMOVE user which donot have any COMBLIMIT
num=0
cat /tmp/tempuser.txt | while read users
do
num=$[$num+1]
USERID=`echo $users | awk '{print $1}'`
SRVID=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvid FROM radius.rm_users WHERE rm_users.username = '$USERID';" |awk 'FNR == 2 {print $1}'`
COMBLIMITCHECK=`mysql -uroot -p$SQLPASS -e "use radius; SELECT limitcomb FROM rm_services WHERE srvid = '$SRVID';" |awk 'FNR == 2 {print $1}'`
if [[ $COMBLIMITCHECK -eq "1" ]]; then
echo "" > /dev/null
#echo "$USERID have Quota limit = 1 , moving to correct file"
echo "$USERID" >> /tmp/username.txt
else
echo "" > /dev/null
#sed -i 's/\<$USERID\>//g' /tmp/username.txt
fi

done

# Check if username.txt is empty , maybe no user is applicable to show or email have already been sent to them. so they will not appear,
# Echo this info for admin info purposes.
if [ -s /tmp/username.txt ]; then
echo "" > /dev/null
else
echo "Maybe no user is applicable to show or email have already been sent to them. so they will not appear"
fi

# Apply Loop formula throught the rest of script / zaib
num=0
cat /tmp/username.txt | while read users
do
num=$[$num+1]
USERID=`echo $users | awk '{print $1}'`

# Check if time is in between 00:00 till 00:10 , if YES, then maek qmail flag set to 0 so that email can be sent again. Clever ūüėČ . ZAIB
#CURHM=`date +%H:%M`
#start="00:00"
#end="00:10"
#if [[ "$CURHM" > "$start" && "$CURHM" < "$end" ]]; then
#echo "Time matches to reset FLAGS on qmail flag set to zero ...."
#mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET qmail = 0 WHERE username = '$USERID';"
#mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET qmailtime = '0000-00-00 00:00:00' WHERE username = '$USERID';"
#fi

TODAY=$(date +"%Y-%m-%d")
TODAYTIME=$(date +"%Y-%m-%d %T")
TOMORROW=`date --date='tomorrow' +%Y-%m-%d`

# CHECK IF DATE IS CHANGED then CLEAR THE QMAIL FLAGS, otherwise ignore and continue
LASTDEXEC=`cat /etc/lastupdate.txt`
if [ "$TODAY" != "$LASTDEXEC" ]; then
echo "ALERT: Date changed. clearing the flags .... "
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET qmail = 0 WHERE username = '$USERID';"
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET qmailtime = '0000-00-00 00:00:00' WHERE username = '$USERID';"
fi

#ZZZZZAIB
QMAILTIME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT qmailtime FROM rm_users WHERE username = '$USERID';" |awk 'FNR == 2 {print $1,$2}'`
#echo "$USERID vs $QMAILTIME vs $TODAY"
#if [[ $QMAILTIME -eq $TODAY ]]; then
#echo "SMS have already sent to $USERID for $TODAY !"
#else
#echo "" > /dev/null
#fi

SRVID=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvid FROM radius.rm_users WHERE rm_users.username = '$USERID';" |awk 'FNR == 2 {print $1}'`
SRVNAME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvname FROM radius.rm_services WHERE rm_services.srvid = '$SRVID';" |awk 'FNR == 2'`

NEXTSRVID=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT dailynextsrvid FROM radius.rm_services WHERE srvid = '$SRVID';" |awk 'FNR == 2 {print $1}'`
NEXTSRVIDNAME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvname FROM radius.rm_services WHERE rm_services.srvid = '$NEXTSRVID';" |awk 'FNR == 2'`

COMBQUOTA=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT combquota FROM radius.rm_services WHERE srvid = '$SRVID';" |awk 'FNR == 2 {print $1}'`
QMAIL=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT qmail FROM radius.rm_users WHERE rm_users.username = '$USERID';" |awk 'FNR == 2 {print $1}'`
EXPIRY=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT expiration FROM radius.rm_users WHERE rm_users.username = '$USERID';" |awk 'FNR == 2 {print $1}'`

# Query Today Download Dynamically
TODAYDL=`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 '$USERID' AND radacct.acctstarttime > '$TODAY' AND radacct.acctstarttime < '$TOMORROW' AND
FramedIPAddress LIKE '%' AND CallingStationId LIKE '%'
GROUP BY radacct.radacctid
) AS tmp GROUP BY date LIMIT 0, 50;" |sed '1d' | awk '{ print $2 + $3 }'`

# If user Download is Empty or Zero, set fake value of 111 so that percentage formula maynot make issues
if [ ! -z "$TODAYDL" ];
then
#TODAYDL="1000"
echo ""
else
echo ""
#No quota is used TODAY so using FAKE zero value so percentage value will not give errors."
TODAYDL="111"
fi

# If downloaded data percentage is above then 70% then do action

PERCENTUSED=$((100*$TODAYDL/$COMBQUOTA))

#if [[ $PERCENTUSED -gt 70 ]]
if [ "$PERCENTUSED" -gt $QUOTAPERCLIMIT ]
then

echo "
-----------------------------------------------
ID = $USERID
QUOTA ALERT = $PERCENTUSED %
SRVID = $SRVID
NAME = $SRVNAME
NEXT DAILY SERVICE = $NEXTSRVIDNAME
TODAY DONWLOAD BYTES = $TODAYDL
QUOTA LIMIT IN BYTES = $COMBQUOTA"
echo "QUOTA ALLOWED = $(($COMBQUOTA / 1024 / 1024))" MB
DLINMB=`echo "$TODAYDL/1024/1024" | bc`
echo "Today Downloaded = $DLINMB MB"

else
# Otherwise just ECHO, do nothing
echo "
-----------------------------------------------
ID = $USERID
QUOTA = OK, NOT USED / $PERCENTUSED %
NAME = $SRVNAME
Next Daily Service = $NEXTSRVIDNAME"
if [ "$TODAYDL" -eq 111 ];
then
echo "TODAYDL is empty so using fake value"
fi
#TODAYDL="1000"
#echo "NEW VALUE is $TODAYDL"
#else
#TODAYDL="1000"
#fi
echo "TODAY DONWLOADED BYTES = $TODAYDL
QUOTA LIMIT IN BYTES = $COMBQUOTA"
echo "QUOTA ALLOWED = $(($COMBQUOTA / 1024 / 1024))" MB
#echo "$TODAYDL/1024/1024" | bc
fi

# check if near quota users have already sent email, if fetched value is 1, then do nothing
# else send email and update QMAIL flag in rm_users table
########## SENDGIN EMAIL
if [[ $PERCENTUSED -gt $QUOTAPERCLIMIT && $QMAIL -eq 1 ]]; then
echo "INFO: $USERID have consumed 70% or above quota and SMS have alreay been sent on $QMAILTIME
-----------------------------------------------"
fi

if [[ $PERCENTUSED -gt $QUOTAPERCLIMIT && $QMAIL -eq 0 ]]
then
echo "Sending SMS Alert info to $USERID for Quota Alert ..."

# Setting Variables for sending email and fetch other data
DAILYLIMITINMB=`echo "$COMBQUOTA/1024/1024" | bc`
MOBILE=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT mobile FROM radius.rm_users WHERE rm_users.username = '$USERID';" |awk 'FNR == 2 {print $1}'`
FIRSTNAME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT firstname FROM radius.rm_users WHERE rm_users.username = '$USERID';" |awk 'FNR == 2 {print $1}'`
LASTNAME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT lastname FROM radius.rm_users WHERE rm_users.username = '$USERID';" |awk 'FNR == 2 {print $1}'`

# Echo for Screen Print
echo "Dear $FIRSTNAME $LASTNAME,
Your internet account ID $USERID have consumed $QUOTAPERCLIMIT% of daily allowed quota that is $DAILYLIMITINMB MB. After this your speed will be reduced to $NEXTSRVIDNAME for current date.
After current date change, You will be reverted back to $SRVNAME.
Your account expiration date is $EXPIRY.

Regard's
Syed Jahanzaib"

# Echo to save data inf ile which will be used later by KANNEL to send properly formatted message.

echo "Dear $FIRSTNAME $LASTNAME,
Your internet account ID $USERID have consumed $QUOTAPERCLIMIT% of daily allowed quota that is $DAILYLIMITINMB MB. After this your speed will be reduced to $NEXTSRVIDNAME for current date.
After current date change, You will be reverted back to $SRVNAME.
Your account expiration date is $EXPIRY.

Regard's
Syed Jahanzaib" > /tmp/$USERID.sms

# Finally SENDING SMS using KANNEL SMS GATEWAY, you can use other functions as well : D ~
curl "http://$KHOST:13013/cgi-bin/sendsms?username=$KID&password=$KPASS&to=$MOBILE" -G --data-urlencode text@/tmp/$USERID.sms

# Update mysql QMAIL flag so that system should not repeat sending emails
# Make sure you run another script that should change the QMAIL flag to 0 after data cahnges
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET qmail = 1 WHERE username = '$USERID';"
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET qmailtime = '$TODAYTIME' WHERE username = '$USERID';"
fi
done

echo "$TODAY" > /etc/lastupdate.txt

tables


 

Older Posts »

Blog at WordPress.com.

%d bloggers like this: