Syed Jahanzaib Personal Blog to Share Knowledge !

September 7, 2018

COA with Radclient workaround for RM 4.1 with Mikrotik 6.4x

Filed under: freeradius, Mikrotik Related — Tags: , — Syed Jahanzaib / Pinochio~:) @ 1:12 PM

dealing-with-dynamic-change-2

Scenario:

  • Dmasoftlab Radius Manager 4.1 with multiple services. Some of services have dynamic dynamic bandwidth scheduling for day & night. Example some services have double up mode for day , some for evening, and some for night.
  • Mikrotik 6.42.7 server with hotspot or pppoe authentication services for LAN users

Problem:

DMA Radius Manager 4.1 ‘s API functionality is broken for Mikrotik RouterOS newer versions. The 4.1 code is relying on modifying dynamic queues which had worked on 5.x version (& in some 6.2x series as well e.g: v6.29) . Any circumstances where that was doable were bugs that MikroTik has since fixed. And relying on bugs is generally a bad practice. This can be solved by using CoA instead of modifying dynamic queues which I have used in this post.

It is highly recommended that you must upgrade radius manager to latest 4.2 version which works good with new ROS.


Workaround for RM 4.1:

If for some reasons you want to stick with 4.1 version for whatsoever reason, example 4.2 version have some strict licensing policies so if still wants to use ROS latest series like 6.42.7 (as of writing this post)  , , & if you still wants to avail dynamic bandwidth changes on the fly for particular services , you can schedule following script which will run on hourly basis and will send bandwidth change request to mikrotik according to the service time.



Limitations of the Script:

  • This is a lab testing version of the script. You must modify and tune it for production use. Example the script is doing lots of sql queries, you can minimize it by creating single combined query to fetch all data from the tables, and then read values in next cmd from local file which will be much faster then querying from MySQL.
  • The service must have single time schedule. example from 08:00:00 to 20:00:00 , Multiple times for single service is not supported.
  • The time must consists of single day, it cannot overlap to next day,
  • Script will run as per cron schedule , despite you have selected specific days or not.
  • In lab I have configured it to run every hour , It will query services and its associated users. If the Start time matches , it will send bandwidth change request to the NAS, and if end time matches it will send user original package values to NAS. You can overcome repeating issue by adding additional column in the respective table and update it every time script runs which will check if it have already sent or not.
  • You should disable echoing the outputs, it will save some resources.

the Scheduler!

Either use @hourly in CRONTAB or make separate file under /etc/crond

Create new file name bw in /etc/crond/ with following contents

touch /etc/cron.d/bw
nano /etc/cron.d/bw

& add following line in it,

0 * * * * root /temp/bw.sh

Save & Exit …


the Script!

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

& add following …

#!/bin/bash
# Following script is made specifically for Dmasoftlab radius manager 4.1.x for New Mikrotik 6.3x.x+ , 6.4x.x
# It will check "rm_specperbw" table, and if found any service entry,
# It will query that service and make list of users attached to this service,
# Then it will query next package , start/end time, and will perform actions accordingly
# Syed Jahanzaib
# Created: 6-SEP-2018
# Last Modified : 1-NOV-2018
# Tested on Ubuntu OS Only
#set -x
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
#################
# CHANGE these
SQLID="root"
SQLPASS="MYSQL_ROOT_PASS"
NAS_COA_PORT="1700"
DB="radius"
SRV="mysql"
#################

#DATE TIME FUNCTIONS
currenttime=$(date +%H:%M:%S)
# Add Script start execution entry in the /var/log/syslog to see if the script got executed or not
logger "Bandwidth poller Last executed @ $currenttime by the CRON scheduler ... Powered by SYED.JAHANZAIB"
echo "- Script Start Time - $currenttime
"
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLID --skip-column-names -s -e"
#Table which contain main users information
USER_TABLE="rm_users"
SRV_BW_DB="rm_services"
#Table which contains service name id which will be scanned for user and packages
DYN_BW_TABLE="rm_specperbw"
USER_SERVICE_TABLE="rm_services"
# Temp file where services/users list will be saved
TMP1="/tmp/bwsch_srv.txt"
TMP2="/tmp/bwsch_users.txt"
TMP3="/tmp/bwsch_users_final.txt"
RADCLIENT="/usr/local/bin/radclient"
> $TMP1
> $TMP2
> $TMP3
# Check if $SRV (in this case mysql) is running or not, if NOT, then exit the script
SRVSTATUS=$(pgrep $SRV | wc -l);
if [ "$SRVSTATUS" -ne 1 ];
then
echo "- $SRV service is down. Pleasec check your $srv service first.
- Exiting ...";
exit 1
else
echo "- INFO: $SRV service is accessible. Proceeding further ... OK"
fi
# Check if DB (in this case radius ) is accessible or not, if NOT, then exit the script
RESULT=`$CMD "SHOW DATABASES LIKE '$DB'"`
if [ "$RESULT" == "$DB" ]; then
echo "- INFO: $DB database exist. Proceeding further ... OK"
else
echo "- ERROR: $DB database does not exist! Sending EXIT signals ..."
exit 1
fi

# Look for services that have Dynamic bandwidth change (and remove duplicate entries as well becasue of multipel time definitiosn ins ingle service)
$CMD "use $DB; select srvid from $DYN_BW_TABLE" | sort -u >> $TMP1
TOTSRV=`cat $TMP1 | wc -l`
echo "- INFO: Total number of services with Dynamic bandwidth enabled = $TOTSRV / No.s ... OK"
if [ ! -s $TMP1 ]
then
endtime=$(date +%H:%M:%S)
echo "- WARNING: No SERVICES found to check for bandwdith changing in $DYN_BW_TABLE
- Script Ends Here

- EXITING peacefully...
- Script End Time - $endtime
"
exit 1
fi

# If required service found then look for Users
num=0
cat $TMP1 | while read srvid
do
num=$[$num+1]
SRVID=`echo $srvid |awk '{print $1}'`
$CMD "use $DB; select username from $USER_TABLE where srvid ='$SRVID';" >> $TMP2
done
TOTUSR=`cat $TMP2 | wc -l`
echo "- INFO: Total number of users with Dynamic bandwidth enabled = $TOTUSR / No.s ..."

# Remove duplicate users , If any (it was dueto the fact if the service have multiple time defined)
if [ ! -s $TMP2 ]
then
endtime=$(date +%H:%M:%S)
echo "- WARNING: No User found for bandwidth upgrade in DMA RADIUS MANAGER TABLE '$DYN_BW_TABLE' , Sending EXIT signals ...

- Script Ends Here...
- EXITING peacefully...
- Script End Time - $endtime
"
exit 1
fi

# Run loop forumla to run CMD for single or multi usernames
echo "- INFO: Checking for Dynamic Bandwidth Policies and implemnt change on the fly for online users , if any ...

"
num=0
cat $TMP2 | while read users
do
num=$[$num+1]
USERNAME=`echo $users |awk '{print $1}'`
SRVID=`$CMD "use $DB; select srvid from $USER_TABLE where username ='$USERNAME';"`
DN_ST=`$CMD "use $DB; select starttime from $DYN_BW_TABLE where srvid ='$SRVID';" |awk 'FNR == 1'`
DN_ET=`$CMD "use $DB; select endtime from $DYN_BW_TABLE where srvid ='$SRVID';" |awk 'FNR == 1'`

#######################
##### UP-GRADE SECTION
#######################
if [[ "$currenttime" > "$DN_ST" ]] && [[ "$currenttime" < "$DN_ET" ]]; then
#If time matches then take upgrade action
# If user is Online UPGRADE its package
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
if [ ! -z "$ACCTSESID" ]; then
NAS_IP=`$CMD "use $DB; select nasipaddress from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
NAS_SECRET=`$CMD "use $DB; select secret from nas where nasname = '$NAS_IP' ;"`
USER_IP=`$CMD "use $DB; select framedipaddress from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
dlrate_c=`$CMD "use $DB; select dlrate from $DYN_BW_TABLE where srvid ='$SRVID';" |awk 'FNR == 1'`
ulrate_c=`$CMD "use $DB; select ulrate from $DYN_BW_TABLE where srvid ='$SRVID';"|awk 'FNR == 1'`
ulrate=$(echo $(( $ulrate_c / 1024 )))k
dlrate=$(echo $(( $dlrate_c / 1024 )))k
DN_BWPKG="$ulrate/$dlrate"
echo "- UPGRADE ***** - $USERNAME / $USER_IP / $ACCTSESID is online, and eligible for package UPGRADE to new package $DN_BWPKG @ $currenttime ..."
#for pppoe, enable following line
echo User-Name=$USERNAME,Acct-Session-Id=$ACCTSESID,Framed-IP-Address=$USER_IP,Mikrotik-Rate-Limit=\"$DN_BWPKG\" | $RADCLIENT -q -x $NAS_IP:$NAS_COA_PORT coa $NAS_SECRET
#for hotspot, enable following line
#echo Framed-IP-Address=$USER_IP,Mikrotik-Rate-Limit=\"$DN_BWPKG\" | radclient -q -x $NAS_IP:$NAS_COA_PORT coa $NAS_SECRET
if [ -z "$ACCTSESID" ]; then
echo "- INFO: UPGRADE ***** - $USERNAME is eligible for package UPGRADE to new package $DN_BWPKG @ $currenttime BUT NOT ONLINE , no need to take action ..."
fi
fi
else
#######################
##### DOWNGRADE SECTION
#######################
# If package DOWNgrade time is matched in services & packages have not changed already, then do it now - zaib
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
if [ ! -z "$ACCTSESID" ]; then
NAS_IP=`$CMD "use $DB; select nasipaddress from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
NAS_SECRET=`$CMD "use $DB; select secret from nas where nasname = '$NAS_IP' ;"`
USER_IP=`$CMD "use $DB; select framedipaddress from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
dlrate_c=`$CMD "use $DB; select downrate from $USER_SERVICE_TABLE where srvid ='$SRVID';" |awk 'FNR == 1'`
ulrate_c=`$CMD "use $DB; select uprate from $USER_SERVICE_TABLE where srvid ='$SRVID';"|awk 'FNR == 1'`
ulrate=$(echo $(( $ulrate_c / 1024 )))k
dlrate=$(echo $(( $dlrate_c / 1024 )))k
DN_BWPKG="$ulrate/$dlrate"
echo "- INFO: DOWNGRADE ***** - $USERNAME / $USER_IP / $ACCTSESID is online, and eligible for package DOWNGRADE to old package $DN_BWPKG @ $currenttime ..."
#for pppoe, enable following line
echo User-Name=$USERNAME,Acct-Session-Id=$ACCTSESID,Framed-IP-Address=$USER_IP,Mikrotik-Rate-Limit=\"$DN_BWPKG\" | $RADCLIENT -q -x $NAS_IP:$NAS_COA_PORT coa $NAS_SECRET
#for hotspot, enable following line
#echo Framed-IP-Address=$USER_IP,Mikrotik-Rate-Limit=\"$DN_BWPKG\" | radclient -q -x $NAS_IP:$NAS_COA_PORT coa $NAS_SECRET
if [ -z "$ACCTSESID" ]; then
echo "- INFO: DOWNGRADE ***** - $USERNAME is eligible for package DOWNGRADE to OLD package $DN_BWPKG @ $currenttime BUT NOT ONLINE , no need to take action ..."
fi
fi
fi
done
endtime=$(date +%H:%M:%S)
echo "
- Script Ends Here
- EXITING peacefully ...
- Script End Time - $endtime
"
# Add Script end execution entry in the /var/log/syslog to see if the script got ended
currenttime=$(date +%H:%M:%S)
logger "Bandwidth poller script ended @ $currenttime ... Powered by SYED.JAHANZAIB"
# Script for dma 4.1.x COA for new Mikrotik 6.3+ - Designed by Syed Jahanzaib

Results:

radius bw poller result.PNG

 

Regard’s
Syed Jahanzaib

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: