Syed Jahanzaib Personal Blog to Share Knowledge !

August 15, 2017

Playing with the `radpostauth` table in Freeradius

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

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

 

Advertisements

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.

 

June 2, 2017

Howto install DMASoftlab Radius Manager in Centos 7 – 64bit

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

centos7

dma415

No doubt, Radius billing is one of the most important point of focus in any ISP network. ISP finance depends on this system to work smoothly. There are various ready made radius application based on LINUX are available in the market to cater different requirements of various operators. Among many, DMASoftlab Radius Manager is one of the most cheapest but feature rich radius candidate starting from 99$ only. It provide variety of options for any ISP. best thing is its ONE TIME COST only & it works life time 🙂 , you can install it locally on your system or on cloud as well, it requires Linux base OS like Debian/Ubuntu/Centos. Its full features can be viewed at Radius Manager web site. It required very little resources to run, provide plenty of user administration functions, backup / restoration / management is quite simple as well. plus in background you can accompany it with many scripting of your own to perform various customized functions.

We all know that cloud is every where now and almost all hosting providers are now providing latest versions of operating system like Centos 7. Some of us are well aware that DMA Radius Manager is quite comfortable with following flavors of nix …

  • Ubuntu 12.4 / 32bit [*** Best OS & my personnel choice, as recovery & troubleshooting is much easier in this variant]
  • Centos 6.5/6.6

But now a days many operators are switching to cloud where hosting providers are not providing older versions of centos/ubuntu. As i had to do few installation on cloud for few clients & I had to recall all the bits & bytes to do the installation every time, therefore I am posting step by step copy paste friendly version of guide on howto install Radius Manager in Centos 7. I posted ready made scripts for Ubuntu 12 Installation Script &Centos 6 in previous guides but This one is for Centos 7 to make installation quicker. I will make its script but better to do it manually as Centos 7 is a bit tricky which will give you some tough time in various aspects

  • OS Used in this guide = CentOS Linux release 7.3.1611 (Core)

Steps:

1# Disabling Firewall to avoid interruption

First we will update YUM & install nano for easy text editing, as many of us are not much comfortable with VI. NANO is better …

yum install update
yum -y install nano

* Disable IPTABLES

service iptables stop
chkconfig iptables off

* Disable SELINUX

So that it may not interfere with our installation/accessing. To disable builtin firewall permanently in centos, edit following

nano /etc/selinux/config

and change the

SELINUX=enforcing

to

SELINUX=disabled

Save & Exit .

*Disable Firewall(Daemon)

Now Disable Firewall’Daemon’ so that it may not interfere with our installation/accessing

systemctl status firewalld
systemctl stop firewalld
systemctl disable firewalld

Reboot system to take effect.


2# Installing various supporting modules !

1- Installing EPEL Repo to download various other supporting packages like mysql,apache etc

rpm -ivh https://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-9.noarch.rpm
yum -y install mc wget crontabs make gcc libtool-ltdl curl mysql-devel php php-mysql php-gd php-snmp php-process ntp alpine mariadb-server mariadb php-mcrypt cronie wget net-tools psmisc
yum -y install glibc.i686 libgcc_s.so.1
systemctl start mariadb.service
systemctl enable mariadb.service

3# Configuring Mysql Section

Now initiate mysql initial setup

mysql_secure_installation

Above command will ask few queries, read and select accordingly. ** Make sure to setup mysql root password **

*Now start / enable APACHE services 

systemctl start httpd.service
systemctl enable httpd.service

4# Adding IONCUBE module in PHP

Verify your PHP version  by php -v & copy the iocube load accordingly.
At my station, I had `PHP 5.4.16 (cli) (built: Nov  6 2016 00:29:02)` version installed by YUM.

wget http://downloads3.ioncube.com/loader_downloads/ioncube_loaders_lin_x86-64.tar.gz
tar -xvzf ioncube_loaders_lin_x86-64.tar.gz
cd ioncube
mv ioncube_loader_lin_5.4.so /usr/lib64/php/modules/
chmod 777 /usr/lib64/php/modules/ioncube_loader_lin_5.4.so

Now add ioncube library in php

nano /etc/php.ini

and add following line after [PHP] heading, usually this heading is in first line.

zend_extension = /usr/lib64/php/modules/ioncube_loader_lin_5.4.so

& restart APACHE service so it can load the new module

systemctl restart httpd.service

re-run ​​’php -v to verify you have something like [if successfull]

PHP 5.4.16 (cli) (built: Nov 6 2016 00:29:02)
Copyright (c) 1997-2013 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2013 Zend Technologies
with the ionCube PHP Loader (enabled) + Intrusion Protection from ioncube24.com unconfigured) v6.1.0 (), Copyright (c) 2002-2017, by ionCube Ltd.

5# Download FREERADIUS & compile.

cd /temp
wget http://www.dmasoftlab.com/cont/download/freeradius-server-2.2.0-dma-patch-2.tar.gz
tar -xvzf freeradius-server-2.2.0-dma-patch-2.tar.gz
cd freeradius-server-2.2.0
./configure
make
make install

Above may take some time to compile. After its done, issue following command to initiate radius

radiusd -X

If you get error in first attempt, try to run it again, and you may see following message on success

centos-radiusdx

Now press CTRL+C to return to command prompt.


 

6- Radius DB creation in MYSQL

Create mysql DATABASES for radius usage. Make sure to change the passwords where required, example mysql root password. It’s OK to use default passwords for the test flight, but for production server Its Highly recommended to use different passwords while you do the installation.

mysql -u root -pYOURMYSQLPASS
CREATE DATABASE radius;
CREATE DATABASE radius;
CREATE DATABASE conntrack;
CREATE USER 'radius'@'localhost' IDENTIFIED BY 'radius123';
CREATE USER 'conntrack'@'localhost' IDENTIFIED BY 'conn123';
GRANT ALL ON radius.* TO radius@localhost;
GRANT ALL ON conntrack.* TO conntrack@localhost;

7# Download & Install RADIUS MANAGER 4.1.0

cd /temp/
wget http://wifismartzone.com/files/rm_related/radiusmanager-4.1.0.tgz
tar zxvf radiusmanager-4.1.0.tgz
cd radiusmanager-4.1.0
chmod 755 install.sh
# Now start the Radius Install Script.
# once you will run INSTALL.SH , it will ask various queries, select according to your OS choice 

./install.sh

8# Access Radius Manager Administration Panel

Now try to access your radius manager admin panel from any browser pointing to ..

http://yourip/radiusmanager/admin.php

If all ok , you will see following window in your browser

dma-login-ok

* Default ID Password 
ID = admin
Password = 1111

Enjoy,

~*~*~*~*~*~*~*~*~*~*~*~*~*~*~

If you see error or blank page, use following Apache error/access logs for further trouble shoot. Most common causes are invalid license files / in-correct php ioncube files.

tail -f /var/log/httpd/error_log
tail -f /var/log/httpd/access_log

 


# TIPS

 

TIP#1 – How to create Virtual NIC with customized / cloned MAC address

First check what ethernet name you have currently, mostly its eth0, [change it according to your network].

ip link add link eth0 address 00:11:22:33:44:55 eth0.1 type macvlan
ifconfig eth0.1 up
ifconfig eth0.1 10.0.0.2

Verify if NIC is created with our desired NIC as well.

ifconfig

# Restart APACHE service if required, YKWIM ; )

systemctl restart httpd.service

TIP#2 – Deploying PATCH 4.1.5 for Centos

DMA released few patches for its radius application. Latest version is 4.1.6 , but I usually prefer to go with 4.1.5. Following is quick copy paste version to upgrade. Make sure that you have proper working installation of radius manager & if you are not much familiarized with the restoration process then you should backup your current installation before proceeding.

# SYED JAHANZAIB
# HTTPS://AACABLE . WORDPRESS . COM
# AACABLE at HOTMAIL dot COM
mkdir /temp
cd /temp
wget http://wifismartzone.com/files/rm_related/radiusmanager-4.1-cumulative_patch.tgz
tar zxvf /temp/radiusmanager-4.1-cumulative_patch.tgz
cd /temp/radiusmanager-4.1-cumulative_patch/
cp -vrf www/radiusmanager/* /var/www/html/radiusmanager
chmod 755 bin/rm*
service rmpoller stop
cp bin/* /usr/local/bin
cp raddb/acct_users /usr/local/etc/raddb
chmod 640 /usr/local/etc/raddb/acct_users
chown root.root /usr/local/etc/raddb/acct_users
service radiusd restart
service rmpoller start

For more information, please refer to

Dmasoftlab Radius Manager 4.1.5 Patch


Note: If you find any errors in this guide, please do let me know

Thanks & Best Regard’s

~SYED JAHANZAIB~

March 2, 2017

RM Reseller Monthly Report via GMAiL

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

hosting-by-the-hour

Disclaimer: This is not a reliable method for some OP to acquire the monthly sale report done by reseller. It requires some criteria to match like strict account creation by specific reseller id only. Better to use provided GUI in DMA_RM to inquire the proper report.

This is just a personal way of collecting reporting via automated email which MUST be cross verified by the OP .

Z@iB


BASH:

Following is a bash script which will simply query invoices, categorize it according to reseller account, format it using html coding, sort, total and send email admin.

Logic:

When you will execute this script, it will fetch the reports from rm_users database using last month dates, it will auto calculate last month dates.

Example:

Current month is March,2017. the script will pick Last Month which was February,2017 and will calculate number of days as well using CAL function.

Sample of Report is as follows.

 

1

RM Reseller Monthly Report via email

2

RM Reseller Monthly Report via email

Schelde following script via crontab @monthly. Modify information as per your local network.



#!/bin/bash
# Script to query INVOICES section of Radius manager mysql, and
# categorize it according to resellers , calculate accounts,sort and total it.
# These are my personal way of doing things, and no one is bound to agree with these.
# Created: Year 2014
# set -x
# MYSQL USER ID PASSWORD
SQLUSER="root"
SQLPASS="YOUR_MYSQL_PASSWORD"

# DATE RELATED STUFF
LASTMY=`date +'%Y-%m' -d 'last month'`
LAST_MONTH_DIGIT=`date +'%m' -d 'last month'`
YEAR_DIGIT=`date +'%Y' -d 'last month'`
LAST_MONTH_LAST_DATE=`cal $LAST_MONTH_DIGIT $YEAR_DIGIT |egrep -v [a-z] |wc -w`
START="$LASTMY-01"
END="$LASTMY-$LAST_MONTH_LAST_DATE"
FMONTH=`date +'%B-%Y' -d 'last month'`
DATE=`date`

# EMAIL RELATED STUFF
TO1="YOUR_EMAIL_ID@hotmail.com"
GMAILID="GMAIL_ID_TO_SEND_EMAIL_FROM@gmail.com"
GMAILPASS="GMAIL_PASSWORD"
CONTENT_TYPE="text/html"

# LOG FILES
FILE="/tmp/dealer_renewal_month.html"
FINALFILE="/tmp/dealer_renewal_month_final.html"
COMPANY="ZAIB(Pvt)_Ltd.
This System is powered by Syed_Jahanzaib aacable@hotmail.com"
BODY_TITLE="Report For Dealer Account asof $FMONTH"
> $FILE
> $FINALFILE

# HTML BODY
echo "
<pre>" > $FILE
echo "<b>$BODY_TITLE</b>" >> $FILE
echo "<b>DEALER User's_Activated Used_Amount Balance</b>
" >> $FILE

# QUERY MANAGERS FROM RM_MANAGERS TABLE
mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; select managername from rm_managers;" | while read dealer
do
num=$[$num+1]
DEALER=`echo $dealer | awk '{print $1}'`

# GATHER DATA OF ACTIVE USERS, USED AMOUNT, CURRENT BALANCE, (MOBILE NUMBER IF SMS IS REQUIRED TO SEND)
ACTIVEUSERSNO=`mysql -uroot -p$PASS --skip-column-names -e "use radius; SELECT price FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$START' AND date <= '$END' AND (paymode = '0' OR paymode = '1' OR paymode = '2' OR paymode = '3' OR paymode = '4' OR paymode = '5' OR paymode = '6' OR paymode = '7' OR paymode = '8' OR paymode = '9' OR paymode = '10' OR paymode = '11' ) AND (invgroup = '0' ) AND invnum != '' AND rm_users.owner = '$DEALER';" | sed '/credited/d' | wc -l`
USEDAMOUNT=`mysql -uroot -p$PASS --skip-column-names -e "use radius; SELECT price FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$START' AND date <= '$END' AND (paymode = '0' OR paymode = '1' OR paymode = '2' OR paymode = '3' OR paymode = '4' OR paymode = '5' OR paymode = '6' OR paymode = '7' OR paymode = '8' OR paymode = '9' OR paymode = '10' OR paymode = '11' ) AND (invgroup = '0' ) AND invnum != '' AND rm_users.owner = '$DEALER';" | sed '/credited/d' | awk '{ sum+=$1} END {print sum}'`
BALANCE=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; select balance from rm_managers WHERE managername = '$DEALER';" |cut -f1 -d"."`
MOBILE=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; select mobile from rm_managers WHERE managername = '$DEALER';"`
SRV=`mysql -uroot -p$SQLPASS --skip-column-names -e "use radius; SELECT SQL_CALC_FOUND_ROWS rm_invoices.service FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$START' AND date <= '$END' AND (paymode = '0' OR paymode = '2' ) AND (invgroup = '0' OR invgroup = '1' ) AND invnum != '' AND rm_users.owner = '$DEALER' ORDER BY id LIMIT 0, 10000;" | sed '/credited/d' | awk '{print $1}' | sort | uniq -c`

#LOOK FOR ZERO VALUE AMOUNT AND REPLACE IT WITH 0 , IF FOUND
#if [ ! -n "$USEDAMOUNT" ]; then
#if [ "USEDAMOUNT == "" ]; then
#USEDAMOUNT="X"

# PRINT ALL GATHERED DATA INTO FILE
#echo "<b>$DEALER</b> $ACTIVEUSERSNO $USEDAMOUNT - $BALANCE
#------------------------------------------------------------------------" >> $FILE
#else

# PRINT ALL GATHERED DATA INTO FILE
echo "<b>$DEALER</b> $ACTIVEUSERSNO $USEDAMOUNT - $BALANCE

Details&nbsp;of&nbsp;Services&nbsp;Activated:
Qty Service&nbsp;Name

$SRV

------------------------------------------------------------------------" >> $FILE

#fi
done

# MAKE COLUMNS SO THAT IT GETs EASIER TO READS
sed -e 's/\t//g' $FILE | column -t | sed 's/ //g' | sed 's/ User/User/g' > $FINALFILE
# GATHER DATA OF ACTIVE USERS, USED AMOUNT, CURRENT BALANCE, (MOBILE NUMBER IF SMS IS REQUIRED TO SEND)
TOTNO=`mysql -uroot -p$PASS --skip-column-names -e "use radius; SELECT price FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$START' AND date <= '$END' AND (paymode = '0' OR paymode = '1' OR paymode = '2' OR paymode = '3' OR paymode = '4' OR paymode = '5' OR paymode = '6' OR paymode = '7' OR paymode = '8' OR paymode = '9' OR paymode = '10' OR paymode = '11' ) AND (invgroup = '0' ) AND invnum != '';" | sed '/credited/d' | wc -l`
SALES=`mysql -uroot -p$PASS --skip-column-names -e "use radius; SELECT price FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$START' AND date <= '$END' AND (paymode = '0' OR paymode = '1' OR paymode = '2' OR paymode = '3' OR paymode = '4' OR paymode = '5' OR paymode = '6' OR paymode = '7' OR paymode = '8' OR paymode = '9' OR paymode = '10' OR paymode = '11' ) AND (invgroup = '0' ) AND invnum != '';" | sed '/credited/d' | paste -sd+ | bc | cut -f1 -d"."`

echo "Total Users Activated/Renewed in $FMONTH = <b>$TOTNO</b>" >> $FINALFILE
echo "Total SALES Done in $FMONTH = <b>$SALES</b>" >> $FINALFILE
echo "
<b>$COMPANY</b>" >> $FINALFILE
echo "Generated on $DATE" >> $FINALFILE
echo "</pre>
" >> $FINALFILE

##Finally send email with all the data gathered USING SEND_EMAIL TOOL
/temp/sendEmail-v1.56/sendEmail -t $TO1 -u "INFO: GT DEALERS MONTHLY BILLING INFO for $FMONTH" -o tls=yes -s smtp.gmail.com:587 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$FINALFILE -o message-content-type=$CONTENT_TYPE
# Type file for view
cat $FINALFILE

 

February 27, 2017

Radius Manager Theme/Skin Collection

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

Following are few customized themes for DMASOFTLAB Radius manager , Designed by various web developers.

  1. Digital Theme [Responsive as well, made by KANGNDO]
  2. Green Theme
  3. Blue Theme

 


General Steps to install theme

I assume you have DMASoftlab Radius Manager 4.1.x version installed and must be fully functional.

  • Download the theme file, unrar it in any temporary folder. you can use `tar zxvf filename.tar.gz /destination_folder`
  • Copy (or let’s say Overwrite) all the contents including files/folders from the (unzipped) folder name radiusmanager in /var/www/radiusmanager (or for CENTOS, its /var/www/html/radiusmanager). You can cp -vr syntax /source /target
  • Clear the browser cache, and reload the ACP administration control panel) page.

.


1- Digital Theme

Download Link: Radius Manager KANGNDO Theme Official Link 

🙂

Sample Images:

1

2

3

4


2- Green Theme

Green Theme Download Link

green-1

 


Will update more later

 

Regard’s
Syed Jahanzaib

December 19, 2016

Incorrect key file for table ‘./conntrack/xxxx@xxxxxx@xxxxxx.MYI’; try to repair it

Filed under: Radius Manager, Uncategorized — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 1:05 PM

corruption

Incorrect key file for table ‘./conntrack/2016@002d12@002d16.MYI’; try to repair it

If you see the above error while accessing Connection Tracking Reports in Radius Manager then …

Recommendations:

  • Make sure you aave plenty of Disk Space. Storage drive should be fast for high load servers preferably SSD or RAID based storage ,
  • Plenty of Memory is usually a good idea for DB systems ,
  • If its a heavy production server, try to move connection tracking to separate host as it takes lots of space and it adds good amount of payload to the CPU as well.
  • Perform tuning to enhance the mysql engine

To repair table …

Goto the conntrack folder [Ubuntu]
cd /var/lib/mysql/conntrack/
service mysql stop
# Change the file number as shown in the error 
myisamchk -r -v -f 2016@002d12@002d16.MYI

Once repair is done, start mysql / restart apache2 services , and test the desired tracking report again.

Regard’s
~Syed Jahanzaib~

August 24, 2016

Radius Manager Connection Tracking System for Mikrotik

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

trackme

As requested by few OP, following is a short technical reference guide on how you can enable TCP/UDP connections in Mikrotik to be stored in Radius manager connection tracking database so that you can view user base connection tracking report. In my personal view, it’s not much useful and at low end hardware it creates bottleneck, but if you have good resources with some fast storage (like SSD or RAID-10) it is better to set it up.

CTS is best described in the manual by dmasoftlab manual as mentioned below

Radius Manager has a special feature: the Connection Tracking System. It is available only in Radius Manager CTS version or higher. With the help of it the system can track and log all the TCP and UDP connections for all registered (online) users.

By default when You install the CTS enabled version of Radius Manager, it will use the default CTS database (CONNTRACK). It is strongly recommended to use a separate database host for the CONNTRACK database, due to the enormous amount of data stored daily. It can be even a 100-500 MegaBytes (and in my personal experience it can grow as much as 3-5 GB on busy network, ZAIB) per day. Fast disks (like SSD in RAID 10 mode,  zaib) are also recommended to be able to seek and store the data in real time. Radius Manager periodically stores the traffic data to CONNTRACK database (typically in
every 5–60 seconds).


Mikrotik (6.x) Configuration to enable Firewall Logging to remote server (RM)

If you have already configured the radius manager, then the conntrack database also get configured via the installation script. Next step is to enable the firewall logging in the mikrotik router so that mikrotik can send the categorized TCP/UDP data to radius manager conntrack database.

In mikrotik, open terminal and issue following commands …

In below example, we have following IP scheme.

PPPoE users ip pool = 172.16.0.1-172.16.0.255
Radius Manager IP  = 101.11.11.254

/ip firewall filter add chain=forward src-address=172.16.0.1-172.16.0.255 protocol=tcp connection-state=new action=log

/ip firewall filter add chain=forward src-address=172.16.0.1-172.16.0.255 protocol=udp connection-state=new action=log

/system logging action add name=rmctszaib remote=101.11.11.254 target=remote remote-port=4950

/system logging add topics=firewall action=rmctszaib

If you don’t see any errors , you are good to Go.


RADIUS MANAGER SECTION:

1. Radius Manager should be licensed with CTS level. Basic license wont gonna work ..

2. Make sure that  RMCONNTRACK service is UP and running.To make sure it’s running , get its process by

ps aux |grep rmconntrack

and you should get result something like following

root@radius:/# ps aux |grep rmconntrack
root xxxx 0.0 0.0 xxxxxx xxx ? Ssl 13:22 0:00 /usr/local/bin/rmconntrack

if you see its running, proceed further , otherwise you may start it manually by

rmconntrack –x

Now login to RM ACP  >  Reports  >  Connection Report

Here you can get report for all or individual user.

As showed in the image below …

c1

 

c2

 


Regard’s
Syed Jahanzaib

 

 

1

June 28, 2016

RM: Auto Renew User if Deposit available

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

AutoRenewal

Revision History:
24-Jun-2015 / Added Base Script
29-Jun-2016 / Added Invoice function / Fixed bug if user quote is under 1 GB limit / few minor tweaks

As requested by an Valenzuela client.

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

 

To make a workaround for this issue, I made a script that does the following.

  1. Scheduled to run after every 10 minutes
  2. Fetch users lists from rm_users table who have DEPOSIT available (credits above then 0)
  3. Check user account status , if Active Ignore it ,
  4. Else if expires, check the current service price and match it with the available deposit/credits,
  5. If deposit is not sufficient, then print error and exit,
  6. if deposit is enough, renew the service , add 30 days to service, add quota if any and sends email/sms to user about the renewal done by deposit : )

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.This is my own idea Just to share with anyone who is in similar need or just for learning purposes !


SCRIPT!

#!/bin/bash
# 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
#set -x
# 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="zaib1234"
USERLIST="/tmp/deposituserlist.txt"
#Create list of users which ahve deposite more then 0.00 value, means valid deposite
mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT username, credits FROM rm_users where credits > '0.00';" > $USERLIST
#LOOK FOR VALID USER IN FILE, IF EMPTY THEN EXIT
USRVALID=`cat $USERLIST`
if [ -z "$USRVALID" ]; then
echo -e "ERROR: NO USER FOUND for matching ... exiting!"
exit 0
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=`echo $users | awk '{print $2}' | sed 's/\..*$//'`
######################
# ACCOUNT EXPIRY CHECK and other variables
######################
TODAY=$(date +"%Y-%m-%d")
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'`
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'`
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:.*//'`
NEXTEXPIRYADD=$(date +"%Y-%m-%d" -d "+30 days")
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"."`
#LOOK FOR USER ACTUAL SERVICE NAME
PKGNAME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvname FROM radius.rm_services WHERE rm_services.srvid = '$SRVID';" |awk 'FNR == 2'`
# Look for Pakacge Quota trafficunitcomb
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))
LASTUSRBAL=$(($DEPOSIT - $SRVPRICE))

TIMEUNITEXP=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT timeunitexp FROM radius.rm_services WHERE srvid = '$SRVID';"`
TIMEBASEEXP=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT timebaseexp FROM radius.rm_services WHERE srvid = '$SRVID';"`

if [ "$TIMEBASEEXP" == "2" ]; then
EXPERIOD="$TIMEUNITEXP Days"
#echo "$EXPERIOD"
fi
# Set Quota Limit variable which will be used in the end
if [ "$TIMEBASEEXP" == "3" ]; then
EXPERIOD="$TIMEUNITEXP Month"
#echo "$EXPERIOD"
fi

#######zzzzzzzz#timebaseexp
# 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
if [ $SRVEXPIRY -gt $TODAYDIGIT ]; then
echo -e "$COL_GREEN User Account = $USR | ALREADY ACTIVE | TIMEUNIT = $EXPERIOD | Expiry Date = $SRVEXPIRYFULLD | User Deposit Available = $DEPOSIT | Pacakge Price = $SRVPRICE PKR | Next Expiry = $NEXTEXPIRYADD | Quota = $QT $COL_RESET
"
else
########### ACCOUNT STATUS EXPIRED BUT NOT ENOUGH DEPOSIT to RENEW ACTION ############
if [ "$DEPOSIT" -lt "$SRVPRICE" ]; then
echo -e "$COL_RED User Account = $USR | TIMEUNIT = $EXPERIOD | ERROR: Account was expired on $SRVEXPIRYFULLD but user $USR DOES NOT HAVE ENOUGH DEPOSIT IN USER ACCOUNT! Current Deposite is $DEPOSIT and Required is $SRVPRICE $COL_RESET
"
else
########### ACCOUNT STATUS EXPIRED and DEPOSIT IS ENOUGH TO RENEW ACTION ############
if [ $SRVEXPIRY -lt $TODAYDIGIT ] || [$SRVEXPIRY -eq $TODAYDIGIT ]; then

# RENEW USERS IF ALL CONDITIONS MATCHED / PRINT FETCHED VALUES , JUST FOR INFO / ZAIB
echo -e "$COL_YELLOW User Account = $USR ** RENEWING NOW ** | TIMEUNIT = $EXPERIOD | Expiry Date = $SRVEXPIRYFULLD | User Deposite Available = $DEPOSIT | Pacakge Price = $SRVPRICE PKR | Next Expiry = $NEXTEXPIRYADD | Quota = $QT $COL_RESET
Now Balance is = $LASTUSRBAL PKR"

# ADD 30 DAYS VALUE TO EXPIRED USER ACCOUNT
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET expiration = '$NEXTEXPIRYADD' WHERE username = '$USR';"
# ADD COMMENTS
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET comment = 'This account was last refresh from DEPOSIT $DATE' WHERE username = '$USR';"
# ADD SYSLOG ENTRY
mysql -u$SQLUSER -p$SQLPASS -e "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
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET credits = '$LASTUSRBAL' WHERE username = '$USR';"
# ADD INVOICE
mysql -u$SQLUSER -p$SQLPASS -e "use radius; 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', '2016-0021', '$ADDRESS', '$CITY', '00000', '$COUNTRY', '$STATE', '$FULLNAME', 'n/a',
 DATE_ADD(CURDATE(), INTERVAL '10' DAY), '0', '0', '$TODAY', '$SRVPRICE', '0.000000', '', '$LASTUSRBAL', '', '$MOBILE', '$MOBILE', '0.00' );"

mysql -u$SQLUSER -p$SQLPASS -e "use radius; 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-$TODAY', '2', '1', DATE_ADD(CURDATE(), INTERVAL '10' DAY),
 '577343812eee0' );"

# UPDATE Quota limitations if any, else ignore
if [ "$PKGQUOTA" -ne 0 ]; then
echo "Adding $PKGQUOTA MB Quota Limit for $USR as well
"
mysql -u$SQLUSER -p$SQLPASS -e "use radius; UPDATE rm_users SET comblimit = '$PKGQUOTABYTES' WHERE username = '$USR';"
else
echo "No need to add quota"
fi
fi
fi
fi
done
# THE END SCRIPT ENDS HERE #
# SYED JAHANZAIB

RESULT!

1


 

June 22, 2016

Retrieve User Old/Original Password in RM

Filed under: Radius Manager — Tags: — Syed Jahanzaib / Pinochio~:) @ 10:44 AM

lostpass

Please beware that this post is just for Example purpose Only. In real production environment you must be very careful for providing such option. Make it tightly secure, add captcha code & provide this feature to requesting users only. AVOID using bash, RELY on PHP !


As requested by an client, Following is an script that can retrieve user’s current current password from 'radcheck' table. This method is useful in some situation where operator dont want to change the password for user, but to provide them there old/original password.

  • In RM, user’s password are encrypted with MD5. which is a Digest algorithm. Think of it as converting a cow into a steak. Now try to reverse that 🙂
  • There are some online MD5 decrypter, but they can decrypt general or common words. If you have something complex password, it wont be able to decrypted.

So rather then getting into MD5 decryption mess, why not retrieve it under the table 😉 by getting it from radcheck table.

There are few methods we can provide ‘current password retrieval’ funcion to user.

  1. We can configure playSMS to receive incoming SMS from user with specific command and username, then the system can retrieve user current password and sms to his Registered mobile number.
  2. Or we can make a simple PHP page where user can enter his user ID and then the system can send password to his Registered Mobile No. and Email address.

the Script ! [SAMPLE]

#!/bin/sh
#set -x
SQLUSER="SQL_USER"
SQLPASS="SQL_PASSWORD"
COMPANY="MyCompany"
CURDATE=$(date +"%Y-%m-%d")
echo $1 > /tmp/rawdata.txt
USERNAME=`cat /tmp/rawdata.txt |awk '{print $1}'`
echo ""
PASS=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; select value from radcheck where username = '$USERNAME';" | awk 'FNR == 1'`
echo "Dear $USERNAME,

Your Password is $PASS

Regard's
$COMPANY"

Execute the script and see the result.

shpass


PHP Form base method using Shell Script !

f1

f2

 

Sample php/shell files uploaded to

https://drive.google.com/folderview?id=0B8B_P2ljEc2xSndud0hDV29HT2s&usp=sharing&tid=0B8B_P2ljEc2xcEdkd2ttV1ZmNFU

Make sure you add good security measurements first !

 

Regard’s
Syed Jahanzaib

 

June 13, 2016

Sending Email/SMS Alert to User for Service Change Event

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

srvchange

 

Screenshot_2016-06-13-12-56-48

SMS Alert


Reference Notes:

Requirements:

We want to send email/sms alert to user about his service package change with old/new package name details. Although this function is builtin in RM , but with customized scripts we can do other functions as well.


Solution:

We will create mysql trigger that will be executed every time srvid column will be changed in rm_users table. then we will create mysql table which will hold all these info. Then the trigger will add user info like old service id , new service id, user name, mobile etc in this table upon srvid change.

Neat & clean.


 

First create mySQL trigger which will be executed once there will be changes made in srvid column in rm_users table.

1- mySQL Trigger

Create file name srvchangetriggers.sql and paste following data

-- MySQL dump 10.13 Distrib 5.5.46, for debian-linux-gnu (i686)
-- Host: localhost Database: radius
-- Syed Jahanzaib
-- ------------------------------------------------------
-- Server version 5.5.46-0ubuntu0.12.04.2-log
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `myTrigger` BEFORE UPDATE ON `rm_users`
FOR EACH ROW BEGIN
IF NEW.srvid <> OLD.srvid THEN
INSERT INTO rm_usersrvchangehistory (datetime, username, newsrvid, oldsrvid, firstname, lastname, mobile) VALUES (NOW(), new.username, new.srvid, old.srvid, new.firstname, new.lastname, new.mobile);
END IF;
END */;;
DELIMITER ;
-- Dumping routines for database 'radius'
--

2- mySQL Table

Add mySQL table where records will be saved.

Create file name rmsrvchangetable.sql and paste following date

-- phpMyAdmin SQL Dump
-- version 3.4.10.1deb1
-- http://www.phpmyadmin.net
-- Syed Jahanzaib
-- 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_usersrvchangehistory`
--
CREATE TABLE IF NOT EXISTS `rm_usersrvchangehistory` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`datetime` datetime NOT NULL,
`username` varchar(64) NOT NULL,
`newsrvid` varchar(64) NOT NULL,
`oldsrvid` varchar(64) NOT NULL,
`firstname` varchar(64) NOT NULL,
`lastname` varchar(64) NOT NULL,
`mobile` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=63 ;
--
-- Dumping data for table `rm_usersrvchangehistory`
--
/*!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 */;

Importing the .sql files in Radius DB / mySQL

Now import both files  in radius DB by command

mysql -uroot -pSQLPASS radius < rmsrvchangetable.sql
mysql -uroot -pSQLPASS radius < srvchangetriggers.sql

Test The Changes …

Now try to change any user service, and check rm_usersrvchangehistory by following command

root@ubuntu:/temp# mysql -u root -pSQLPASS -e "use radius; select * from rm_usersrvchangehistory;"
+----+---------------------+----------+----------+----------+-----------+-----------+-------------+
| id | datetime | username | newsrvid | oldsrvid | firstname | lastname | mobile |
+----+---------------------+----------+----------+----------+-----------+-----------+-------------+
| 71 | 2016-06-13 12:24:00 | test | 4 | 13 | syed | jahanzaib | 03333021909 |
+----+---------------------+----------+----------+----------+-----------+-----------+-------------+

Script to fetch data on scheduled basis and SMS/EMAIL…

Create a script that will be scheduled to run after every 5 minutes , it will check in table rm_usersrvchangehistory and will send sms to user about package change event.

mkdir /temp && cd /temp
touch /temp/srvchange.sh
chmod +x temp/srvchange.sh
nano temp/srvchange.sh

and paste following data…

the Script:

 

#!/bin/bash
# srvchange.sh
# Bash script which will run after every 5 minutes and will fetch info from mysqltable
# and will send SMS/Email alert for service change event.
# Created by SYED JAHANZAIB
# aacable@hotmail.com
# https://aacable.wordpress.com
# Created : 13-JUN-2016
#set -x
SQLUSER="root"
SLQPASS="SQLPASS"

# File where user info wil be hold temporary
TMPUSRINFO=/tmp/usersrvinfo.txt

# Interval in minutes to check user record
INTERVAL="5"

# Fetch user info from the table.
mysql -uroot -p$SQLPASS --skip-column-names -e "use radius; select * from rm_usersrvchangehistory WHERE datetime >= NOW() - INTERVAL $INTERVAL MINUTE;" > $TMPUSRINFO

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

# Company Footer
COMPANY="JZ_ISP"

# 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 $4}'`
firstname=`echo $users | awk '{print $7}'`
lastname=`echo $users | awk '{print $8}'`
mobile=`echo $users | awk '{print $9}'`
date=`echo $users | awk '{print $2,$3}'`
newsrvid=`echo $users | awk '{print $5}'`
oldsrvid=`echo $users | awk '{print $6}'`

# Print Info on screen
# Fetch old/new Package Name
OLDPKGNAME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvname FROM radius.rm_services WHERE rm_services.srvid = '$oldsrvid';" |awk 'FNR == 2'`
NEWPKGNAME=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvname FROM radius.rm_services WHERE rm_services.srvid = '$newsrvid';" |awk 'FNR == 2'`

# Print FINAL Fetched info
echo "Dear $firstname $lastname ,
Your internet package against your User ID: $username has been upgraded from $OLDPKGNAME to $NEWPKGNAME !

$COMPANY"

# Store Info for sending SMS in /tmp folder where we will call kannel to send customized SMS
echo "Dear $firstname $lastname ,
Your internet package against your User ID: $username has been upgraded from $OLDPKGNAME to $NEWPKGNAME !

$COMPANY" > /tmp/$username.srvchange.sms

# send sms using kannel gateway
curl "http://$KHOST/cgi-bin/sendsms?username=$KID&password=$KPASS&to=$mobile" -G --data-urlencode text@/tmp/$username.srvchange.sms

# If you send lot of SMS via local mobile SIM, then make sure you give enough delay so that your SIM may not get blocked by BULK SMS monitor by TELCOM authority like PTA.
#sleep 15
done
# once done, we should delete the .sms files to clear the garbage
rm -fr /tmp/*.sms


End Results !

Now execute the Script  and witness the Fun !

srvchange


Regard’s
Syed Jahanzaib

Older Posts »

%d bloggers like this: