Syed Jahanzaib Personal Blog to Share Knowledge !

September 7, 2018

COA with Radclient workaround for RM 4.1 with Mikrotik 6.4x.x

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, and some other things that we cannot mention here 😉  & 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 17:59:59 , Multiple times for single service is not supported.
  • Make sure to define END time in xx:59:59 format, EXAMPLE: Start from 00:00:00 , ENDS at 17:59:59
  • 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 Script!

#!/bin/bash
# Following script is made specifically for Dmasoftlab radius manager 4.1 , 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 : 20-OCT-2018
#set -x

#################
# CHANGE these
SQLID="root"
SQLPASS="SQL_PASSWORD
NAS_COA_PORT="1700"
DB="radius"
SRV="mysql"
#################

#DATE TIME FUNCTIONS
currenttime=$(date +%H:%M:%S)
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"
> $TMP1
> $TMP2

# 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 UPGRAD to new package $DN_BWPKG @ $currenttime ..."
#for pppoe, you should disable following line if you are not using it
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, you should disable following line if you are not using it
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 / $USER_IP / $ACCTSESID is online, and 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, you should disable following line if you are not using it
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, you should disable following line if you are not using it
echo Framed-IP-Address=$USER_IP,Mikrotik-Rate-Limit=\"$DN_BWPKG\" | radclient -q -x $NAS_IP:$NAS_COA_PORT coa $NAS_SECRET
fi
fi
if [ -z "$ACCTSESID" ]; then
echo "- INFO: UPGRADE ***** - $USERNAME / $USER_IP / $ACCTSESID is online, and eligible for package DOWNGRADE to OLD package $DN_BWPKG @ $currenttime BUT NOT ONLINE , no need to take action ..."
fi
done
endtime=$(date +%H:%M:%S)
echo "
- Script Ends Here
- EXITING peacefully ...
- Script End Time - $endtime
"
# Script Designed by Syed Jahanzaib

Regard’s
Syed Jahanzaib

%d bloggers like this: