Mikrotik with Freeradius/mySQL – Change IP Pool After Expiration # Part-3

FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s>

 

---

Scenario:

It is required that when user account expires, he can still login but he should get IP from ‘expired pool’ so that we can redirect him to payment reminder page or route/manage in some other customized way via NAS.

1- FREERADIUS Section:

For this purpose we will modify the EXPIRATION module. as mentioned below

nano /etc/freeradius/sites-available/default

Add or modify

expiration{
userlock = 1
}
if(userlock){
# Let him connect with EXPIRED pool in reply
ok
update reply {
Reply-Message := "Your account has expired, %{User-Name} / Reason: DATE LIMIT REACHED / zaib"
Framed-Pool := "expired-pool"
}
}

Save & Exit.

---

Now add the user ‘expiration‘ in radcheck table using mysql

Note: If you add expiration check in RADCHECK section, the NAS will auto disconnect that user after the time reaches (session timeout) , or if you want to DC him using script, see the last section of this guide. ZAIB~

INSERT INTO `radius`.`radcheck` (
`id` ,
`username` ,
`attribute` ,
`op` ,
`value`
)
VALUES (
NULL , 'zaib', 'Expiration', ':=', '28 Mar 2016 15:35'
);

TIP: If we will add the expiration check in radcheck table for specific user, then FR will provide session timeout value for that user to the NAS, therefore NAS will disconnect the USER automatically & you dont need to disconnect him manually or by script. Session timeout provided is done automatically by FR if this check is present for that user. However in some cases if we want to manually disconnect those users (example without expiration value in radcheck attribute), then we have to use the script that should check for expired users today and Disconnect them & update group. see the last script in this pot i shared.

Note that I am using Expiration Date along with exact time as well. This can also be used to reduce receiving annoying customer call whose account expires default in night at 00:00 hours as default expiration is done in night and user will have no where to contact as in late night no one pick help support calls in DCN.

OR PHPMYADMIN as showed below …

---

2- Mikrtoik Section

Add new IP pool with the name of ‘expired-pool‘ (or as mentioned in Framed-Pool section)

/ip pool
add name=expired-pool ranges=192.168.100.1-192.168.100.255

---

Now try to login with user (which expiry date(time) have passed. and you will observe that user will still be able to login but he will get ip from the expired pool, and NAS will handle the request afterwards, either to redirect to expired pool, or some other customized action : )

Result:

---

# TIP:

Script to disconnect user expiring today & change there group in RADGROUP section, and email as well to admin

You can cron this script which may run daily. This will create list of EXPIRATION (expiration date will be pick from USERS table). It will compare user expiration ate with TODAY date, if matched, it will add update this user Group membership in radgroupreply to name DISABLED. Also it will kick user from both NAS ( i ad two nases)

#!/bin/sh
#set -x
# # # # QUICK AND DIRTY MADE IN 2 MINUTES. YOU MAY ADD VARIOUS CHECKS AND CONTROL IN THIS SCRIPT
# BASH base script to disconnect users whose expiry is today. ( i added function to include users from last week till date , just in case radius misses any*)
# the simple logic can be applied for about any other task as well. I tried to make it as simple as it can be
# By Syed Jahanzaib
# CREATED on : 16th July, 2015 / Modified on 16th Nov, 2015

# Local Variables

# Mysql credentials
SQLID="root"
SQLPASS="SQLPASSWORD"

# Mikrotik NAS IP and Radport and Shared Secret
NAS2="10.0.0.1:1700"
NAS1="10.0.0.2:1700"
NAS1_SECRET="RADSECRET"
NAS2_SECRET="RADSECRET"

# Temp holder for user list
TMP="/tmp/7days_till_today_expired_users_list.txt"
TODEXPLIST="/tmp/only_today_expired_users_list.txt"
#TODAY=$(date +"%Y-%m-%d")
TODAY=`date -d "yesterday" '+%Y-%m-%d'`
WEEK=`date -d "-7 days" '+%Y-%m-%d'`

# Gmail Data
GMAILID="YOURGMAILID@gmail.com"
GMAILPASS="GMAIL-PASS"
SMTP="64.233.184.108:587"
ADMINMAIL1="ADMIN1@hotmail.com"
ADMINMAIL2="ADMIN2@hotmail.com"
COMPANY="ZAIB-COMPANY"
MAILSUB="$COMPANY - List of account expired on $TODAY"

# Pull users that are expiring TODAY
mysql -u$SQLID -p$SQLPASS --skip-column-names -s -e "use radius; select username from user_status_info where card_expire_on between '$WEEK' AND '$TODAY';" > $TMP
mysql -u$SQLID -p$SQLPASS --skip-column-names -s -e "use radius; select username,card_expire_on from user_status_info where card_expire_on ='$TODAY';" > $TODEXPLIST
# Apply formula
num=0
cat $TMP | while read users
do
num=$[$num+1]
USERNAME=`echo $users | awk '{print $1}'`
ACCTID=`mysql -u$SQLID -p$SQLPASS --skip-column-names -s -e "use radius; select acctsessionid from radacct where username ='$USERNAME' AND acctstoptime IS NULL;"`

# Update user status in RAD group so that he will get ip from DISABLED pool or be rejected
mysql -u$SQLID -p$SQLPASS --skip-column-names -s -e "use radius; update radusergroup set groupname='DISABLED' where username='$USERNAME';"

# Disconnect users now using RADCLIENT with username adn Account session ID taken from radacct table
echo user-name=$USERNAME,Acct-Session-Id=$ACCTID | radclient -x $NAS1 disconnect $NAS1_SECRET
echo echo user-name=$USERNAME,Acct-Session-Id=$ACCTID | radclient -x $NAS2 disconnect $NAS2_SECRET
done

TOT=`cat $TMP | wc -l`
echo "
-----------
-----------

From $WEEK till $TODAY - Expired Users = $TOT"

sendemail -t $GMAILID -u "$MAILSUB" -o tls=yes -s $SMTP -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$TODEXPLIST -o message-content-type=text
sendemail -t $GMAILID -u "$MAILSUB" -o tls=yes -s $SMTP -t $ADMINMAIL2 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$TODEXPLIST -o message-content-type=text 

CRON Example: to run it daily at 5pm

# Run @ 5 pm daily in evening
0 17 * * * /temp/dc_expire.sh

---

# 2 > Another script for reference purpose

Following is another script I made for customized radius billing system based on freeradius. It will check for users whose expiry is Today, its scheduled to run at 12:00pm (noon) , it will update few columns in users table, and in radgroup. Read it thoroughly , I am just sharing it for reference purposes only.

#!/usr/bin/env bash
####!/bin/sh
#set -x
#trap "set +x; set -x" DEBUG
# BASH base script to discopnnect users whose expiry is today.
# the simple logic can be applied for about any other task as well. I tried to make it as simple as it can be
# also check if the scrpit ran successfully then dont re-run today,
# By Syed Jahanzaib
# CREATED on : 16th July, 2015
# Modified on 25h June 2018

# Local Variables
# Mysql credentials
SQLID="root"
SQLPASS="ROOTPASS"
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLID --skip-column-names -s -e"
DB="radius"
#Table which contain main users information
TBL="users"
#Rad user group in which we will update user profile like from 1mb to expired or likewise
GROUP="radusergroup"
NEXTSRV="expired"
TBL_LOG="log"

# Date Time Variables
DATE=$(date +%d-%m-%Y)
FULL_DATE=`date`
CURR_HOUR=$(date +%H)
TODAY=$(date +"%Y-%m-%d")
WEEK=`date -d "-1000 days" '+%Y-%m-%d'`
BEGIN="1970-01-01"
H=$(date +'%-H')
################################
## Set time for script execution
## IMPORTANT, donot use 0 in it
SCR_SCHEDULED_TIME="12"
################################

#Network Related
hostname=`hostname`
IP=`ip route get 8.8.8.8 | awk '{print $NF; exit}'`

# Gmail Data
GMAILID="MYGMAIL@gmail.com"
GMAILPASS="MYGMAILPASS"
SMTP="64.233.184.108:587"
ADMINMAIL1="aacableAThotmail.com"
COMPANY="ZAIB"
MAILSUB="$COMPANY INFO -$HOSTNAME-$IP- List of account expired on $TODAY"

##################################################
##################################################
# Temp folder and files setup for various actions
##################################################
##################################################
TEMP="temp"
# Temp holders to store users list
ALLEXPLIST=/$TEMP/all_time_expired_users_list.txt
TODEXPLIST=/$TEMP/only_today_expired_users_list.txt
FILE=/$TEMP/check_user_expiration_exec.txt

# Checking if /temp folder is previously present or not , if not create one ...
{
if [ ! -d "/$TEMP" ]; then
echo
mkdir /$TEMP
fi
}

# remove and recreate users list to avoid any Duplication or issue
{
if [ -f $ALLEXPLIST ]; then
rm $ALLEXPLIST
touch $ALLEXPLIST
fi
}
{
if [ -f $TODEXPLIST ]; then
rm $TODEXPLIST
touch $TODEXPLIST
fi
}
# File Holder to store last execution date
{
if [ ! -f $FILE ]; then
touch $FILE
fi
}

############################
############################
###### START thE script cOdE
############################
############################

CHK_GREP=`grep -c $DATE $FILE`
echo "Current Date time is $FULL_DATE
"

# If script is executed successfully in current date, then dont re-run and exit now
echo "Stage-1:
Checking if the script have already ran successfully for today, by getting current date from the $FILE"
if grep -q $DATE $FILE >/dev/null 2>&1
then
echo "
Result:
It seems the script was executed successfully today $DATE, It will run on next date change. Exiting now ..."
exit 1
fi

# Check if time is matched that is greater or equals to $SCR_SCHEDULED_TIME and also check if script hae ran successfully or not previously by getting date from $FILE
echo "
Stage-2: Checking if current hour is equals or greater then '$SCR_SCHEDULED_TIME hours' & previous run is not done yet ..."
if [ "$CURR_HOUR" -ge "$SCR_SCHEDULED_TIME" ] && [ "$CHK_GREP" == "0" ]; then
echo "
Stage-2:
Time matched that is equals or greater then $SCR_SCHEDULED_TIME"
# If all matches, then run the code ! and add time stamp in file to avoid repeatingo/re-running the following script code on next RUN
echo "Finally:
All conditions time + this day first execution matched, Now running the script code ..."
date +%d-%m-%Y >> $FILE

# Pull users that are expiring TODAY
$CMD "use $DB; select username from $TBL where expiration between '$BEGIN' AND '$TODAY';" |sort > $ALLEXPLIST
$CMD "use $DB; select username from $TBL where expiration ='$TODAY';" |sort > $TODEXPLIST

# IF no user found , show error and exit - zaib
CHK=`wc -m $ALLEXPLIST | awk {'print $1}'`
if [ "$CHK" -eq 0 ]
then
echo "No user found expiring today, exiting ..."
exit 1
fi

# Apply formula
num=0
cat $ALLEXPLIST |while read data
do
num=$[$num+1]
USERNAME=`echo $data`

# Update user status in RADgroup & users table so that he will rejected on next login
IS_EXPIRED=`$CMD "use $DB; select is_expired from users where username ='$USERNAME';"`
if [ "$IS_EXPIRED" = "N" ]; then
echo "user_IS EXIPRED is NO *************************************************************************************************"
$CMD "use $DB; update $GROUP set groupname='$NEXTSRV' where username='$USERNAME';"
$CMD "use $DB; update $TBL set is_expired='Y' where username='$USERNAME';"
$CMD "use $DB; update $TBL set is_days_expired='Y' where username='$USERNAME';"
$CMD "use $DB; INSERT into $TBL_LOG (data, msg) VALUES ('$USERNAME', '$USERNAME - User reached Expiration, Group udpated.');"
fi

# CHECK ONLINE AND KICK
# Pull account session id from radacct table, which will be used to COA OR user disconnection
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where username ='$USERNAME' AND acctstoptime is NULL;"`
# If user is not Online , just give info that he is not online
if [ -z "$ACCTSESID" ]; then
$CMD "use $DB; INSERT into $TBL_LOG (data, msg) VALUES ('$USERNAME', '$USERNAME - User reached Expiration, Group udpated but its already offline');"
echo "$USERNAME - User reached Expiration, Group udpated but its already offline."
else
# Kick user by getting his NAS ip, secret and other info and log
# Mikrotik NAS IP and Radport and Shared Secret
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';"`
# Disconnect users now using RADCLIENT with username adn Account session ID taken from radacct table
echo user-name=$USERNAME,Acct-Session-Id=$ACCTSESID | radclient -x $NAS_IP:$NAS_COA_PORT disconnect $NAS_SECRET
# LOG into LOG TABLE in radius DB
$CMD "use $DB; INSERT into $TBL_LOG (data, msg) VALUES ('$USERNAME', '$USERNAME - User reached Expiration, kicked & updated,.');"
echo "$USERNAME - User reached Expiration, kicked & updated."
fi
done

#TOT=`cat $TODEXPLIST | wc -l`
#echo "
#-----------
#-----------

#$COMAPNY _ List of TODAY $TODAY expired users, Total = $TOT"
#cat $TODEXPLIST

# Send Email to all admin - currently 4 users
sendemail -t $GMAILID -u "$MAILSUB" -o tls=yes -s $SMTP -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$TODEXPLIST -o message-content-type=text
fi

---

Regard’s
Syed Jahanzaib