×

DMA Radius – Bash scripts for Daily renewal/expiry account reporting

  • Home
  • Blog
  • DMA Radius – Bash scripts for Daily renewal/expiry account reporting
DMA Radius – Bash scripts for Daily renewal/expiry account reporting


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


Posting it form old archives. Following are two bash scripts that can perform following actions

  1. renewal_alert.sh > Sends email to admin for accounts renewed at end of the day along with few other details like manager who renewed [sort by manager name] , username package, days added, renewal date, expiration date etc.
  2. expiry_alert.sh > Sends email to admin for accounts that are going to expire after X days, (X is parameter like 1, 2, 3 & so on

TIP:

For sendemail function, you can install it on Ubuntu by using following cmd’s ((sendemail is a small email app which can use your Gmail account. Make sure to enable ALLOW LESS SECURE APP in gmail account settings section))

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

Renewal_alert.sh

For renewal query function , 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

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

Now create below script to generate result.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
#!/bin/sh
# set -x
# BASH base script for account renewal notification for RADIUS MANAGER based on Freeradius/mysql
# By Syed Jahanzaib / aacable at hotmail dot com
# CREATED on : 16th July, 2015
# Modified on 14th DEC, 2015
# Modified on 15th JAN, 2021
 
SQLUSER="root"
SQLPASS="XXX"
MNT="5"
DATE=`date`
# 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/new.user.account.list.in.last.5.mnts"
TMPHOLDER1="/tmp/renewal.report_raw.log"
COMPANY="Zabbu"
GMAILID="XXX@gmail.com"
GMAILPASS="XXX"
ADMINMAIL1="XXX@hotmail.com"
> $TMPUSRINFO
> $TMPHOLDER1
# 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 renewed 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.date, rm_invoices.expiration, rm_invoices.days, rm_invoices.managername, 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}'`
paid=`echo $users | awk '{print $2,$3}'`
expiration=`echo $users | awk '{print $4}'`
daysadded=`echo $users | awk '{print $5}'`
managername=`echo $users | awk '{print $6}'`
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}'`
PKGNAME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvname FROM radius.rm_services WHERE rm_services.srvid = '$SRVID';" |awk 'FNR == 2'`
echo "MGR = $managername | UserID = $username | PKG = $PKGNAME | Days = $daysadded | Renewal_Date = $paid | Expiration_Date = $expiration " >> $TMPHOLDER1
done
 
# Sort file based on 2nd column that is MANAGER NAME
sort -k3 -n $TMPHOLDER1
 
echo "
Sending Email Now ...
"
sendemail -t $ADMINMAIL1 -u "Renewal report for $DATE" -o tls=yes -s smtp.gmail.com:587 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$TMPHOLDER1 -o message-content-type=text
#in the end delete sms/email files for this action
rm $TMPHOLDER1

Result:

Expiry_alert.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
#!/bin/sh
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
#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
# Created on : 8th June, 2015
# Modified last on: 15th January 2021
#################
# CHANGE these
if [ -z "$1" ]; then
echo "Number of days Parameter missing,
Usage: ./scrtipname 3"
exit 1
fi
 
SQLID="root"
SQLPASS="XXX"
DB="radius"
DB_SRV_NAME="mysql"
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"
TMP="/tmp/neAr_expiring_users_list.log"
TMPHOLDER2="/tmp/neAr_expiring_users_list_final.log"
DATE=`date`
 
GMAILID="XXX@gmail.com"
GMAILPASS="XXX"
ADMINMAIL1="aacable@hotmail.com"
COMPANY="Zabbu"
> $TMP
> $TMPHOLDER2
# Interval before alert which should be sent to user before this number days
EXPIRY_DAYS_REMAIN="$1"
#DATE TIME FUNCTIONS
currenttime=$(date +%H:%M:%S)
 
# Export usernames and mobile from the mysql table in a file, which Expiry is after X days
$CMD "use $DB; SELECT username,firstname,lastname FROM $USER_TABLE WHERE expiration = DATE_ADD(CURDATE(), INTERVAL $EXPIRY_DAYS_REMAIN DAY);" > $TMP
TOTUSER=`cat $TMP | wc -l`
#echo "- INFO: Total number of users with near expiry = $TOTUSR / No.s ... OK"
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...
- Script End Time - $endtime
"
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}'`
firstname=`echo $users |awk '{print $2}'`
lastname=`echo $users |awk '{print $3}'`
if [ -z "$firstname" ]; then
firstname="none"
fi
if [ -z $lastname ]; then
lastname="none"
fi
EXPIRATION_DATE=`$CMD "use $DB; select expiration from $USER_TABLE where username ='$USERNAME';" |sed 's/00:00:00//g'`
SRVID=`$CMD "use $DB; SELECT srvid from $USER_TABLE where username = '$USERNAME';"`
#### ECHO THE RESULTS
echo "USERNAME: $USERNAME | FNAME: $firstname | LNAME: $lastname | Expiration: $EXPIRATION_DATE" >> $TMPHOLDER2
done
 
cat $TMPHOLDER2
 
echo "
Sending Email Now ...
"
sendemail -t $ADMINMAIL1 -u "Expiry report for $DATE" -o tls=yes -s smtp.gmail.com:587 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$TMPHOLDER2 -o message-content-type=text
rm $TMP
rm $TMPHOLDER2

Sample: [accounts that are going to expire after 3 days.

Related Post

Generating Refill Cards in Radius Manager

Enable `radpostauth` table for Radius Manager

Sharing Ideas … Updated Script for Renew Expired User Account via SMS in DMASOFTLAB Radius Manager

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

MySql Database Recovery from Raw Files

RM: Delete Expired Users Record

Blog Categories

Related Posts

×

Notice!!

All Quantic user are requested to use our hybrid cloud drive for you project and Data base . We had added new module of cronjob to schedule and optimise your backup .