DMA Radius Manager: BASH script for sending SMS upon renewal & new user registration

~! Personnel Notes: For future retrieval of the code !~

---

Post Index:

1. User Account Renewal SMS script in DMA – Manual Method
2. New User Welcome SMS script – Manual Method [old but posted in july 2019]

---

 

1- User Account Renewal SMS script in DMA – Manual Method

 

second version:

---

Task:

DMASOFTLAB Radius Manager have the limited facility to send sms on different events like account creation welcome msg, expiry, password retrieval.

Radius Manager  send following SMS upon new account creation

“Welcome to our system! Your account name is {USERNAME}, password is {PASSWORD}”

But the OP wanted to send some customized SMS in different time with few other info as well like login details, upon every account renewal (which RM does not support).

+ the system should be able to detect that if the account is registered today, then it should send WELCOME message along with details, BUT if the account is old and only it get renewed, then it should send RENEWAL message.

---

 

 

Solution:

First you need to modify the DATE type to DATETIME in rm_invoices table, you can use phpmyadmin to do the task easily, or use the command as follows:

login to mysql and issue following commands

use radius;
ALTER TABLE `rm_invoices` CHANGE `date` `date` DATETIME NOT NULL ;

Now you can use following script.

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

 

---

SCRIPT # 1

Add following data in the scrip

 

#!/bin/sh
# set -x
# BASH base SMS/EMAIL script for NEW ACCOUNT / RENEWAL notification for RADIUS MANAGER based on Freeradius/mysql
# 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 / aacable at hotmail dot com
# CREATED on : 16th July, 2015
# Modified on 14thth DEC, 2015

SQLUSER="root"
SQLPASS="PASSWORD"
MNT="5"
CURDATE=`date`
KHOST="http://127.0.0.1:13013/cgi-bin"
KID="kannel"
KPASS="KANNELPASS"
GMAILID="GMAILID@gmail.com"
GMAILPASS="GMAILPASS"
ADMINMAIL="TO-ADDRESS@hotmail.com"
COMPANY="zaib+TECH+Pvt+Ltd."
COMPANY2="ZAIB TECH Pvt Ltd."
# Setting Date as variable
TODAY=$(date +"%Y-%m-%d")
# Removing DASH from date to use it in compare formula later
TODAYDIGIT=`echo $TODAY  | sed -e 's/-//g'`
TMPUSRINFO=/tmp/tmpuserinfo
#> /var/log/renewal.log
> $TMPUSRINFO
rm -fr /tmp/*.sms

# Simply print the info for manualy executed this script
mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT rm_invoices.username, rm_invoices.paid, rm_users.createdon, rm_invoices.expiration, rm_users.mobile, rm_users.firstname, rm_users.lastname, rm_users.email FROM rm_invoices INNER JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= NOW() - INTERVAL $MNT MINUTE  AND (paymode = '0' ) AND (invgroup = '0'  OR invgroup = '1' );"

# Check User Validation, if not found exit with error , else continue
USRVALID=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT rm_invoices.username, rm_invoices.paid, rm_users.createdon, rm_invoices.expiration, rm_users.mobile FROM rm_invoices INNER JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= NOW() - INTERVAL $MNT MINUTE  AND (paymode = '0' ) AND (invgroup = '0'  OR invgroup = '1' );"`
if [ ! -n "$USRVALID" ]; then
echo  "No account have been updated in last $MNT minutes ! Powered by SYED JAHANZAIB"
exit 0
fi

# Fetch user account details which were created in last 5 minutes from rm tables using inner joing function in mysql
mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT rm_invoices.username, rm_invoices.paid, rm_users.createdon, rm_invoices.expiration, rm_users.mobile, rm_users.firstname, rm_users.lastname, rm_users.email FROM rm_invoices INNER JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= NOW() - INTERVAL $MNT MINUTE  AND (paymode = '0' ) AND (invgroup = '0'  OR invgroup = '1' );" > $TMPUSRINFO

# Apply Count Loop Formula while deleting first line which have junk text
num=0
cat $TMPUSRINFO |sed '1d' | while read users
do
num=$[$num+1]
username=`echo $users | awk '{print $1}'`
paidwod=`echo $users | awk '{print $2}' | sed -e 's/-//g'`
paid=`echo $users | awk '{print $2}'`
cratedwod=`echo $users | awk '{print $3}' | sed -e 's/-//g'`
crated=`echo $users | awk '{print $3}'`
expiration=`echo $users | awk '{print $4}'`
mobile=`echo $users | awk '{print $5}'`
firstname=`echo $users | awk '{print $6}'`
lastname=`echo $users | awk '{print $7}'`
email=`echo $users | awk '{print $8}'`

#Print Service ID for SPECIFIC_USER via CLI
SRVID=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvid FROM radius.rm_users WHERE rm_users.username = '$username';" |awk 'FNR == 2 {print $1}'`

# Print Package Name of current service via CLI
PKGNAME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvname FROM radius.rm_services WHERE rm_services.srvid = '$SRVID';" |awk 'FNR == 2'`

#Print Password
PASSWORD=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; select * from radcheck order by UserName;" |grep $username | awk 'NR==1' | awk '{print $5}'`

# Use following if you want to send SMS / NEW USER NOTIFICAITON
# Or just ECHO

echo "Dear $firstname $lastname,
Your Internet account details.

Payment Date = $paid
User ID = $username
Package = $PKGNAME
Expiration Date = $expiration
For furhter information & support you may contact your local Dealer.

$COMPANY2
Powered by Syed.Jahanzaib" >> /tmp/$username.sms

curl "$KHOST/sendsms?username=$KID&password=$KPASS&to=$mobile" -G --data-urlencode text@/tmp/$username.sms
sleep 10

# If you want to send email , use below ...
/temp/sendEmail-v1.56/sendEmail -t $email -u "Dear $firstname $lastname : Your Internet Service Activation/Renewal Details as of $CURDATE" -o tls=yes -s smtp.gmail.com:587 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=/tmp/$username.sms  -o message-content-type=text

done

 

---

FOR EMAIL ALERTS, Download sendEmail tool to send email alerts

Install sendEmail Tool

wget http://caspian.dotconf.net/menu/Software/SendEmail/sendEmail-v1.56.tar.gz

tar zxvf sendEmail-v1.56.tar.gz

cd sendEmail-v1.56/

ADD SUPPORTING LIBRARY

for ubuntu

apt-get -y install libio-socket-ssl-perl libnet-ssleay-perl perl

for centos

yum -y install perl perl-Crypt-SSLeay perl-IO-Socket-SSL

 

EMAIL CONFIGURATION AND SAMPLE RESULTS OF RECEIVING

Try to send email using command line , make sure you fill the variables with your original values like mail address password etc: Example

/temp/sendEmail-v1.56/sendEmail -t $TO1 -u “Test Email” -s smtp.gmail.com:587 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o tls=yes -m “hi”

 

---

2- New User Welcome SMS script – Manual Method [old but posted in july 2019]

This script will send SMS to newly registered users

#!/usr/bin/env bash
####!/bin/sh
#set -x
#trap "set +x; set -x" DEBUG
# BASH base script to send WELCOME SMS to new radius users [in DMA]
# 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 25h June 2018

# Local Variables
# Mysql credentials
SQLID="root"
SQLPASS="MYSQLPASSWORD"
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLID --skip-column-names -s -e"
COMPANY="zaibisp"
DB="radius"
#Table which contain main users information
TBL="rm_users"
CREATION="5"
# Date Time Variables
FULL_DATE=`date`
TODAY=$(date +"%Y-%m-%d")
TOMORROW1=`date -d "+1 days" '+%Y-%m-%d'`
TOMORROW2=`date -d "+1 days" '+%Y-%m-%d'`
# Kannel Server IP, if its local leave it as it is
KURL="http://127.0.0.1:13013"
KID="kannel"
KPASS="fawwa"

##################################################
# Temp folder and files setup for various actions
##################################################
TEMP="tmp"
# Temp holders to store users list
NEWUSERLIST=/$TEMP/new_users_list_4_sms.txt

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

{
if [ -f $NEWUSERLIST ]; then
rm $NEWUSERLIST
touch $NEWUSERLIST
fi
}

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

# Pull users that are expiring TODAY
$CMD "use $DB; select data1 from rm_syslog where eventid ='0' and datetime >= NOW() - INTERVAL $CREATION MINUTE;" |sort > $NEWUSERLIST

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

TODAY_EXP_TOTAL=`cat $NEWUSERLIST | wc -l`
echo "- Total Number of users Expiring tomorrow: $TODAY_EXP_TOTAL"
echo "- Now sending sms to epxired users"
#sleep 5

# Apply formula
num=0
cat $NEWUSERLIST |while read data
do
num=$[$num+1]
USERNAME=`echo $data`
MOBILE=`$CMD "use $DB; select mobile from $TBL where username='$USERNAME';" | sed -e 's/-//g'`
FULLNAME=`$CMD "use $DB; select firstname,lastname from $TBL where username='$USERNAME';"`
EXPIRATION=`$CMD "use $DB; select expiration from $TBL where username='$USERNAME';" | awk '{print $1}'`
SRVID=`$CMD "use $DB; select srvid from $TBL where username='$USERNAME';"`
SRVNAME=`$CMD "use $DB; select srvname from rm_services where srvid='$SRVID';"`

########### ACCOUNT STATUS EXPIRED TODAY ACTION / SEND SMS OR EMAIL ############
# PRINT FETCHED VALUES into files, which we will use later to send sms using url encoded command of kannel
if [ -z "$MOBILE" ]; then
echo "$USERNAME does not have mobile number associated therefore no need to to send sms"
else
#####################
#### SMS MESSAGE ####
#####################
echo "Dear Mr. $FULLNAME,
Your internet service have been activated.
Account ID [$USERNAME]
For further information & support please contact your local dealer.

Powered by $COMPANY" > /tmp/$USERNAME.new.account.sms
cat /tmp/$USERNAME.new.account.sms

# Send SMS Alert via KANNEL
curl "$KURL/cgi-bin/sendsms?username=$KID&password=$KPASS&to=$MOBILE" -G --data-urlencode text@/tmp/$USERNAME.new.account.sms
echo "
SMS Sent to ID: $USERNAME - $FULLNAME $MOBILE , waiting 60 seconds for another SMS ...
"
logger NEW Account SMS Sent to $FULLNAME / $MOBILE, waiting 30 seconds for another SMS ...
rm /tmp/$USERNAME.new.account.sms
fi
sleep 60
done
rm $NEWUSERLIST

---

 

Regard’s
Syed Jahanzaib