Syed Jahanzaib Personal Blog to Share Knowledge !

February 14, 2022

RM: Auto Renew User if Deposit available

Filed under: Radius Manager — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 11:04 AM

Revision History:

  1. 24-Jun-2015 / Added Base Script
  2. 29-Jun-2016 / Added Invoice function
  3. 14-Feb-2022 / Added multi check for quota related functions


As requested by an Valenzuela / African friends

Scenario:

In radius manager, there are few options to purchase credits via online payment gateways like paypal or others. If the user account is expired and he purchase service online, it adds the deposit into user account but it does not auto renew the service (as per my assumption, as paypal doesn’t works here in pakistan, so i have very little to no knowledge on it).

Example:

err

 

As a workaround, I made below script that can perform following functions ,,,

  1. Scheduled to run after every 5 (or x) minute(s)
  2. Fetch all users who are expired either by Date or Quota
  3. Check if these users have DEPOSIT available (credits above then 0)
  4. Check the current service price and match it with the available deposit/credits
  5. If deposit is not sufficient as per service price, Then print error
  6. If deposit is enough, renew the service , Add Expiration Days (according to the service package)
  7. Reset the QUOTA …
    * if account is fresh, add new quote as per package
    * if account is old and quota is expired, then reset it add new quota as per package
    * if account date is expired but quota is remaining, then add the new quota in existing quota (addictive mode
    * Show user Before / After Quota
    – If user is online show his IP / NAS IP / Time / NAS-IP]if any and sends email/sms to user about the renewal done by deposit : )

We can further add the SMS/EMAIL function in the script as per requirements to let know about the renewal.

Disclaimer: The script can further be customized according to the requirements. No part of this script is copied from anywhere. You are free to use it, modify it as you like [keep the header intact].
This is my own idea Just to share with anyone who is in similar requirements or just for learning purposes !


SCRIPT!

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

& paste following, make sure to change the SQL Password !

#!/bin/bash
#set -x
clear
# Script to renew user account via check deposit and act accordingly
# For Radius Manager 4.1.x
# Created by Syed Jahanzaib
# https://aacable.wordpress.com / aacable@hotmail.com
# 24th Jun, 2016 , 18 Ramazan, 1437 Hijri
# Last modified on 14-Feb-2022
# Colors Config . . . [[ JZ . . . ]]
ESC_SEQ="\x1b["
COL_RESET=$ESC_SEQ"39;49;00m"
COL_RED=$ESC_SEQ"31;01m"
COL_YELLOW=$ESC_SEQ"33;01m"
COL_GREEN=$ESC_SEQ"32;01m"
SQLUSER="root"
SQLPASS="XXXXXXX"
export MYSQL_PWD=$SQLPASS
DB=radius
CMD="mysql -u$SQLUSER --skip-column-names -s -e"
bytesToHuman() {
b=${1:-0}; d=''; s=0; S=(Bytes {K,M,G,T,P,E,Z,Y}iB)
while ((b > 1024)); do
d="$(printf ".%02d" $((b % 1024 * 100 / 1024)))"
b=$((b / 1024))
let s++
done
echo "$b$d ${S[$s]}"
}
USERLIST="/tmp/deposituserlist.txt"
> $USERLIST
DATE=`date`
#Create list of users which have deposit more then 0.00 value, means valid deposite
$CMD "use $DB; 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 = '1' OR tmp.acctype = '2' OR tmp.acctype = '3' OR tmp.acctype = '4' OR tmp.acctype = '5' )
AND tmp.enableuser = 1 AND
(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
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
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
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
OR
IF (limitexpiration=1, UNIX_TIMESTAMP(expiration) - UNIX_TIMESTAMP(NOW()), 1) <= 0) LIMIT 0, 50;" | awk '{print $1}' > $USERLIST
TOTUSR=`$CMD "use $DB; select username from rm_users;" | wc -l`
echo "- INFO: Total Users scanned: $TOTUSR

"
#LOOK FOR VALID USER IN FILE, IF EMPTY THEN EXIT
USRVALID=`cat $USERLIST`
if [ -z "$USRVALID" ]; then
echo "INFO: No user found with Expired Date/Quota package ... Exiting peacefully!"
exit 1
fi
# Apply Formula to read the file in which users list and act accordingly.
num=0
cat $USERLIST | while read users
do
num=$[$num+1]
USR=`echo $users | awk '{print $1}'`
DEPOSIT=`$CMD "use radius; SELECT credits FROM rm_users where username = '$USR';" | sed 's/\..*$//'`
###########################################
# ACCOUNT EXPIRY CHECK and other variables#
###########################################
TODAY=$(date +"%Y-%m-%d")
TODAYHM=$(date +"%Y-%m-%d-%H-%M")
TODAYDIGIT=`echo $TODAY | sed -e 's/-//g'`
MONTH=$(date +"-%m")
CMONTH=`echo $MONTH | sed -e 's/-//g'`
MONTHYEAR=$(date +"%B-%Y")
ALPHAMONTHYEAR=`echo $MONTHYEAR #| sed -e 's/-//g'`
CURR_MONTHYEAR=$(date +"%Y-%m")
SRVEXPIRYFULL=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT expiration FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2'`
FULLNAME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT firstname, lastname FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2'`
MOBILE=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT mobile FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2'`
COUNTRY=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT country FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2'`
STATE=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT state FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2'`
ADDRESS=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT address FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2'`
LOGOFFDATE=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT lastlogoff FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2 {print $1,$2}'`
SRVID=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvid FROM radius.rm_users WHERE rm_users.username = '$USR';" |awk 'FNR == 2 {print $1}'`
SRVPRICE=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT unitprice FROM radius.rm_services WHERE rm_services.srvid = $SRVID;" |awk 'FNR == 2 {print $1}' | cut -f1 -d"."`
SRVEXPIRYFULLD=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT expiration FROM radius.rm_users WHERE username = '$USR';" |awk '{print $1}' | sed 's/expiration//'`
SRVEXPIRY=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT expiration FROM radius.rm_users WHERE username = '$USR';" |awk 'FNR == 2' | sed -e 's/-//g' | sed 's/00:.*//'`
PKGNAME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvname FROM radius.rm_services WHERE rm_services.srvid = '$SRVID';" |awk 'FNR == 2'`
PKGQUOTA=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT trafficunitcomb FROM rm_services WHERE srvid= '$SRVID';" |awk 'FNR == 2'`
PKGQUOTAB=$(($PKGQUOTA / 1024))
PKGQUOTABYTES=$(($PKGQUOTA * 1024 * 1024))
USR_CUR_COMBLIMIT=`$CMD "use $DB; SELECT comblimit FROM rm_users WHERE username= '$USR';"`
USR_CUR_COMBLIMIT_HUMAN=`bytesToHuman $USR_CUR_COMBLIMIT`
TOT_DOWN_UP_CURR_MONTH_IN_BYTES=`$CMD "use $DB; SELECT ((SUM(AcctInputOctets)+SUM(AcctOutputOctets))) FROM radacct WHERE username ='$USR' AND acctstarttime LIKE '$CURR_MONTHYEAR-%' LIMIT 0 , 30;"`
TOT_USER_DOWNLOAD_SINCE_LAST_REFRESH_IN_BYTES=`$CMD "use $DB; SELECT downlimit from rm_users where username = '$USR';" |sed 's/[\._-]//g'`
TOT_USER_UPLOAD_SINCE_LAST_REFRESH_IN_BYTES=`$CMD "use $DB; SELECT uplimit from rm_users where username = '$USR';" |sed 's/[\._-]//g'`
RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_VALUE=`$CMD "use $DB; SELECT SQL_CALC_FOUND_ROWS 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 username LIKE '$USR%' AND (tmp.acctype = '0' OR tmp.acctype = '1' OR tmp.acctype = '2' OR tmp.acctype = '3' OR tmp.acctype = '4' OR tmp.acctype = '5' ) LIMIT 0, 50;" | awk '{print $3}'`
RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_VALUE_IN_HUMAN_FRIENDLY_FORMAT=`bytesToHuman $RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_VALUE`
IS_QUOTA_LEFT_IN_NEGATIVE=`echo $RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_VALUE_IN_HUMAN_FRIENDLY_FORMAT | grep -i -c "-"`
RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_NEGATIVE_VALUE_IN_BYTES_WITHOUT_DASH=`echo $RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_VALUE_IN_HUMAN_FRIENDLY_FORMAT |sed 's/[\._-]//g'`
RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_NEGATIVE_VALUE_IN_HUMAN_FRIENDLY_VALUE=`bytesToHuman $RM_CMD_FOR_USER_ACTUAL_LIVE_QUOTA_NEGATIVE_VALUE_IN_BYTES_WITHOUT_DASH`
RQT=`$CMD "use $DB; SELECT username,
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)
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
FROM rm_users JOIN rm_services USING (srvid) ORDER BY username ASC
) AS tmp
WHERE 1 AND (tmp.acctype = '0' OR tmp.acctype = '1' OR tmp.acctype = '3' OR tmp.acctype = '4')
AND username LIKE '$USR%';" | awk '{print $2}'`
RQT_OUT=`echo $RQT`
RQT_RESULT1=`echo $RQT | grep -i -c "-"`
RQT_RESULT_REMOVE_HYPHEN=`echo $RQT | sed -e 's/-//g'`
#exit 1
RQT4=`bytesToHuman $RQT_RESULT_REMOVE_HYPHEN`
QTL_Y_OR_NO=`$CMD "use $DB; SELECT limitcomb FROM rm_services WHERE srvid = '$SRVID';"`
if [ "$DEPOSIT" -eq 0 ]; then
LASTUSRBAL=0
else
LASTUSRBAL=$(($DEPOSIT - $SRVPRICE))
fi
TIMEUNITEXP=`$CMD "use $DB; SELECT timeunitexp FROM radius.rm_services WHERE srvid = '$SRVID';"`
TIMEBASEEXP=`$CMD "use $DB; SELECT timebaseexp FROM radius.rm_services WHERE srvid = '$SRVID';"`
if [ "$TIMEBASEEXP" == "2" ]; then
NEXTEXPIRYADD=$(date +"%Y-%m-%d" -d "+$TIMEUNITEXP days")
EXPERIOD="$TIMEUNITEXP Days"
fi
# Set Quota Limit variable which will be used in the end zzzzzzzzzzzz
if [ "$TIMEBASEEXP" == "3" ]; then
NEXTEXPIRYADD=$(date +"%Y-%m-%d" -d "+$TIMEUNITEXP month")
EXPERIOD="$TIMEUNITEXP Month"
fi
# Set Expiry Date/Month Unit
if [ $PKGQUOTA -eq 0 ]
then
QT="UNLIMITED"
else
QT="$PKGQUOTA MB"
fi
# Check Service Expiry Date, if Active then ignore
IS_USER_EXPIRED=`rmauth 127.0.0.1 $USR 1 |grep -i -c "The account has expired"`
if [ "$IS_USER_EXPIRED" -eq 1 ];then
USREXPORNOT=1
else
USREXPORNOT=0
fi
# Check if user quota is ended or not
IS_USER_QUOTA_END=`rmauth 127.0.0.1 $USR 1 |grep -i -c "Total traffic limit reached"`
if [ "$IS_USER_QUOTA_END" -eq 1 ];then
USRQTORNOT=1
else
USRQTORNOT=0
fi
if [ "$USREXPORNOT" -eq 1 ]; then
DATEEXP="$USR - *** Account Date Expired that is $SRVEXPIRYFULL !! **"
else
DATEEXP="$USR - Account Date is OK that is $SRVEXPIRYFULL."
fi
#IS_QUOTA_LEFT_IN_NEGATIVE zzzzzzzzzzzzzzzzzzz
if [ "$USRQTORNOT" -eq 1 ] && [ "$IS_QUOTA_LEFT_IN_NEGATIVE" -eq 1 ] ; then
RQT_in_NEGATIVE_HUMAN=`bytesToHuman $RQT_RESULT_REMOVE_HYPHEN`
QTEXP="$USR - *** Account QUOTA Expired that is Negative $RQT_in_NEGATIVE_HUMAN !! **"
fi
if [ "$USRQTORNOT" -eq 0 ]; then
RQT_in_POSITIVE_HUMAN=`bytesToHuman $RQT_RESULT_REMOVE_HYPHEN`
QTEXP="$USR - Account QUOTA is OK that is $RQT_in_POSITIVE_HUMAN "
fi
if [ "$DL" == "NULL" ]; then
QTEXP="$USR - NO QUOTA have been added yet! seems fresh account."
fi
if [ "$UL" == "NULL" ]; then
QTEXP="$USR - NO QUOTA have been added yet! seems fresh account."
fi
if [ "$RQT_OUT" == "0" ]; then
QTEXP="$USR - QUOTA INFO: No Quota hase been added yet! seems fresh account."
#echo "$USR - QUOTA INFO: NO quota hase been added yet! seems fresh account."
fi

########### ACCOUNT STATUS EXPIRED BUT NOT ENOUGH DEPOSIT to RENEW ACTION ############
if [ "$DEPOSIT" -eq 0 ]; then
USRDEPORNOT=0
fi
if [ "$DEPOSIT" -lt "$SRVPRICE" ]; then
USRDEPORNOT=0
echo "
$DATEEXP
$QTEXP
$USR - Current Expiry = $SRVEXPIRYFULLD
$USR - Pacakge Name/Price: $PKGNAME | $SRVPRICE PKR
$USR - Current Deposite Available = $DEPOSIT"
echo -e "$COL_RED$USR | ERROR: Insufficient deposit for Renewal ! Current Deposite is $DEPOSIT and SRV renewal price is $SRVPRICE $COL_RESET
"
fi
if [ "$DEPOSIT" -eq "$SRVPRICE" ] || [ "$DEPOSIT" -gt "$SRVPRICE" ]; then
USRDEPORNOT=1
echo -e "$COL_GREEN$USR INFO: | Deposit Balance: $DEPOSIT | Service Name: $PKGNAME | Price $SRVPRICE $COL_RESET"
fi
########### ACCOUNT STATUS EXPIRED and DEPOSIT IS ENOUGH TO RENEW ACTION ############
if [ "$USREXPORNOT" -eq 1 ] || [ "$USRQTORNOT" -eq 1 ]; then
if [ "$USRDEPORNOT" -eq 1 ] ; then
# RENEW USERS IF ALL CONDITIONS MATCHED / PRINT FETCHED VALUES , JUST FOR INFO / ZAIB bbbbb
echo "
$DATEEXP
$QTEXP
$USR - Resetting Date: Current Expiry = $SRVEXPIRYFULLD / Next Expiry: $NEXTEXPIRYADD | UNIT = $EXPERIOD
$USR - Pacakge Name/Price: $PKGNAME | $SRVPRICE PKR
$USR - Current Deposite Available = $DEPOSIT | Deposite Balance after Deduction: $LASTUSRBAL"
# ADD 30 DAYS VALUE TO EXPIRED USER ACCOUNTzzzzzzzzzz
$CMD "use radius; UPDATE rm_users SET expiration = '$NEXTEXPIRYADD' WHERE username = '$USR';"
# ADD COMMENTS
$CMD "use radius; UPDATE rm_users SET comment = '$USR account last renewed from previous DEPOSIT $DATE' WHERE username = '$USR';"
# ADD SYSLOG ENTRY
#$CMD "use radius; INSERT INTO rm_syslog (datetime, ip, name, eventid, data1) VALUES (NOW(), 'n/a', 'DEPOSIT_$USR', '$USR', '$USR renewd service $PKGNAME');"
# UPDATE User Balance
$CMD "use radius; UPDATE rm_users SET credits = '$LASTUSRBAL' WHERE username = '$USR';"
# ADD INVOICE
$CMD "use $DB; INSERT INTO rm_invoices (managername, username, date, bytesdl, bytesul, bytescomb, downlimit, uplimit, comblimit, time, uptimelimit,
days, expiration, capdl, capul, captotal, captime, capdate, service, comment, transid, amount, invnum,
address, city, zip, country, state, fullname, taxid, paymentopt, paymode, invtype, paid, price, tax, remark,
balance, gwtransid, phone, mobile, vatpercent )
VALUES
('admin', '$USR', NOW(), '0', '0', '$PKGQUOTABYTES', '0', '0', '$PKGQUOTABYTES', '0', '0', '30', '$NEXTEXPIRYADD', '0', '0', '1', '0', '1', '$PKGNAME', 'This user service renewed by Deposit/Payment', '577343812eee0', '1', '$TODAYHM', '$ADDRESS', '$CITY', '00000', '$COUNTRY', '$STATE', '$FULLNAME', 'n/a',
DATE_ADD(CURDATE(), INTERVAL '10' DAY), '0', '0', '$TODAY', '$SRVPRICE', '0.000000', '$TODAYHM', '$LASTUSRBAL', '', '$MOBILE', '$MOBILE', '0.00' );"

$CMD "use $DB; INSERT INTO rm_invoices (managername, username, amount, price, tax, vatpercent, balance,
date, service, paymode, invgroup, paymentopt, transid)
VALUES ('admin', 'admin', 1, '-$SRVPRICE', '0', '0.00',
'', NOW(), 'Ref.: C-$TODAYHM', '2', '1', DATE_ADD(CURDATE(), INTERVAL '10' DAY),
'577343812eee0' );"
if [ "$RQT_RESULT1" -eq 1 ] ;then
echo "$USR - Total Data Reamining since last renewal = NO DATA REMAINING !"
else
echo "$USR - Total Data Reamining since last renewal = $RQT4"
fi
######################################################
############## QUOTA SECTION STARTS HERE #############
######################################################
# UPDATE Quota limitations if any, else ignore
DL=`$CMD "use radius; SELECT (SELECT SUM(acctoutputoctets) FROM radacct WHERE username = '$USR') - (SELECT COALESCE(SUM(dlbytes), 0) FROM rm_radacct WHERE username = '$USR');"`
UL=`$CMD "use radius; SELECT (SELECT SUM(acctinputoctets) FROM radacct WHERE username = '$USR') - (SELECT COALESCE(SUM(ulbytes), 0) FROM rm_radacct WHERE username = '$USR');"`
if [ "$DL" == "NULL" ]; then
DL=x
fi
if [ "$UL" == "NULL" ]; then
UL=x
fi
if [ "$RQT_OUT" == "0" ]; then
QTEXP="QUOTA INFO: NO QUOTA have been added yet! seems fresh account."
echo "QUOTA INFO: NO QUOTA have been added yet! seems fresh account."
fi
# QUOTA FRESH ADD MODE FOR NEW ACCOUNT
if [ "$DL" == "x" ] || [ "$UL" == "x" ]; then
echo "*********** QUOTA FRESH ADD MODE FOR NEW ACCOUNT"
echo "$USR - QUOTA INFO: Account Seems Fresh, Adding NEW Quota ..."
#$CMD "use $DB; UPDATE rm_users SET comblimit = '$PKGQUOTABYTES' WHERE username = '$USR';"
ADDICTIVE_COMB_LIMIT=`echo "1+$PKGQUOTABYTES" |bc -l`
$CMD "use $DB; UPDATE rm_users SET comblimit = '$ADDICTIVE_COMB_LIMIT' WHERE username = '$USR';"
fi
#exit 1
# QUOTA RESET MODE for expired QUOTA
IS_USER_QUOTA_END=`rmauth 127.0.0.1 $USR 1 |grep -i -c "Total traffic limit reached"`
if [ "$IS_USER_QUOTA_END" -eq 1 ] && [ "$RQT_RESULT1" -eq 1 ] ;then
DL=`$CMD "use radius; SELECT (SELECT SUM(acctoutputoctets) FROM radacct WHERE username = '$USR') - (SELECT COALESCE(SUM(dlbytes), 0) FROM rm_radacct WHERE username = '$USR');"`
UL=`$CMD "use radius; SELECT (SELECT SUM(acctinputoctets) FROM radacct WHERE username = '$USR') - (SELECT COALESCE(SUM(ulbytes), 0) FROM rm_radacct WHERE username = '$USR');"`
FINAL1_COMB_LIMIT=`echo "$DL+$UL" |bc -l`
FINAL2_COMB_LIMIT=`echo "$FINAL1_COMB_LIMIT+$PKGQUOTABYTES" |bc -l`
# ADDing Quota
echo "$USR - QUOTA info: RESET MODE: Existing Quota limit is Used. Adding New Quota as per package ..."
$CMD "use radius; UPDATE rm_users SET downlimit = '- $DL', uplimit = '- $UL', comblimit = '$FINAL2_COMB_LIMIT' WHERE username = '$USR';"
fi
## QUOTA ADDICTIVE MODE
IS_USER_QUOTA_END=`rmauth 127.0.0.1 $USR 1 |grep -i -c "Total traffic limit reached"`
if [ "$IS_USER_QUOTA_END" -eq 1 ] && [ "$RQT_RESULT_REMOVE_HYPHEN" -gt 0 ] ;then
#echo "********* QUOTA ADDICTIVE MODE"
echo "$USR - QUOTA info: ADDICTIVE MODE: Existing Quota is remaining. Adding New Quota in existing quota as per pacakge (IF ANY) ..."
##echo "$USR - QUOTA setting is - ADDICTIVE ..."
ADDICTIVE_COMB_LIMIT=`echo "$USR_CUR_COMBLIMIT+$PKGQUOTABYTES" |bc -l`
$CMD "use $DB; UPDATE rm_users SET comblimit = '$ADDICTIVE_COMB_LIMIT' WHERE username = '$USR';"
fi
# QUOTA ADDICTIVE MODE for DATE EXPIRED USERS ONLY
IS_USER_QUOTA_END=`rmauth 127.0.0.1 $USR 1 |grep -i -c "Total traffic limit reached"`
if [ "$IS_USER_QUOTA_END" -eq 0 ] && [ "$RQT_RESULT1" -eq 0 ] ;then
echo "$USR - QUOTA info: ADDING quota for DATE EXPIRED USERS ONLY (Existing Quota will be added in new Quota, IF ANY) ..."
ADDICTIVE_COMB_LIMIT=`echo "$USR_CUR_COMBLIMIT+$PKGQUOTABYTES" |bc -l`
$CMD "use $DB; UPDATE rm_users SET comblimit = '$ADDICTIVE_COMB_LIMIT' WHERE username = '$USR';"
fi
#####################################################
############## QUOTA SECTION ENDS HERE ##############
#####################################################
RQT=`$CMD "use radius; SELECT username,
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)
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
FROM rm_users JOIN rm_services USING (srvid) ORDER BY username ASC
) AS tmp
WHERE 1 AND (tmp.acctype = '0' OR tmp.acctype = '1' OR tmp.acctype = '3' OR tmp.acctype = '4')
AND username LIKE '$USR%';" | awk '{print $2}'`
RQT_HUMAN_FRIENDLY=`bytesToHuman $RQT`
echo "$USR - Current Updated Quota after the renewal ... : $RQT_HUMAN_FRIENDLY"
USRIP=`$CMD "use $DB; SELECT framedipaddress FROM radacct WHERE acctstoptime IS NULL AND username = '$USR';"`
if [ -z "$USRIP" ]; then
echo "$USR - Online Status: Offline !"
else
NASIP=`$CMD "use $DB; SELECT nasipaddress FROM radacct WHERE username = '$USR' and acctstoptime IS NULL;"`
ONLINE_TIME=`$CMD "use $DB; SELECT acctsessiontime FROM radacct WHERE username = '$USR' and acctstoptime IS NULL;"`
ONLINE_START_TIME=`$CMD "use $DB; SELECT acctstarttime FROM radacct WHERE username = '$USR'and acctstoptime IS NULL;"`
ONLINE_TIME_FIN=`echo "$ONLINE_TIME/60/60" |bc -l | cut -c1-4`
echo "$USR - Online Status: IP: $USRIP | Online Since: $ONLINE_START_TIME | Online Time: $ONLINE_TIME_FIN Hour(s) | NAS: $NASIP "
fi
fi
fi
done


Result:

  • /temp/auto.sh
- INFO: Total Users scanned: 2
INFO: No user found with Expired Date/Quota package ... Exiting peacefully!
  • /temp/auto.sh
- INFO: Total Users scanned: 2

test50 INFO: | Deposit Balance: 99960 | Service Name: 2mb Speed - 30 Days - 1 GB Quota | Price 10

test50 - *** Account Date Expired that is 2022-02-14 00:00:00 !! **
test50 - Account QUOTA is OK that is 2.00 GiB
test50 - Resetting Date: Current Expiry = 2022-02-14 / Next Expiry: 2022-03-16 | UNIT = 30 Days
test50 - Package Name/Price: 2mb Speed - 30 Days - 1 GB Quota | 10 PKR
test50 - Current Deposit Available = 99960 | Deposite Balance after Deduction: 99950
test50 - Total Data Reamaining since last renewal = 2.00 GiB
test50 - QUOTA info: ADDING quota for DATE EXPIRED USERS ONLY (Existing Quota will be added in new Quota, IF ANY) ...
test50 - Current Updated Quota after the renewal ... : 3.00 GiB
test50 - Online Status: Offline !
  • /temp/auto.sh

- INFO: Total Users scanned: 2

test50 INFO: | Deposit Balance: 99950 | Service Name: 2mb Speed - 30 Days - 1 GB Quota | Price 10

test50 - Account Date is OK that is 2022-03-16 00:00:00.
test50 - *** Account QUOTA Expired that is Negative 742.69 MiB !! **
test50 - Resetting Date: Current Expiry = 2022-03-16 / Next Expiry: 2022-03-16 | UNIT = 30 Days
test50 - Pacakge Name/Price: 2mb Speed - 30 Days - 1 GB Quota | 10 PKR
test50 - Current Deposite Available = 99950 | Deposite Balance after Deduction: 99940
test50 - Total Data Reamining since last renewal = NO DATA REMAINING !
test50 - QUOTA info: RESET MODE: Existing Quota limit is Used. Adding New Quota as per package ...
test50 - Current Updated Quota after the renewal ... : 1024.00 MiB
test50 - Online Status: Offline !

July 2, 2021

Radius | Disconnect users after service change / user disable

Filed under: Mikrotik Related, Radius Manager — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 8:50 AM


Note for self

In older version of radius manager, when OP disables any user account or change user service package , those changes doesnt take effect until the user disconnect/reconnect. Sometimes user remains connected for days. Back in those days , we made a workaround by creating a mysql trigger and script combination. 

Assuming you have fully functional radius working, along with root access to DB. Save this trigger.sql and import it in mysql radius DB.

Example: mysql -uroot -pMYPASS radius < trigger.sql

Following trigger will check for rm_users table changes, and if it found any changes in the users disable/enable or srvid changes, it will add entry in the rm_kickuserstable & our schedule script will pick the data from there and will act accordingly …

MYSQL > kickuser_trigger

-- Host: localhost Database: radius
-- Server version 5.5.54-0ubuntu0.12.04.1
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
/!50003 CREATE/ /!50017 DEFINER=root@localhost/ /*!50003 TRIGGER kickuser_trigger BEFORE UPDATE ON rm_users
FOR EACH ROW BEGIN
IF NEW.enableuser <> OLD.enableuser THEN
INSERT INTO rm_kickuser (datetime, username, msg) VALUES (NOW(), new.username, new.enableuser);
END IF;
IF NEW.srvid <> OLD.srvid THEN
INSERT INTO rm_kickuser (datetime, username, msg) VALUES (NOW(), new.username, new.srvid);
END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
---- Dumping routines for database 'radius'
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2021-07-02 8:24:05

rm_kickuser TABLE

Save following & import it in radius db 

Example: mysql -uroot -pMYPASS radius < rm_kickuser_SQL_DB_Creation.sql

root@radius-zaib:/temp# cat rm_kickuser_SQL_DB_Creation.sql

-- phpMyAdmin SQL Dump
-- version 3.4.10.1deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 13, 2016 at 10:32 AM
-- Server version: 5.5.46
-- PHP Version: 5.3.10-1ubuntu3.21
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!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 utf8 */;
--
-- Database: `radius`
--
-- --------------------------------------------------------
--
-- Table structure for table `rm_kickuser`
--
CREATE TABLE IF NOT EXISTS `rm_kickuser` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`datetime` datetime NOT NULL,
`username` varchar(64) NOT NULL,
`msg` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=63 ;
--
-- Dumping data for table `rm_kickuser`
--
/*!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 !

Now create the script & schedule it to run every 1 minutes

kick_user.sh

#!/bin/bash
#set -x
# Following script is made specifically for Dmasoftlab radius manager 4.1.x
# When OP disables any user or change service, it will kick the user so that either disconnects, or his package changes on reconnect
# it requires custom trigger on rm_users table, this script will be schedule to run every minute
# Created: 25-MARCH-2019
# Tested on Ubuntu OS Only
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
#################
# CHANGE these
HOSTNAME=`hostname`
SQLID="root"
SQLPASS="MYPASSWORD"
NAS_COA_PORT="1700"
DB="radius"
SRV="mysql"
USR_TABLE="rm_kickuser"
MNT="1"
RADCLIENT="/usr/local/bin/radclient"
#################
#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 "Kick Disabled/Enabled & Service Change - User poller script Started @ $currenttime by the CRON scheduler ... Powered by SYED.JAHANZAIB"
echo "- Script Start Time - $currenttime"
echo "- Checking Disabled/Enabled Users in $USR_TABLE table ..."
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLID --skip-column-names -s -e"
#Table which contain main users information
TMPUSRINFO=/tmp/_users_list.txt
TEMP="/temp"

# Checking if /temp folder is previously present or not . . .
{
if [ ! -d "$TEMP" ]; then
echo
echo "- INFO: $TEMP folder not found, Creating it now to store logs ..."
mkdir $TEMP
else
echo -e "- INFO: $TEMP folder is already present to store logs."
echo
fi
}

KICKUSER_LIST_FILE=$TEMP/kick_users_list.txt
SYSLOG="/var/log/syslog"
> $TMPUSRINFO
# Check if table exists
if [ $($CMD \
"select count(*) from information_schema.tables where \
table_schema='$DB' and table_name='$USR_TABLE';") -eq 1 ]; then
echo "- INFO: $USR_TABLE Table exists ..."
else
echo "- WARNING: $USR_TABLE Table does not exists ..."
fi
# pull user record
$CMD "use $DB; select username from $USR_TABLE WHERE datetime >= NOW() - INTERVAL $MNT MINUTE;" >> $TMPUSRINFO
if [ ! -s $TMPUSRINFO ]
then
endtime=$(date +%H:%M:%S)

echo "
- INFO: No User to KICK found in DMA RADIUS MANAGER TABLE '$USR_TABLE' , Sending EXIT signals ...

- Script Ends Here...
- EXITING peacefully...
- Script End Time - $endtime
"
exit 1
fi
# Apply Count Loop Formula while deleting first line which have junk text
num=0
cat $TMPUSRINFO | while read users
do
num=$[$num+1]
username=`echo $users | awk '{print $1}'`
USER_IP=`echo $users | awk '{print $2}'`
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where username ='$username' AND acctstoptime is NULL;"`
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' ;"`
if [ -z "$ACCTSESID" ]; then
echo "User Found to KICK: USER: $username , BUT USER IS NOT ONLINE, no action is requiroed ..."
else
# Print Info on screen
echo " User Found to KICK: USER: $username , IP: $USER_IP, ID: $ACCTSESID, NAS: $NAS_IP @ $currenttime ... KICKING him now ..."
logger " User Found to KICK: USER: $username , IP: $USER_IP, ID: $ACCTSESID, NAS: $NAS_IP @ $currenttime ... KICKING him now ..."
echo " User Found to KICK: USER: $username , IP: $USER_IP, ID: $ACCTSESID, NAS: $NAS_IP @ $currenttime ... KICKING him now ..." >> $KICKUSER_LIST_FILE
#in below cmd, I am using SSH base method to kick the user because there were some issues in routing & NAS was not accepting radclient packets, you may use the radclient method which is better approach
ssh -p 22 admin@192.168.0.1 /ppp active remove [find name=$username]
#for pppoe , use below
#echo user-name=$username | radclient -x $NAS_IP:$NAS_COA_PORT disconnect $NAS_SECRET
#for hotspot, enable following line
#echo Framed-IP-Address=$USER_IP | radclient -x -c 1 $NAS_IP:$NAS_COA_PORT disconnect $NAS_SECRET
fi
done
# once done, we should delete the tmp files to clear the garbage
rm $TMPUSRINFO

ADDING CRON Entry

*** Schedule the script to run every minute

crontab -e

*/1 * * * * /temp/kickuser.sh > /dev/null 2>&1

Result:

root@radius-zaib:/temp# ./kickuser.sh

- Script Start Time - 08:43:58
- Checking Disabled/Enabled Users in rm_kickuser table ...
- INFO: /temp folder is already present to store logs.
- INFO: rm_kickuser Table exists ...
- INFO: No User to KICK found in DMA RADIUS MANAGER TABLE 'rm_kickuser' , Sending EXIT signals ...

- Script Ends Here...
- EXITING peacefully...
- Script End Time - 08:43:58

 


Regard’s
Syed Jahanzaib

January 19, 2021

November 23, 2019

DMASOFTLAB Radius Manager – Adding custom attribute to facilitate Dynamic address list on Mikrotik

Filed under: Mikrotik Related, Radius Manager — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 3:04 PM

Quick Recipe: If you are using DMA Radius Manager & want to assign Dynamic Address list to a service so that user can automatically be added to dynamic address list under NAS, you can do so by using custom RADIUS attributesunder services section

  • Login to Admin Panel
  • Goto Services
  • Click on your desired service example 4mb
  • Under `Custom RADIUS attributes` , add below attribute
Mikrotik-Address-List := 4mb

adding attribute in radius manager service

Save , & test any user authentication by CMD,

rmauth 127.0.0.1 test 1

freeradius attribute for dma radius manager test via cmd

 

on NAS dynamic address list will be created for each user of this service group. Late ryou can use this address list to mark connections / packets/ routing/queue etc.

4mb pppoe.PNG

Happy Attributing 😉


Regard’s
Jz.

July 23, 2019

RM: Delete Expired Users Record

Filed under: Radius Manager — Tags: — Syed Jahanzaib / Pinochio~:) @ 12:57 PM

expired.jpg


Following script was made for DMA Radius Manager 4.1.x. It can delete X months old Expired users record from the mysql DB.

Sharing for reference purposes …

WordPress is not letting proper pasting of the code …

delete expired uesr.PNG

#!/bin/sh
#set -x
# This script delets users who have expired 2 months ago. and then delete there records from all tables.
# Syed Jahanzaib / June 2019
SQLPASS=”SQLPASS”
export MYSQL_PWD=$SQLPASS
> /tmp/expired.users.txt

#mysql -uroot -e “use radius; select username from rm_users where expiration BETWEEN ‘2010-01-01’ AND ‘2019-04-30’;” |sort > /tmp/expired.users.txt

# Fetch users who have expired 2 months ago & before, (using expired date), BE CAREFUL WHEN USING THIS
mysql -uroot -e “use radius; select username from rm_users where expiration <= DATE_SUB(CURDATE(), INTERVAL 2 MONTH)” |sort > /tmp/expired.users.txt
num=0
cat /tmp/expired.users.txt | while read users
do
num=$[$num+1]
USERNAME=`echo $users | awk ‘{print $1}’`
echo “$USERNAME —- user record from all relevant tables”
mysql -uroot -e “use radius; DELETE FROM rm_cards WHERE cardnum = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM rm_users WHERE username = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM rm_changesrv WHERE username = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM radcheck WHERE username = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM radacct WHERE username = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM rm_radacct WHERE username = ‘$USERNAME’;”
done

 

Jz

April 22, 2019

MySql Database Recovery from Raw Files

Filed under: Linux Related, Radius Manager — Tags: , , , , , — Syed Jahanzaib / Pinochio~:) @ 2:31 PM

mysql recovery.PNG


Disclaimer: This worked under particular case. It may or may not work for everyone.

Scenario:

OS: Ubuntu 12.4 Servedit Edition / x86

MYSQL: Ver 14.14 Distrib 5.5.54, for debian-linux-gnu (i686) using readline 6.2

The OP was running radius for AAA. The disk got faulty for some unknown reasons and were unable to boot from it. There was no database backup [Real example of bad practices] So restoration from mysqldump to new system was not an option there !

Requirements:

We need to restore the Database using mysql raw files. Luckily the faulty disk was able to got attached to other system & we were able to copy the core /var/lib/mysql/ folders (along with all sub folders in it)


Quick & Dirty Restoration Step !

Requires some good level of Linux / DB knowledge]

  • Setup a test SANDBOX, Install same level of OS along with MYSQL on new system/disk. Create databases / tables as required. Verify all is working by logging to mysql
  • Stop the MYSQL service.
  • Copy the folder /var/lib/mysql [copied from faulty disk] to this new box under /var/lib/mysql/  
  • Set the permission on newly copied files/folders
    chown mysql -R /var/lib/mysql/

After this point Try to start the MYSQL service , IF it starts successfully & you can see your DATA , then skip below steps , ELSE continue through below steps …

  • Edit the /etc/mysql/my.cnf & add following line under [mysqld] section
    innodb_force_recovery = 6
  • Start MYSQL service & the service will start in Safe Mode with limited working support. Verify if you can able to login to MYSQL service by
    mysql -uroot -pPASS
  • If above step works, Export the Database backup using mysqldump cmd e.g:
    mysqldump -uroot -pSQLPASS   radius  >  radius_db_dump_.sql
  • Once done, Open the file in nano or any other text editor, & verify if it contains the required data.

Now copy the radius_db_dump_.sql to safe location & you know what to do next 🙂

  • Import this mysqldump file to your working radius system !

TIPS:

best-practice2

Make sure you have multistage backup strategies in place for any mission critical server.

Example for mysql Database, You can do following

  • If your server is VM, then VEEAM B&R will be your best friend & guardian, go for it
  • 1st Stage Backup: [Highly recommended for live replication]
    ideally, you should have at least 2 Replica servers & configure either Master-Master or Master-Slave Replication
  • 2nd Stage backup:
    Create bash scripts to export DB backup in local folder on a daily basis, (or hourly basis if required]
  • 3rd Stage backup:
    Attach external USB disk to the server, and in your backup script, add this usb as additional backup repository
  • 4th Stage backup:
    Configure DROPBOX and add it as additional backup repository
  • 5th Stage backup:
    The admin should manually copy the backup folders to his desktop so that if all other backups gets failed , this should come in handy.

Regard’s
Syed Jahanzaib

 

 

 

March 25, 2019

Mikrotik with Freeradius/mySQL # Part-21 – Weird Trigger for Duplicate Users

Filed under: Mikrotik Related, Radius Manager — Tags: , — Syed Jahanzaib / Pinochio~:) @ 11:13 AM

dup user.jpg

fre



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.

Regard’s
Syed Jahanzaib~


Scenario:

  • We have DMASOFTLAB radius manager installed as a billing system in Ubuntu 12.04 server
  • Mikrotik version 6.4x.x is acting as Hotspot NAS and connected with radius for AAA

Requirement: [A Weird one really]

As operator demanded

“We are running Hotspot on mikrotik, & client login to hotspot using his mobile/laptop. If logged-in client leaves his primary location without logout, & move to another location, & if he try to login from another device, his request will gets DENY because of Single user limit. We increased it to 2 by using SIM-USE=2 directive in user properties,It allows second session to login, but both sessions can use the bandwidth, therefore we want that once second session is established its old first live session should get kicked. If it was single Hotspot we could have used the script on LOGIN, but there are several NAS spreaded across various location using single radius.”

if the user uses same device then we could have used

if (User-Name){
if("%{sql:UPDATE radacct set AcctStopTime=ADDDATE(AcctStartTime,INTERVAL AcctSessionTime SECOND), AcctTerminateCause='Clear-Stale Session' WHERE UserName='%{User-Name}' and CallingStationId='%{Calling-Station-Id}' and AcctStopTime is NULL}"){
}
}

but things are different in hotspot as I have observed, if devices are different then it will give us already logged-in error, if we use sim-use=2 then second device can be logged-in but old session will also be alive and both ids will suck the bandwidth at a time.

Also using idle-timeout or keep-alive timeout is the simplest way to achieve this , but for some weird reasons and to avoid long arguments dueto accent issues, I made one customized solution for the operator.


Solution:

Login to mysql with root

mysql -uroot -pXXXX

and switch to radius database

use radius;

Now create new table that will hold duplicate users record

MYSQL Table to hold duplicate users list


--
-- Table structure for table `rm_dupusers`
--

DROP TABLE IF EXISTS `rm_dupusers`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `rm_dupusers` (
`dupid` int(9) NOT NULL AUTO_INCREMENT,
`datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`username` varchar(64) NOT NULL,
`ip` varchar(16) NOT NULL,
`nas` varchar(16) NOT NULL,
`comments` varchar(64) DEFAULT NULL,
KEY `dupid` (`dupid`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `rm_dupusers`
--

MYSQL TRIGGER to check duplicate users sessions

Now we will create a new Trigger that will be executed when any record is inserted in radacct, it will check for existing duplicate session of user and if it found , it will add its entry in the mysql table of rm_dupusers

drop trigger chk_dup_user;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `chk_dup_user` BEFORE INSERT ON `radacct` FOR EACH ROW BEGIN
SET @dupuserchk = (SELECT count(*) from radacct where username=New.username and acctstoptime is NULL);
IF (@dupuserchk = 1) THEN
SET @dupusername = (SELECT username from radacct where username=New.username and acctstoptime is NULL);
SET @dupuserip = (SELECT framedipaddress from radacct where username=New.username and acctstoptime is NULL);
SET @dupusernas = (SELECT nasipaddress from radacct where username=New.username and acctstoptime is NULL);
INSERT into rm_dupusers (dupid,username,ip,nas,comments) values ('',@dupusername,@dupuserip,@dupusernas,'Duplicate User');
END IF;
END */;;
DELIMITER ;

Mysql Part is Done.

Now we will create a BASH script that will scheduled to run every minute.

BASH script !

Create bash script in desired folder, in this example I am using /temp folder as default

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

& paste following, make sure to modify credentials

#!/bin/bash
#set -x
# Following script is made specifically for Dmasoftlab radius manager 4.1.x
# When any new user will login, it will simply check if exists session of same user found, it will kick previous session
# it requires custom trigger on radacct table, this script will be schedule to run every minute
# Created: 25-MARCH-2019
# Tested on Ubuntu OS Only
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
#################
# CHANGE these
HOSTNAME=`hostname`
SQLID="root"
SQLPASS="XXXXXX"
NAS_COA_PORT="1700"
DB="radius"
SRV="mysql"
DUP_TABLE="rm_dupusers"
INT="1"
RADCLIENT="/usr/local/bin/radclient"
#################
#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 "Duplicate User poller script Started @ $currenttime by the CRON scheduler ... Powered by SYED.JAHANZAIB"
echo "- Script Start Time - $currenttime"
echo "- Checking Duplicate Users in $DUP_TABLE table ..."
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLID --skip-column-names -s -e"
#Table which contain main users information
TMPUSRINFO=/tmp/userpass.txt
TEMP="/temp"

# Checking if /temp folder is previously present or not . . .
{
if [ ! -d "$TEMP" ]; then
echo
echo "- INFO: $TEMP folder not found, Creating it now to store logs ..."
mkdir $TEMP
else
echo -e "- INFO: $TEMP folder is already present to store logs."
echo
fi
}

DUP_LIST_FILE=$TEMP/duplicate_users_list.txt
SYSLOG="/var/log/syslog"
> $TMPUSRINFO

# KANNEL DETAILS
KHOST="127.0.0.1:13013"
KID="kannel"
KPASS="KANNEL_PASSWORD"

IPADD=`ip route get 1 | awk '{print $NF;exit}'`
SRVSTATUS=`service $SRV status |grep running |wc -l`
if [ "$SRVSTATUS" -ne 1 ];
#if [ -z "$SRVSTATUS" ];
then
echo "- ALERT: $HOSTNAME - $IPADD - $SRV NOT RESPONDING CHECK - $DATE $DT .Exiting ..."
echo "- ALERT: $HOSTNAME - $IPADD - $SRV NOT RESPONDING CHECK - $DATE $DT .Exiting ..." >> $SYSLOG
echo "- ALERT:

- $HOSTNAME
- $IPADD
- $SRV not responding ***
- $currenttime

Exiting ..."
exit 1
else
echo "- INFO: $SRV service is accessible. Proceeding further ... OK"
fi

# Check if table exists
if [ $($CMD \
"select count(*) from information_schema.tables where \
table_schema='$DB' and table_name='$DUP_TABLE';") -eq 1 ]; then
echo "- INFO: $DUP_TABLE Table exists ..."
else
echo "- WARNING: $DUP_TABLE Table does not exists ..."
fi
########
########
# Enable following line so that it will update all users simultanous-use to '2' so that two sessions can be established
# $CMD "use $DB; UPDATE  radius.radcheck SET value = '2' where Attribute = 'Simultaneous-Use';
#######
#######
# pull user record
$CMD "use $DB; select username,ip,nas from $DUP_TABLE WHERE datetime >= NOW() - INTERVAL $INT MINUTE;" >> $TMPUSRINFO
if [ ! -s $TMPUSRINFO ]
then
endtime=$(date +%H:%M:%S)

echo "
- INFO: No Duplicate User found in DMA RADIUS MANAGER TABLE '$DUP_TABLE' , Sending EXIT signals ...

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

# Apply Count Loop Formula while deleting first line which have junk text
num=0
cat $TMPUSRINFO | while read users
do
num=$[$num+1]
username=`echo $users | awk '{print $1}'`
USER_IP=`echo $users | awk '{print $2}'`
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where framedipaddress ='$USER_IP' AND acctstoptime is NULL;"`
NAS_IP=`echo $users | awk '{print $3}'`
NAS_SECRET=`$CMD "use $DB; select secret from nas where nasname = '$NAS_IP' ;"`

# Print Info on screen
echo "Duplicate User Found: USER: $username , IP: $USER_IP, ID: $ACCTSESID, $NAS: $NAS+IP @ $currenttime ... KICKING him now ..."
echo "Duplicate User Found: USER: $username , IP: $USER_IP, ID: $ACCTSESID, $NAS: $NAS+IP @ $currenttime ... KICKING him now ..." >> $DUP_LIST_FILE
#echo User-Name=$USERNAME,Acct-Session-Id=$ACCTSESID,Framed-IP-Address=$USER_IP,Mikrotik-Rate-Limit=\"$DN_BWPKG\" | $RADCLIENT -q -c 1 $NAS_IP:$NAS_COA_PORT coa $NAS_SECRET
#for hotspot, enable following line
echo Framed-IP-Address=$USER_IP | radclient -x -c 1 $NAS_IP:$NAS_COA_PORT disconnect $NAS_SECRET
done
# once done, we should delete the tmp files to clear the garbage
rm $TMPUSRINFO

CRON scheduler to run the above script every minute. Edit crontab by

crontab -e

& add following entry

* * * * * /temp/kickdupuser.sh >/dev/null 2>&1

Testing …

Using same credentials, Login to first device, and then on second ,

& run this script,

root@radius:/temp# /temp/kickdupuser.sh
- Script Start Time - 10:52:03
- Checking Duplicate Users in rm_dupusers table ...
- INFO: /temp folder is already present to store logs.
- INFO: mysql service is accessible. Proceeding further ... OK
- INFO: rm_dupusers Table exists ...
Duplicate User Found: USER: test , IP: 172.16.0.253, ID: 81d00057, : +IP @ 10:52:03 ... KICKING him now ...
Sending Disconnect-Request of id 58 to 10.0.0.1 port 1700
Framed-IP-Address = 172.16.0.253
rad_recv: Disconnect-ACK packet from host 10.0.0.1 port 1700, id=58, length=32
NAS-Identifier = "ZAIB_CCR_GW"
root@radius:/temp#

older session will be removed

radclient dc the first user.PNG


Weirdo …. but its fun to learn !

TIPS:

Command to view duplicate users session in freeradius using CLI


mysql -uroot -pMYPASS --skip-column-names -e 'use radius; SELECT username FROM radacct WHERE acctstoptime IS NULL;' > 1.txt && sort 1.txt | uniq -cd

Regard’s
Syed Jahanzaib

July 17, 2018

August 15, 2017

Enable `radpostauth` table for Radius Manager

Filed under: Radius Manager — Syed Jahanzaib / Pinochio~:) @ 3:40 PM

Following post was tested with DMA,

For better approach you may want to see following …

Freeradius External Auth BASH Script & RADPOSTUATH logging with customized reply message !


Freeradius is a well known billing system which is commonly used by ISP’s worldwide due to its reliability,  highly customizable and versatility. Many 3rd party vendors have made some good GUI fronted to manage the FR back-end engine.

It also sues mysql to facilitate logging of various users details. One of the table called radpsotauth which can hold information about users successfull/failed login attempts. Using this table, we can compliment our own GUI or 3rd party fronted (for easy management of freeradius engine) like DMASoftlab radius manager Authentication Log section so that admin / support can see users authentication logs. With some modification we can add useful information for quick troubleshooting example why user denied the authentication request, either invalid mac, wrong password, or account expired.

Note to *.*

  • You can add UNLAG query as well to apply IF statement, and add REPLY result according to your requirements.
  • This post was written for some reference purposes & will be updated as per request.
  • This guide is incomplete post. But it can be used as a reference as well for future retrieval

Example:

showing auth logs with errors numbers.JPG

As we can see in above image that in Radius Reply column, it clearly showing why user is denied like invalid mac address , account expires, invalid service reference (when user account id disabled in dma).

To enable these features we have to perform few steps as following …

 


RADDB DEFAULT CONFIG

First we need to edit the default sites config file for raddb.

Edit following file

nano /usr/local/etc/raddb/sites-available/default

Now search for “post-auth {” section

To make it simple and copy-paste format, Use following


post-auth {
sqlippool
reply_log
exec
sql
Post-Auth-Type REJECT {
sql
attr_filter.access_reject
}
}

As showed in the image below …

psot-auth section

Save & Exit.


#DIALUP.CONF Section

Edit the post-auth section in /usr/local/etc/raddb/sql/mysql/dialup.conf file

At the end of this file you will see “postauth_query” section. You need to change it

Old post-auth query

old-dialup

After changing

new-dialup

or copy paste text as below…


#######################################################################
# Authentication Logging Queries
#######################################################################
# postauth_query - Insert some info after authentication
#######################################################################

postauth_query = "INSERT INTO ${postauth_table} \
(username, pass, reply, authdate, nasipaddress, mac) \
VALUES ( \
'%{User-Name}', \
'%{%{User-Password}:-%{Chap-Password}}', \
'%{reply:Packet-Type} - %{reply:Reply-Message}', \
'%S', \
'%{NAS-IP-Address}', \
'%{Calling-Station-Id}')"

Save & Exit.


#Alter the RADPOSTAUTH table using mysql cmd …

Using mysql cmd, we will perform 2 functions

  1. Increase the REPLY column length to accommodate longer reply messages display properly
  2. Add the MAC Address column so we can detect the calling user device mac address
mysql -uroot -pPASSWORD
use radius;
ALTER TABLE radpostauth MODIFY `reply` VARCHAR( 100 );
ALTER TABLE radpostauth ADD COLUMN mac TEXT;
exit

Restart the RADIUSD service

service radiusd restart

using CMD, you can now see the authentication log table

mysql -uroot -pPASSWORD -e “use radius; select * from radpostauth;”

& you will see the information

phpmyadmin query for table

1 JOHN     123456     Access-Reject - The account has expired=21      2017-08-15 [14:14:05       192.168.1.1         10:FE:ED:33:BD:AX

Notes:

  • You can modify the messages appearing in the different columns, you can add your own customized columns as well like called station, or others
  • You can add UNLAG query as well to apply IF statement, and add REPLY result according to your requirements.
  • It can chew up your disk space, so try to make text shortest possible, like error codes only, not the whole text.
  • scheduled a cron job so that it can empty the table on weekly/monthly basis so that it may not swallow disk space plus performance should remain optimal.
  • If used in heavy environment it can put considerable strain on your system resources by putting large quantity of mysql INSERT queries into the table.

 

 


Regard’s
Syed Jahanzaib

 

June 7, 2017

Generating Refill Cards in Radius Manager

Filed under: Radius Manager — Tags: , , , — Syed Jahanzaib / Pinochio~:) @ 2:29 PM

On Demand Guide!

DMASoftlab Radius Manager is a nice billing system for any ISP. It works on Linux base OS like Ubuntu / Centos etc. It uses Free-radius as back-end engine and PHP as fronted GUI. It provides variety of ways to manage users. You can manually create users . Or it also provide self registration service for user via registration option where user can sign up, choose his Id / Package & renew it using refill card.

Radius manager provides 2 type of Cards system.

  1. Prepaid Cards
  2. Refill Cards [Scratch card]

 

1- Prepaid cards are simpler as they contains username / password , and starts counting expiry/quota after first usage / login from user. they also binds with selected service. This is suitable for environment where users join.leave very frequently, like Cafe’s / restaurants / public hotspot places.

2- Refill cards does not contains any user name or password. they contains only amount, which user can deposit in his account himself by login to the UCP , user control panel and use redeem voucher option). Refill cards are useful if you have more permanent type of clients who renews on monthly basis. Using refill cards, you can providers with liberty to change his service on his own if required. off course he would required higher amount of refill card to renew his new upscale service. but user can do it all on his own if required. so its kind of fully automatic system as well.

Refill cards does not binds with any specific service, they contains only AMOUNT. Once the user add deposit using refill card code, his account wont get auto renewed (although we can use script for it) . He have to activate the service as well after depositing amount. So in short user have to perform 2 steps in order to renew his account. Using refill cards, you just have to only sell cards to user, rest user will maintain on his own.

Example:

We have created a service with 1mb speed, 15gb quota & 30 days expiry limit. We want to create refill cards so that we sell refill card to user, and user will renew his account by using the portal.

Service Example:

2- services


To create refill cards, Goto Card System / Generate Cards

1- refill card menu

 

3- refill generation process

To view these generated cards, Goto Cards System  / List Refill Cards

view refill

As you can see in above screenshot, it have generated cards. You can view it in CSV which will contains cards details in the text format (for local customized card printing in bulk), and if you click on PDF button, it will show you cards in ready to print format using PDF viewer As showed in the image below

refill card in pdf

Now you can sell this card to users and they can use it from there user panel called UCP.


Using REFILL card to renew account.

From user end, login to user portal, and you will see something like this for new / expired user

1- user.PNG

As we can see that user account is in expired state, to renew it ,

Goto REDEEM VOUCHER & enter the code of refill card.

2- refill number

Once submit, it will add 100 Rs deposit in user account. Goto HOME tab and you will see following

deposite

Notice that now user have 100 rs in DEPOSIT but the account is still in EXPIRED status. We will use this deposit amount to renew / activate the account.

Goto Purchase credits, and select 1 in AMOUNT field. As showed in the image below …

1- purchase credits

 

2- refill done

 

3- refill done

Go back to HOME tabe

4- done

Account is now active, and user can login by hotspot / ppp or whatever auth method you have in your mikrotik or other NAS.

You can also refill cards by sending SMS to the radius system connected gsm modem. You will be requiring KANNEL along with Play SMS as well. I shared the idea here.

https://aacable.wordpress.com/2015/05/25/sharing-ideas-renew-expired-user-account-via-sms-in-dmasoftlab-radius-manager/

reill-with-no-quota-showing-it


 

Done.

 

Older Posts »

%d bloggers like this: