Send Expiry Alert via SMS/Email For Freeradius/Mysql Users

As some one asked me on howto send sms (or possibly email) to users whose expiry is after XX days in freeradius/mysql base billing system, Here is a simple script to do the task. It’s not very elegant way to achieve the task but since I donot have any programming level experience so this is how achieve it some Desi style coding 🙂 & the good part is , It’s doing the job and you can at least get some ideas from the code.

So basically this post is just another Sharing Idea’s Series …

---

 

Requirements:

• You must have working billing system in freeradius/mysql with the appropriate tables like radius, username, expiration etc.

---

 

In this example I used Radius Manager base system which also uses FREERADIUS/MYSQL as its backend DB. Radius Manager already have expiry alerts notification in its core configurable via web panel, but its a 3rd party paid application. So I am showing you a way howto achieve the same with your own billing system.

So basically what I did was to simply ran mysql query which pulled user name and mobile number from the table [mobile number column must be be created with appropriate values] and exported it to local file. Then I applied a simple ‘Loop‘ formula to go through this file and then applied appropriate action in the end like send SMS via mobile / usb modem attached , use any external http Gateway , or send EMAIL.

You can use this logic to achieve the results on about any other billing system (which is open source or readable) OR any other purposes as well.

Just Go through this script ,its very simple, modify it as per your network and setup. If you manage to add some enhancements, do post here for the sake of every one. :~)

I will add some more details later.

Happy Alerting !

Syed Jahanzaib

---

Create SMS Script

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

Now paste the following script

#!/bin/sh
#set -x
# BASH base SMS script for sending expiry notification for radius Manager users
# 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
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
##############
# CHANGE these
##############
COMPANY="YOUR_COMPANY_NAME"
SQLID="root"
SQLPASS="SQLPASS"
DB="radius"
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLID --skip-column-names -s -e"
#Table which contain main users information
USER_TABLE="rm_users"
SRV_TABLE="rm_services"
# Interval before alert which should be sent to user before this number days
EXPIRY_DAYS_REMAIN="3"
# Kannel Server IP, if its local leave it as it is
KURL="http://127.0.0.1:13013"
KID="kannel"
KPASS="KANNELPASS"

###################
#Script starts here
###################
TMP="/tmp/$EXPIRY_DAYS_REMAIN.days.expiring_users_list.txt"
> $TMP
#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 "Checking & sending SMS to near $EXPIRY_DAYS_REMAIN days expiry users, a reminder - Last executed @ $currenttime by the CRON scheduler ... Powered by SYED.JAHANZAIB"

# Export usernames and mobile from the mysql table in a file, which Expiry is after X days
$CMD "use $DB; SELECT username FROM $USER_TABLE WHERE expiration = DATE_ADD(CURDATE(), INTERVAL $EXPIRY_DAYS_REMAIN DAY);" > $TMP
TOTUSER=`cat $TMP | wc -l`
if [ ! -s $TMP ]
then
endtime=$(date +%H:%M:%S)
echo "- WARNING: No users found near expiration (that is $EXPIRY_DAYS_REMAIN Days)
- Script Ends Here
- EXITING peacefully...
"
exit 1
fi

TOTUSR=`cat $TMP | wc -l`
echo "- INFO: Total number of users with near expiry = $TOTUSR / No.s ...

"

# Apply Count Loop Formula while deleting first line which have simple text, and also any line which dont have mobile number [in second column]
num=0
cat $TMP | 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';"`
CREDITS=`$CMD "use $DB; select credits from $USER_TABLE where username ='$USERNAME';" | cut -f1 -d"."`
MOBILE=`$CMD "use $DB; select mobile from $USER_TABLE where username ='$USERNAME';"`
EMAIL=`$CMD "use $DB; select email from $USER_TABLE where username ='$USERNAME';"`
EXPIRATION_DATE=`$CMD "use $DB; select expiration from $USER_TABLE where username ='$USERNAME';" |sed 's/00:00:00//g'`

# If there is balance/credit in user account then dont send alert,
if [ "$CREDITS" -gt 1 ];then
echo "$USERNAME - This user will expire on $EXPIRATION_DATE BUT he/she have deposit/credit in his/her account, therefore No need to send alert ..."
# Else Send him Email/SMS alert
else
# Store message in temporary holder
echo "$USERNAME - This user will expire on $EXPIRATION_DATE & he/she have NO deposit/credit, therefore sending email/sms alerts ..."
echo "Dear $USERNAME, Your internet account will expire on $EXPIRATION_DATE
$COMPANY" > /tmp/$USERNAME.$EXPIRY_DAYS_REMAIN.days.near.expiry.sms.alert.txt
#cat /tmp/$USERNAME.$EXPIRY_DAYS_REMAIN.days.near.expiry.sms.alert.txt
#echo "Sending EMAIL & SMS ALERT to mobile - $MOBILE / email - $EMAIL ..."

# Send SMS Alert via KANNEL
#curl "$KURL/cgi-bin/sendsms?username=$KID&password=$KPASS&to=$MOBILE" -G --data-urlencode text@/tmp/$USERNAME.$EXPIRY_DAYS_REMAIN.days.near.expiry.sms.alert.txt
# Send EMAIL message
#sendemail -u "$USERNAME account will expire on $EXPIRATION_DATE " -o tls=yes -s smtp.gmail.com:587 -t $EMAIL -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=/tmp/$USERNAME.$EXPIRY_DAYS_REMAIN.days.near.expiry.sms.alert.txt -o message-content-type=text
# Remove leftover entries
rm /tmp/$USERNAME.$EXPIRY_DAYS_REMAIN.days.near.expiry.sms.alert.txt
fi
done
# Script Ends here 

---

 

OUTPUT:

[Just echoing in this example]

Run the script manually for test purposes and you should then be able to see something like if you already have proper billing configured with enough data. Below example is a working radius system showing accounts with mobile numbers which will expire in next 3 days. We can show more info if required.

 

---

 

Schedule to run it DAILY

You can schedule it to run on daily basis so it can check for accounts expiring on next xx days and take appropriate action as required.

Example of scheduled job by ‘crontab‘ command:

crontab -l

@daily /temp/sms.sh

With above code, this script will run daily at 00:00 hours [in night] daily. Then it will search for accounts whose account will expire after 3 days, then it will take defined action.

Jz!