Syed Jahanzaib Personal Blog to Share Knowledge !

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

Mikrotik: Using Firewall Filters to Acquire Wan Data Usage via Email

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

monitoring_report


Requirements:

An Operator [from Amsterdam] wanted to receive email on daily basis for the Daily usage of WAN link on the Mikrotik. Something like Mikrotik should send simple reporting email on daily basis like ‘XXX MB were downloaded Yesterday via WAN link’.

Solution:

Use following Script. All sections are well defined in it. Make sure you read it carefully line by line, and modify items required.

In this example I used FILTER rules approach to collect the bytes. Surely there are more elegant ways to fetch the data like … Linux base MRTG, NMS like DUDE, above all SNMP  and many others. I just used this method. You may select other as it suites you. This one worked neatly for me 🙂

You can customize it as per your requirements, like multi wan report, monthly report etc.


Requirements:

Mikrotik 6.x

  1. Schedule this script to run after 5 (or xx) minutes. When DATE change occurs, it will reset the counter file and filter rules counters.
  2. Create two FILTER rules in Mikrotik as following. SFP1 is the wan interface, so do change it accordingly.
/ip firewall filter
add action=passthrough chain=forward comment=WAN_1_IN in-interface=sfp1
add action=passthrough chain=forward comment=WAN_1_OUT out-interface=sfp1

the Script !


# Script to collect WAN DATA USAGE by FILTER rules, and send data to admin by Email Daily.
# And reset the counters on daily basis in night. it will preserve the data in a file even if the router reboots.
# Syed Jahanzaib / aacable @ hotmail . com
# https://aacable.wordpress.com
# 23-MAY-2016

# Make sure you add two firewall rules as showed below so that script can take bytes from it and reset it when date changes.
# Change the interface name accordingly, and make sure to enter the matching comments too.
# /ip firewall filter
#add action=passthrough chain=forward comment=WAN_1_IN in-interface=sfp1
#add action=passthrough chain=forward comment=WAN_1_OUT out-interface=sfp1

# Set comments for firewall filter rules, change them as required
:local WAN1INCOMMENT
:local WAN1OUTCOMMENT
:local WAN1INCOMMENT "WAN_1_IN"
:local WAN1OUTCOMMENT "WAN_1_OUT"

:local BYTESOUT [/ip firewall filter get [/ip firewall filter find comment="$WAN1INCOMMENT"] bytes]
:local BYTESIN [/ip firewall filter get [/ip firewall filter find comment="$WAN1OUTCOMMENT"] bytes]

# SET GMAIL for sending email, make sure you have configured /TOOLS,EMAIL option of mikrotik. and test it before using following.
:global gmailsmtp
:set gmailsmtp [:resolve "smtp.gmail.com"];
# Set your GMAIL Account Password
:local gmailpass
:set gmailpass GMAIL-PASSWORD
# Set your email where you want to receive the alert
:local mailsendto
:set mailsendto YOUR-ADMIN-EMAIL@xxxx.com

# set DATE TIME
:local date
:local time
:set date [/system clock get date];
:set time [/system clock get time];

# Create file (if file is not already there.) to update date time of last update
:if ([:len [/file find where name=counterslastupdate.txt ]] < 1 ) do={
/file print file=counterslastupdate.txt where name=counterslastupdate.txt
/file set counterslastupdate.txt contents="0";
};

# Create file (if file is not already there.) to store last update date time in normal format to be showed in email.
:if ([:len [/file find where name=counterslastupdatenormalformat.txt ]] < 1 ) do={
/file print file=counterslastupdatenormalformat where name=counterslastupdatenormalformat.txt
/file set counterslastupdatenormalformat contents="0";
};

# Setting variables
:local curDate [/system clock get date]
:local curYear [:pick $curDate 7 13]
:local curMon [ :pick $curDate 0 3 ]
:local curDay [:pick $curDate 4 6]
:local COMPANY "JZ"
:local CURRENTDATE "$curDay$curYear"
:local LASTUPDATEDATE value=[/file get counterslastupdate.txt contents]
:local LASTUPDATEDATENORMAL value=[/file get counterslastupdatenormalformat.txt contents]

# Update counters last update with current date time
/file set counterslastupdate.txt contents=$CURRENTDATE

# Calculate data in MB to be displayed in LOG and email
:local TOTAL
:set $TOTAL ($BYTESOUT+$BYTESIN)
:local TOTALMB
:set $TOTALMB ($TOTAL / 1024 / 1024)
#:log info ( "Traffic out = " . $BYTESOUT . " bytes" )
#:log info ( "Traffic in = " . $BYTESIN . " bytes" )
#:log warning ( "TOTAL TRAFFIC = " . $TOTAL. " bytes" )
:log warning "$TOTALMB MB Downloaded iva WAN link on $curDate"

# If date is changed (usually in night) , then send email using GMAIL , with the Data
:if ($CURRENTDATE = $LASTUPDATEDATE) do={
:log warning "No need to send email."
} else {
:log warning "DATE changed, sending email for last day data usage and also reset the Firewall Counters ..."
# Reset the firewall counters and counter files if date change is detected / zaib
/ip firewall filter reset-counters [find comment=$WAN1INCOMMENT ]
/ip firewall filter reset-counters [find comment=$WAN1OUTCOMMENT ]
/file set counter.txt contents="0";

# Set Email Subject
:local es "$[/system identity get name] $[/system clock get date] $[/system clock get time] $COMPANY MIKROTIK / $TOTALMB MB were downloaded via WAN link on $LASTUPDATEDATENORMAL"
# Set Email Body
:local eb "$[/system identity get name] $[/system clock get date] $[/system clock get time] $COMPANY MIKROTIK / $TOTALMB MB were downloaded via WAN link on $LASTUPDATEDATENORMAL"
# Finally send email
/tool e-mail send to=$mailsendto subject=$es body=$eb start-tls=yes
};

# Create file (if file is not already there.) to update download bytes
:if ([:len [/file find where name=counter.txt]] < 1 ) do={
/file print file=counter.txt where name=counter.txt;
/delay delay-time=1;
/file set counter.txt contents="0";
};

# If current value is bigger then older, then update the counters,
# Helpfule to save counters, when router reboots.

# Get value from stored data for matching
:local before value=[/file get counter.txt contents]

:if ($TOTAL > $before) do={
/file set counter.txt contents=$TOTAL
} else= {
# Else update both values in the file
:set $TOTAL ($TOTAL+$before)
/file set counter.txt contents=$TOTAL
};

# Update Date time stamp in both files / zaib
/file set counterslastupdate.txt contents=$CURRENTDATE
/file set counterslastupdatenormalformat.txt contents=$curDate

# Regard's
# Syed Jahanzaib


End Results !

downlaoded

 

 

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


 

May 11, 2016

Sending ‘Password Change’ Alert to users via SMS/Email through KANNEL SMS GATEWAY in Radius Manager

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

1234

Screenshot_2016-05-11-14-44-07


Following is a quick dirty method on how you can generate SMS / EMAIL alert when admin changes any user password [as requested by an OP]. and I think its a good idea so that users must be informed about there account password security.

In this guide I have used Radius Manager 4.1.5 along with KANNEL on same machine. Serial Modem is being used along with local mobile SIM for sending SMS.

You need to perform few steps. Proceed with caution, as alerting mysql DB incorrectly can result in partial or total database wipe out or can led to corruption of DB. Make sure you take full DB backup before proceeding. Better to test it on Virtual lab.

you need to make two .sql file

1- triggers.sql
[It will make a new trigger that will be executed when rm_users table will be modified. It will match new password field with the old.field and add then log the changed with username and other details in below table.

2-rm_userpasschangehistory.sql
[It will create new DB which will store password change datetime, username, first last name and mobile]


1- TRIGGERS.SQL

Ok lets first make triggers.sql file, open text editor and paste the data.

mkdir /temp
nano /temp/triggers.sql

Paste the following data in this file.

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

DELIMITER ;;

FOR EACH ROW BEGIN
IF NEW.password <> OLD.password THEN
INSERT INTO rm_userpasschangehistory (datetime, username, firstname, lastname, mobile) VALUES (NOW(), new.username, new.firstname, new.lastname, new.mobile);
END IF;
END */;;
DELIMITER ;

-- Dumping routines for database 'radius'
--

Save and exit.


2- rm_userpasschangehistory

Now let’s make rm_userpasschangehistory.sql , open text editor and paste the data.

mkdir /temp
nano /temp/rm_userpasschangehistory.sql

Paste the following data in this file.

-- Table structure for table rm_userpasschangehistory`
--

DROP TABLE IF EXISTS rm_userpasschangehistory`;
CREATE TABLE `rm_userpasschangehistory` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`datetime` datetime NOT NULL,
`username` varchar(64) NOT NULL,
`firstname` varchar(64) NOT NULL,
`lastname` varchar(64) NOT NULL,
`mobile` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
ALTER TABLE `rm_users` ADD `ModifiedTime` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
--
-- Dumping data for table rm_userpasschangehistory

Save and exit.


IMPORTING .sql files intro RADIUS DB.

Now we can import above created .sql files into radius DB. Use below commands

mysql -uroot -pSQLPASS radius < triggers.sql

mysql -uroot -pSQLPASS radius < rm_userpasschangehistory.sql

TEST DRIVER ….

Ok now try to change any user password from radius admin panel. Once updated, check the new table cahnges by following command (I used time interval to fetch accounts changed in last 5 minutes, you can modify it as per your requirements

.. and you may see result as below …


mysql -uroot -pSQLPASS --skip-column-names -e "use radius; select * from rm_userpasschangehistory WHERE datetime >= NOW() - INTERVAL 5 MINUTE;"
+---+---------------------+------+------+-----------+-------------+
| 5 | 2016-05-11 13:46:55 | zaib | syed | jahanzaib | 03333021909 |
+---+---------------------+------+------+-----------+-------------+

~ ALHAMDOLILLAH ~


SCRIPT to fetch data via SCHEDULED CRON  job to send SMS/EMAIL.

You can use following script in cron scheduler.


#!/bin/sh
# passchange.sh
# Bash script which will run after every 5 minutes and will fetch info from mysqltable
# and will send SMS/Email alert for password change event.
# Created by SYED JAHANZAIB
# aacable@hotmail.com
# https://aacable.wordpress.com

SLQPASS="MYSQL_ROOT_PASSWORD"
TMPUSRINFO=/tmp/userpass.txt
mysql -uroot -p$SQLPASS --skip-column-names -e "use radius; select * from rm_userpasschangehistory WHERE datetime >= NOW() - INTERVAL 5 MINUTE;" >> $TMPUSRINFO

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

# 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 $5}'`
lastname=`echo $users | awk '{print $6}'`
mobile=`echo $users | awk '{print $7}'`
date=`echo $users | awk '{print $2,$3}'`
# Print Info on screen
echo "Dear $firstname $lastname,
Password for your internet account ID=$username been successfully changed on $date.
Regard's

XYZ ISP SERVICES (PVT) LTD"

# Store Info for sending SMS in /tmp folder where we will call kannel to send customized SMS
echo "Dear $firstname $lastname,
Password for your internet account ID=$username been successfully changed on $date.

Regard's
XYZ ISP SERVICES (PVT) LTD" > /tmp/$username.passchange.sms

curl "http://$KHOST/cgi-bin/sendsms?username=$KID&password=$KPASS&to=$mobile" -G --data-urlencode text@/tmp/$username.passchange.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

sms done


CRON CODE for 5 minute schedule.

crontab -e

# Run renewal check script Script after every 5 minutes
*/5 * * * * /temp/passchange.sh

Possibilities are endless…..

Regard’s
Syed Jahanzaib

May 9, 2016

Radius Manager Table Missing Errors

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

Error # 1

Unknown column ‘pm_sagepay’ in ‘field list’

After install or upgrade radius manager, you may see following error when you click on Home / Settings

 

123

It is caused by in-correct table name pm_netcash where as RM searches for `pm_ sagepay`. Issue following command to solve it.

Login to mysql, and change db to radius.

mysql -uroot -pSQLPASS
use radius;
ALTER TABLE `rm_settings`  CHANGE `pm_netcash` `pm_sagepay` TINYINT( 1 ) NOT NULL ;"

Make sure to change mysql password. This will alter the in.correct table name to correct one and then you will be able to access the menu correctly.


Error # 2

Unknown column in ‘pm_netcash’ in ‘field list’

[Updated 27th-Feb-2017]

If you see following error

pm_netcash-error-in-radius-manager

Then follow this …

login to mysql, change db to radius, and alter the table. full commands are as below …

mysql -u root -pYOUR_MYSQL_PASSWORD
use radius;
ALTER TABLE `rm_settings` ADD `pm_netcash` INT NOT NULL;
exit

Done. Now refresh ACP page in browser and all will be fine Insha Allah.

 

Regard’s
Syed Jahanzaib

 

Create a free website or blog at WordPress.com.

%d bloggers like this: