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
Like this:
Like Loading...
Last Update: 10-April-2019
Syed Jahanzaib - Personal Blog to Share Knowledge ! 
