Syed Jahanzaib Personal Blog to Share Knowledge !

June 8, 2015

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

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




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



  • 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/
chmod +x /temp/
nano /temp/

Now paste the following script

# BASH base SMS script for sending expiry notification for Freeradius/mysql 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
# Created on : 8th June, 2015

# Interval before alert which should be sent to user before this number days

# Export usernames and mobile from the mysql table in a file,  which Expiry is after 3 days
mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT username,mobile FROM radius.rm_users  WHERE expiration = DATE_ADD(CURDATE(), INTERVAL $EXPIRY DAY);" > /tmp/list

# Apply Count Loop Formula while deleting first line which have simple text, and also any line which dont have mobile number [in second column]
cat /tmp/list |sed '1d' |awk 'NF > 1' | while read users
username=`echo $users |awk '{print $1}'`
mobile=`echo $users | awk '{print $2}'`

# Add action like send sms or email as per required or designed / zaib
# Here I am just echoing , You must change this if you want some action liek sms or mail as showed in the end
echo "Dear $username, Your account will expire after 3 days. Your cell is $mobile"

# gammu sendsms TEXT $mobile -text "Dear $username, Your account will expire after 3 days / ABC ISP"

# KANNEL SMS HTTP GATEWAY Example, is kannel server ip
# curl "$mobile&text=Dear+$username+Your+account+will+expire+after+3+days++ABC+ISP

# Email Example using -mail- tool
# mail -s 'Dear $username, Your account will expire after 3 days / ABC ISP' $email




[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 bycrontabcommand:

crontab -l

@daily /temp/

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.





  1. This is not completely solution


    Comment by — June 8, 2015 @ 5:09 PM

  2. sir i have radius manager in hyderabad location and mikrotik installed by 200km long
    in location how can i connect operate this mikrotik.


    Comment by ramanji neyuluk — June 12, 2015 @ 5:48 PM

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: