FREERADIUS WITH MIKROTIK – Part #14 – Dynamic Bandwidth Change on the FLY using COA with radclient

FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s Click here to read more on FR tutorials …

word-press blog is not saving the code properly, so some syntax is missing in the script after every update, so if requires this script then email me.

---

Disclaimer! This is important!

Every Network is different , so one solution cannot be applied to all. Therefore try to understand logic & create your own solution as per your network scenario. Just dont follow copy paste.

If anybody here thinks I am an expert on this stuff, I am NOT certified in anything Mikrotik/Cisco/Linux or Windows. However I have worked with some core networks and I read , research & try stuff all of the time. So I am not speaking/posting about stuff I am formerly trained in, I pretty much go with experience and what I have learned on my own. And , If I don’t know something then I read & learn all about it.

So , please don’t hold me/my-postings to be always 100 percent correct. I make mistakes just like everybody else. However – I do my best, learn from my mistakes and always try to help others.

This particular script was tested in Virtual environment only, therefore consider this posting as an reference only, donot use it in production environment.

Regard's
Syed Jahanzaib~

---

Scenario:

We have a generic FreeRADIUS Version 2.2.8 based billing system in Ubuntu 16.04.3 LTS Server. Users are authenticating to NAS (Mikrotik) which is using Freeradius as its AAA Server.

---

Requirement:

Currently users packages are 1mb,  2mb and so on. We would like to introduce different bandwidth for day and night for specific services. Upgrade/Downgrade of user package should be done by dynamically with COA, so that package changing should be done on the fly without disconnecting user.

Its a bit complicated piece of BASH scripting , but so far doing its job.

---

Software / Hardware Components Used:

• NAS: Mikrotik CCR1036 / Firmware: 6.42.1
• OS: Ubuntu 16.04.3 LTS Server Edition / 64bit
• FreeRADIUS Version: 2.2.8 (using apt-get default repository)

---

SERVICES Table!

/*!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 utf8mb4 */;

-- Database: `radius`
-- Table structure for table `services`
CREATE TABLE `services` (
`srvid` int(10) NOT NULL,
`srvname` varchar(128) NOT NULL,
`descr` varchar(128) DEFAULT NULL,
`enabled` varchar(1) NOT NULL DEFAULT '1',
`expdays` int(4) NOT NULL DEFAULT '0',
`dlimit` varchar(32) NOT NULL DEFAULT '0',
`ulimit` varchar(32) NOT NULL DEFAULT '0',
`qt_enabled` tinyint(1) NOT NULL,
`tot_qt` int(32) NOT NULL DEFAULT '0',
`free_quota_enabled` tinyint(1) NOT NULL DEFAULT '0',
`free_qt_start_time` time NOT NULL,
`free_qt_end_time` time NOT NULL,
`dyn_bw_change` int(1) NOT NULL DEFAULT '0',
`dn_bwpkg` varchar(16) NOT NULL DEFAULT '0',
`dn_st_time` time NOT NULL,
`dn_et_time` time NOT NULL,
`dn_st_exec` int(1) NOT NULL DEFAULT '0',
`dn_et_exec` int(1) NOT NULL DEFAULT '0',
`ippool` varchar(64) NOT NULL,
`createdon` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Dumping data for table `services`

INSERT INTO `services` (`srvid`, `srvname`, `descr`, `enabled`, `expdays`, `dlimit`, `ulimit`, `qt_enabled`, `tot_qt`, `free_quota_enabled`, `free_qt_start_time`, `free_qt_end_time`, `dyn_bw_change`, `dn_bwpkg`, `dn_st_time`, `dn_et_time`, `dn_st_exec`, `dn_et_exec`, `ippool`, `createdon`) VALUES
(9, '1mb', '1mb 30 days expity', '1', 30, '1024k', '1024k', 1, 1024, 1, '00:00:00', '00:00:00', 1, '2048k/2048k', '13:00:00', '23:00:00', 0, 1, 'public-pool', '2018-06-27 12:13:04');

-- Indexes for dumped tables
-- Indexes for table `services`
ALTER TABLE `services`
ADD PRIMARY KEY (`srvid`),
ADD KEY `nasname` (`srvname`);

-- AUTO_INCREMENT for dumped tables
-- AUTO_INCREMENT for table `services`
ALTER TABLE `services`
MODIFY `srvid` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
/*!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 */;

---

the Script !

Following is bash script which will query different tables and take action according to the results. I scheduled to run on a hourly (or decide as per network load).

At one live network with more then 3500+ users online, this script took around 5 minutes change all the online users bandwidth on different NASES by sending COA, also it changed the group.

Script-1 [/temp/dn_bw.sh > Sample]

#!/bin/bash
#set -x
# Following script will check services table, and if found any servic entry (ID),
# It will query that service and make list of users attached to this service,
# Then it will query next pacakge , start/end time, and will perform actions accordingly
# Created by Syed Jahanzaib - https://aacable . wordpress . com / aacable @ hotmail . com
# Created: 25-Jun=2018
# Last Modified: 27-Jun-2018
SQLID="root"
SQLPASS="SQLPASS"
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLID --skip-column-names -s -e"
DB="radius"
#Table which contain main users information
USER_TABLE="users"
#Table which contains users name which will be scanned for quota
SRV_BW_DB="services"
#Log table in which few actions will be logged
USER_TABLE_LOG="log"
#Rad-user group in which we will update user profile like from 1mb to different pacake as per service
GROUP=radgroupreply
# Temp file where user list will be saved
TMP1="/tmp/bwsch_srv.txt"
TMP2="/tmp/bwsch_users.txt"
TMP3="/tmp/tot_users_upgrade_coa_sent.txt"
TMP4="/tmp/tot_users_dngrade_coa_sent.txt"
TMPLOG="/tmp/bwsch_log.txt"
> $TMP1
> $TMP2
> $TMP3
> $TMP4
> $TMPLOG
#DATE TIME FUNCTIONS
currenttime=$(date +%H:%M:%S)
currenttimetrimmed=$(date +%H%M%S)
# Look for services that have Dynamic bandwidth change enabled
$CMD "use $DB; select groupname from services where dyn_bw_change ='1';" |sort >> $TMP1
if [ ! -s $TMP1 ]
then
echo "No SERVICES found to check for bandwdith changing in $SRV_BW_DB , exit"
exit 1
fi

# If time matches, change service package in reply group
num=0
cat $TMP1 | while read GROUP_NAME
do
num=$[$num+1]
GROUP_NAME=`echo $GROUP_NAME |awk '{print $1}'`
SRVID=`$CMD "use $DB; select srvid from services where groupname ='$GROUP_NAME';"`
SRV_BWPKG=`$CMD "use $DB; select dlimit_ulimit from services where srvid ='$SRVID';"`
DN_BWPKG=`$CMD "use $DB; select dn_bwpkg from services where srvid ='$SRVID';"`
DN_ST=`$CMD "use $DB; select dn_st_time from services where srvid ='$SRVID';" | sed s/://g`
DN_ET=`$CMD "use $DB; select dn_et_time from services where srvid ='$SRVID';" | sed s/://g`

# If package UPgrade time is matched in services & packages have not changed already, then do it now - zaib
if [[ ${DN_ST#0} -ge ${currenttimetrimmed#0} ]] && [[ ${DN_ET#0} -le ${currenttimetrimmed#0} ]]; then
echo "$GROUP_NAME = UPGRADE time found for $GROUP_NAME from $SRV_BWPKG to $DN_BWPKG , changing radgroupreply ..."
$CMD "use $DB; update $GROUP set value ='$DN_BWPKG' where groupname ='$GROUP_NAME' and attribute ='Mikrotik-Rate-Limit';"
else
echo "$GROUP_NAME = DOWNGRADE time found for $GROUP_NAME from $DN_BWPKG to $SRV_BWPKG , changing radgroupreply ..."
$CMD "use $DB; update $GROUP set value ='$SRV_BWPKG' where groupname ='$GROUP_NAME' and attribute ='Mikrotik-Rate-Limit';"
fi
done

TOTSRV=`cat $TMP1 | wc -l`
echo "- Total Number of services for change = $TOTSRV"
# If required service found then look for Users
num=0
cat $TMP1 | while read GROUP_NAME
do
num=$[$num+1]
GROUP_NAME=`echo $GROUP_NAME |awk '{print $1}'`
$CMD "use $DB; select username from users where groupname ='$GROUP_NAME' and is_expired='N' and is_days_expired='N' and is_enabled='Y';" |sort >> $TMP2
done
if [ ! -s $TMP2 ]
then
echo "No User found for bandwidth upgrade $SRV_BW_DB , exit"
exit 1
fi
TOTUSER=`cat $TMP2 | wc -l`
echo "- Total Number of users for package change = $TOTUSER

- Checking for Dynamic Bandwidth Changing. Match Start/End time ...
"
#sleep 3

# Run loop forumla to run CMD for single or multi usernames
num=0
cat $TMP2 | while read users
do
num=$[$num+1]
USERNAME=`echo $users |awk '{print $1}'`
GROUP_NAME=`$CMD "use $DB; select groupname from users where username ='$USERNAME';"`
DN_ST=`$CMD "use $DB; select dn_st_time from services where groupname ='$GROUP_NAME';"| sed s/://g`
DN_ET=`$CMD "use $DB; select dn_et_time from services where groupname ='$GROUP_NAME';"| sed s/://g`
# If package UPgrade time is matched in services , then send COA to Mikrotik NAS - Zaib
if [[ ${DN_ST#0} -ge ${currenttimetrimmed#0} ]] && [[ ${DN_ET#0} -le ${currenttimetrimmed#0} ]]; then
# If user is Online, Upgrade his package
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
if [ ! -z "$ACCTSESID" ]; then
DN_BWPKG=`$CMD "use $DB; select dn_bwpkg from services where groupname ='$GROUP_NAME';"`
SRV_BWPKG=`$CMD "use $DB; select dlimit_ulimit from services where groupname ='$GROUP_NAME';"`
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' ;"`
NAS_COA_PORT=`$CMD "use $DB; select nas_coa_port from nas where nasname = '$NAS_IP';"`
echo User-Name=$USERNAME,Mikrotik-Rate-Limit=$DN_BWPKG | radclient -q -c 1 $NAS_IP:$NAS_COA_PORT coa $NAS_SECRET
echo "UPGRADE - $USERNAME - $GROUP_NAME - $SRV_BWPKG - NAS $NAS_IP - is online, changing package to $DN_BWPKG"
echo "UPGRADE - $USERNAME - $GROUP_NAME - $SRV_BWPKG - NAS $NAS_IP - is online, changing package to $DN_BWPKG" >> $TMP3
fi
if [ -z "$ACCTSESID" ]; then
echo "UPGRADE - $USERNAME - $GROUP_NAME - is not online, ignoring ..."
fi
# Else assume Downgrade & act accordingly
else
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
if [ ! -z "$ACCTSESID" ]; then
DN_BWPKG=`$CMD "use $DB; select dn_bwpkg from services where groupname ='$GROUP_NAME';"`
SRV_BWPKG=`$CMD "use $DB; select dlimit_ulimit from services where groupname ='$GROUP_NAME';"`
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' ;"`
NAS_COA_PORT=`$CMD "use $DB; select nas_coa_port from nas where nasname = '$NAS_IP';"`
echo User-Name=$USERNAME,Mikrotik-Rate-Limit=$SRV_BWPKG | radclient -q -c 1 $NAS_IP:$NAS_COA_PORT coa $NAS_SECRET
echo "DOWNGRADE - $USERNAME - $GROUP_NAME - NAS $NAS_IP - is online, changing package to $SRV_BWPKG"
echo "DOWNGRADE - $USERNAME - $GROUP_NAME - NAS $NAS_IP - is online, changing package to $SRV_BWPKG" >> $TMP4
fi
if [ -z "$ACCTSESID" ]; then
echo "DOWNGRADE - $USERNAME - $GROUP_NAME - NAS $NAS_IP - is not online, ignoring ..."

fi
fi
done
TOT_USERS_UP=`cat $TMP3 | wc -l`
TOT_USERS_DOWN=`cat $TMP4 | wc -l`
echo "

- Total Number of users packages changed on NAS for UP-GRADE = $TOT_USERS_UP
- Total Number of users packages changed on NAS for DOWN-GRADE = $TOT_USERS_DOWN

Start Time = $currenttime"
currenttime=$(date +%H:%M:%S)
echo "End Time = $currenttime"

#Script Ends here

---

 

Result:

When bandwidth time change starts …

root@radius:/temp# ./dn_bw.sh
* Checking for Dynamic Bandwidth Changing. Match Start/End time, Check if upgrade/downgrade is already done etc,
************** Bandwidht Packages UPgraded to New as per time **************
Sending CoA-Request of id 94 to 10.0.0.6 port 3799
User-Name = "zaib"
Mikrotik-Rate-Limit = "2048k/2048k"
rad_recv: CoA-ACK packet from host 10.0.0.6 port 3799, id=94, length=38
NAS-Identifier = "_CCR_GW"
NAS-IP-Address = 10.0.0.6
root@radius:/temp#
root@radius:/temp# ./bwsch.sh
* Checking for Dynamic Bandwidth Changing. Match Start/End time, Check if upgrade/downgrade is already done etc,
root@radius:/temp#

**********************************

root@radius:/temp# ./dn_bw.sh
* Checking for Dynamic Bandwidth Changing. Match Start/End time, Check if upgrade/downgrade is already done etc,
************** Bandwidht Packages Downgraded to original packages **************
Sending CoA-Request of id 54 to 10.0.0.6 port 3799
User-Name = "zaib"
Mikrotik-Rate-Limit = "1024k/1024k"
rad_recv: CoA-ACK packet from host 10.0.0.6 port 3799, id=54, length=38
NAS-Identifier = "_CCR_GW"
NAS-IP-Address = 10.0.0.6
root@radius:/temp# ./bwsch.sh
* Checking for Dynamic Bandwidth Changing. Match Start/End time, Check if upgrade/downgrade is already done etc,
root@radius:/temp#

BEFORE

AFTER

 

---

When bandwidth time change ends … return pkg to normal

---

RADCLIENT code to change bandwidth on the fly

For PPPoE

echo User-Name=$USERNAME,Acct-Session-Id=$ACCTSESID,Framed-IP-Address=$USER_IP,Mikrotik-Rate-Limit="128k/128k" | radclient -q -x $NAS_IP:$NAS_COA_PORT coa $NAS_SECRET

For HOTSPOT

echo Framed-IP-Address=$USER_IP,Mikrotik-Rate-Limit="128k/128k" | radclient -q -x $NAS_IP:$NAS_COA_PORT coa $NAS_SECRET