Syed Jahanzaib Personal Blog to Share Knowledge !

February 29, 2016

[LAB] QOS Management in Mikrotik – Bandwidth Pools for Group of Users

Filed under: Mikrotik Related — Tags: , , , — Syed Jahanzaib / Pinochio~:) @ 4:48 PM

As required by an Operator:

We are using Mikrotik Routerboard in lab environment, and have total 384kbps of internet bandwidth from the ISP.

Hotspot with User manager is already configured and working. (no static/dynamic queues are configured)


 

Requirements:

We want to distribute bandwidth as follows.

Group A = 192.168.10.1-192.168.10.255
Total Bandwidth Pool for Group A = 128 kb
Per User Allowed = 64 kb and xxx kb (but overall all users must not exceed the parent queue that is 128 kb)

Group B = 192.168.20.1-192.168.20.255
Total Bandwidth Pool for Group B = 256 kb
Per User Allowed = 64 k and 128 k  for different profile users (but overall all users must not exceed the parent queue that is 256 kb)

This way Group A should get per user bandwidth from the parent queue 128 kb all the time but overall all users must not exceed the parent queue that is 128 kb

and the Group B users should get 128 k per user from the parent queue 256 kb all the time but overall all users must not exceed the parent queue that is 256 kb

 

This is also a way to give guaranteed bandwidth to group of people. example you have 10mb of link, and want to provide dedicated 5 mb to a single or group of users, and rest of 5 mb to common subscriber. (I am talking in kbs because in lab i have smaller amount of bandwidth, and its easier to do repeated tests in smaller links)


Pseudo Code:

  1. Mark connections and then packets from the specific IP Pool
  2. Create PCQ queue in Queue Type (for per user classification)
  3. Create Bandwidth pool for Groups in Queue Tree (Parent)
  4. Create per user queue in parent groups for per user distribution for marked packets.

 


 

1- Mark connections from the specific IP Pool

For this guide, I am using my local pc’s for demonstration purposes. but you can use groups as showed above.

PC#1, Laptop PC,
PC#2, Zaib PC,
PC#3, Temporarily PC

/ip firewall mangle

add action=mark-connection chain=prerouting comment="LAPTOP pc marking connection" new-connection-mark=laptop_pc_conn src-address=101.11.16.159
add action=mark-packet chain=prerouting comment="LAPTOP pc marking packets" connection-mark=laptop_pc_conn new-packet-mark=laptop_pc_pkts

add action=mark-connection chain=prerouting comment="zaib pc marking connection" new-connection-mark=zaib_pc_conn src-address=101.11.11.161
add action=mark-packet chain=prerouting comment="zaib PC Marking Packets" connection-mark=zaib_pc_conn new-packet-mark=zaib_pc_packets

add action=mark-connection chain=prerouting comment="VM PC marking CONNECTIONS" new-connection-mark=TEMP_pc_conn src-address=101.11.14.19
add action=mark-packet chain=prerouting comment="VM PC Marking Packets" connection-mark=TEMP_pc_conn new-packet-mark=temp_pc_packets

 


 

2- Create PCQ queue in Queue Type (for per user classification)

/queue type
add kind=pcq name=64k-per-user pcq-classifier=dst-address pcq-dst-address6-mask=64 pcq-rate=65536 pcq-src-address6-mask=64
add kind=pcq name=128k-per-user pcq-classifier=dst-address pcq-dst-address6-mask=64 pcq-rate=131072 pcq-src-address6-mask=64

 



3- Create Parent queues

/queue tree
add disabled=yes max-limit=256k name="256k TOTAL  for Group B" parent=global queue=default
add disabled=yes limit-at=128k max-limit=128k name="128k TOTAL for Group A" parent=global queue=default

 

4- Create per user (or group) queue in parent groups for per user distribution for marked packets.

/queue tree

# Adding user bandwidth rule from 128k pool
add name="TEMP PC - 64k - from 128k Pool" packet-mark=temp_pc_packets parent="128k TOTAL for Group A" queue=64k-per-user

# Adding users bandwidth rule from 256k pool
add name="ZAIB PC 64k - from 256k pool" packet-mark=zaib_pc_packets parent="256k TOTAL  for GroupB" queue=64k-per-user
add name="LAPTOP PC 128k - from 256k pool" packet-mark=laptop_pc_pkts parent="256k TOTAL  for GroupB" queue=128k-per-user

 


Watching rules in action 🙂

 

pcq


This post requires lot of time to explain things or add other items as well. will do it later.

zaib_scattered

Regard’s
Syed Jahanzaib

Advertisements

February 26, 2016

IBM ServeRAID m5110e Fatal firmware errors on Server 2008


In our data center, we are using IBM Xseries 3650 M4 series. We updated one of the systemX server  firmware and drivers a month ago. since then it was having issue of halting in random days specially in heavy load conditions (like when backup executes) and was presenting following errors on the screen.

AGP-23-FEB-2016-firmware error 2

 

AGP-23-FEB-2016-firmware error

Raid controller details were as followed.


SERVER---
OS name:                 Windows Server 2008 R2
OS Version:              6.1
OS Architecture:         x86_64
Driver Name:             megasas2.sys
Driver Version:          6.702.07.00
Application Version:     MegaRAID Storage Manager - 13.01.04.00

HARDWARE---
Controller:              ServeRAID M5110e(Bus 22,Dev 0)
Status:                  Optimal
Firmware Package Version:23.22.0-0024
Firmware Version:        3.340.75-3372
BBU:                     NO

 

After some diagnostic it was found that the culprit was driver version “6.702.07.00“. As stated in IBM web site.

https://www-947.ibm.com/support/entry/portal/docdisplay?lndocid=migr-5096069

After that, we downloaded IBM Update Express ver 9.63 (ibm_utl_uxspi_9.63_winsrvr_32-64.exe) and execute the update for selected drivers on live running system that was hosting our lotus domino email system. It took around 1 hour for the download + update and upon rebooting, and till the writing of this post, the problem seems to be solved now.

ibm firmware update


 

After Update,


SERVER---
OS name:                 Windows Server 2008 R2
OS Version:              6.1
OS Architecture:         x86_64
Driver Name:             megasas2.sys
Driver Version:          6.708.09.00
Application Version:     MegaRAID Storage Manager - 15.03.01.00

HARDWARE---
Controller:              Controller0: ServeRAID M5110e(Bus 22,Dev 0,Domain 0)
Status:                  Optimal
Firmware Package Version:23.33.0-0043
Firmware Version:        3.450.145-4983
BBU:                     NO

Note: I would recommend to NOT upgrade any critical system firmware until is really required.

 


Regard’s
Syed Jahanzaib

February 17, 2016

Hotspot self registration PHP form with captcha and SMS/Email function

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

c


TASK:

It is required that user can connect to open WiFi network (probably running Mikrotik hotspot system) and upon browsing he will see the login page with REGISTER option as well. User can self register his account on radius billing system by using PHP FORM
[for example can be used in FREE WiFi environment, exhibition, conference rooms, resorts etc].

It is also useful if you want to provide trial/free internet access but want to log all the required info , like mobile numbers, contacts, and there usage with control.

Upon submissions, PHP form will execute Linux bash script with the supplied data, and then it should perform various checks , example.

  • User must enter no less or more  then 11 numeric digits in MOBILE field.
  • User must enter no less or more  then 13 numeric digits in CNIC field.
  • User must enter correct CAPTCHA image code in order to submit the form. It is required in order to prevent BRUTE force attack using automated scripting.
  • If the user is registering for the first time, his account will be registered with specific service (the service must be added by admin, and its name is configurable in reg.sh script file. The system will send SMS (and print screen , you can configure it as per the requirements) to user mobile number supplied in the form with the login , validity and other information.
  • The user should be allowed 3 hours for the current day (or as per the profile configured in reg.sh)
  • If the user consumed 3 hours within the same date and try to register again, he will be denied with the information message that he have already registered with the same mobile and account status will be provided like still ACTIVE or EXPIRED and that he should try again next day (date).
  • If the user still have balance and account is active, he should be informed accordingly.
  • If the user have expired (dueto DATE OR UPTIME hours limit) and next date arrived, he can register again, and INFO SMS message (and print screen , you can configure it as per the requirements) will be sent to use informing that his previously registered account got renewed with the login , validity and other information.

 

Components Used:

1- Ubuntu
2- Apache Web Server
3- Radius Manager as Billing system in working condition
4- GSM Modem used to send SMS to containing the information
5- KANNEL as SMS gateway
6- Captcha code software for prevention of BRUTE force attack, Make sure to install it in /var/www/reg/securimage, test it to make sure its showing images properly. you can install captcha by following from https://aacable.wordpress.com/2015/08/12/passing-php-variables-to-shell-script-example-renew-account-via-web/
7 – Radius manager service profile, like as showed in the image below …


 

SCRIPTS:

Following PHP page and scripts should be copied to /var/www/reg folder [for ubuntu]

  1. user.php [User Input Registration Form]
  2. reg-display.php [Displays the User Input Result and execute the Script which executes the action]
  3. reg.sh [main script that executes the action based on supplied information by reg-display.sh]

 


 

 

1- user.php [User Input Registration Form]


<!DOCTYPE html>
<html>
<head>
<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.5/jquery.min.js"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js"></script>
<script>
$(document).ready(function() {
$("#start_datepicker").datepicker({ dateFormat: 'yy-mm-dd' });
$("#end_datepicker").datepicker({ dateFormat: 'yy-mm-dd' }).bind("change",function(){
var minValue = $(this).val();
minValue = $.datepicker.parseDate("yy-mm-dd", minValue);
minValue.setDate(minValue.getDate()+1);
$("#to").datepicker( "option", "minDate", minValue );
})
});
</script>
</head>
<body style="font-size:62.5%;">
<form action="reg-display.php" method="post">
<h1><font color="blue">Register yourself to get one hour internet access for current day.<br></font> <br><br>

<pre>
MOBILE NUMBER    : <input pattern=".{11,11}" onkeypress='return event.charCode >= 48 && event.charCode <= 57' name="mobile" cnic="11 characters minimum" maxlength="11">  [11 Numeric Digits Only Without dash/space]

CNIC NO        : <input pattern=".{13,13}" onkeypress='return event.charCode >= 48 && event.charCode <= 57' name="cnic" cnic="13 characters minimum"maxlength="13">  [13 Numeric Digits Only Without dash/space]

FIRST NAME    : <input type="text" name="firstname">

LAST NAME    : <input type="text" name="lastname">

<br></h1>
</pre>
<input type="submit" value="Submit:">

<img id="captcha" src="/securimage/securimage_show.php" alt="CAPTCHA Image" />
<input type="text" name="captcha_code" size="10" maxlength="6" />
<a href="#" onclick="document.getElementById('captcha.).src = '/securimage/securimage_show.php?. + Math.random(); return false">[ Different Image ]</a>

</form>
</body>
<br>
<br>

<?php
$ip = "";
if (!empty($_SERVER["HTTP_CLIENT_IP"]))
{
//check for ip from share internet
$ip = $_SERVER["HTTP_CLIENT_IP"];
}
elseif (!empty($_SERVER["HTTP_X_FORWARDED_FOR"]))
{
// Check for the Proxy User
$ip = $_SERVER["HTTP_X_FORWARDED_FOR"];
}
else
{
$ip = $_SERVER["REMOTE_ADDR"];
}
echo "<pre><h2>YOUR IP Address is        : $ip</h2></pre>";
?>

<h3><font color="red">This System is powered by Syed_Jahanzaib aacable@hotmail.com
</html>


 

 

2- reg-display.sh [Displays the User Input Result and execute the Script which executes the action]


<?php

include_once $_SERVER['DOCUMENT_ROOT'] . '/securimage/securimage.php';
$securimage = new Securimage();
if ($securimage->check($_POST['captcha_code']) == false) {
echo "The CAPTCHA security code entered was incorrect. Make Sure You are HUMAN - zaib!<br /><br />";
echo "Please go <a href='javascript:history.go(-1)'>back</a> and try again.";
exit;
}

$MOBILE = $_POST['mobile'];
$CNIC = $_POST['cnic'];
$FIRSTNAME = $_POST['firstname'];
$LASTNAME = $_POST['lastname'];

$ip = "";

if (!empty($_SERVER["HTTP_CLIENT_IP"]))
{
//check for ip from share internet
$ip = $_SERVER["HTTP_CLIENT_IP"];
}
elseif (!empty($_SERVER["HTTP_X_FORWARDED_FOR"]))
{
// Check for the Proxy User
$ip = $_SERVER["HTTP_X_FORWARDED_FOR"];
}
else
{
$ip = $_SERVER["REMOTE_ADDR"];
}

echo "<h2><u>You have entered the following information:</u></h2>";
echo "<pre>Mobile        : $MOBILE</pre>";
echo "<pre>CNIC No        : $CNIC</pre>";
echo "<pre>Firstname    : $FIRSTNAME</pre>";
echo "<pre>Lastname    : $LASTNAME</pre>";
echo "<pre>IP Address is    : $ip</pre>";


echo "<h2><u>BILLING RESPONSE</u></h2>";
$var = shell_exec("TERM=xterm /var/www/reg/reg.sh $MOBILE $CNIC $ip $FIRSTNAME $LASTNAME");
echo "<pre>$var</pre>";

?>


 

3- reg.sh [main script that executes the action based on supplied information by reg-display.sh]


#!/bin/sh
# CREATE NEW USER VIA PHP/HTML INPUT FORMS AND ADD IN MYSQL DB, MADE FOR RADIUS MANAGER
# LOTS OF VAROIUS CHECKS added
# CREATED : 17-FEB-2016
# LAST MODIFIED =
# set -x

# ========================================================================================================================
# Service name (exact) you want to provide to temporary hotspot users - CHANGE IT FOR SURE - valid for RADIUS MANAGER only
SRVNAME="hotspot1mb"
# ========================================================================================================================


SQLUSER="root"
SQLPASS="MYSQL_PASSWORD"
COMPANY="JAHANZAIB-PVT-LTD"
NEXTEXPIRYADD=$(date +"%Y-%m-%d" -d "+1 days")
CURDATE=$(date +"%Y-%m-%d")
CURDATEWOD=`date +%Y%m%d`
TIME=`date | awk {'print $4'}`

# KANNEL RELATED INFORMATION
KID="kannel"
KPASS="KANNEL_PASSWORD"
KHOST="127.0.0.1"

#################################
###### DONOT EDIT BELOW  ########
#################################

# Create temporary holder if not already there, for the user data and empty it as well
touch /tmp/$1.txt
> /tmp/$1.txt
echo $1 $2 $3 $4 $5  >> /tmp/$1.txt

MOBILE=`cat /tmp/$1.txt |awk '{print $1}'`
CNIC=`cat /tmp/$1.txt |awk '{print $2}'`
IP=`cat /tmp/$1.txt |awk '{print $3}'`
FIRSTNAME=`cat /tmp/$1.txt |awk '{print $4}'`
LASTNAME=`cat /tmp/$1.txt |awk '{print $5}'`

# Look for user service ID - Example 1mbps service
SRVID=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT srvid FROM radius.rm_services WHERE rm_services.srvname = '$SRVNAME';" |awk 'FNR == 2 {print $1}'`

# Look for user Registration date
REGDATE=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT createdon FROM radius.rm_users WHERE rm_users.username = '$MOBILE';" |awk 'FNR == 2 {print $1}'`

# Look for registration date without digits for comparision
REGDATEWOD=`mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT createdon FROM radius.rm_users WHERE rm_users.username = '$MOBILE';" |awk 'FNR == 2 {print $1}' | sed 's/-//g'`

#LOOK FOR VALID USER IN RADIUS
USRVALID=`mysql -uroot -p$SQLPASS -e "use radius; SELECT username FROM radius.rm_users WHERE rm_users.username = '$MOBILE';" |awk 'FNR == 2 {print $1}'`

# Look for EXPIRATION
CUREXPIRY=`mysql -uroot -p$SQLPASS --skip-column-names  -e "use radius; SELECT expiration FROM radius.rm_users WHERE rm_users.username = '$MOBILE';"`

# Look for UPTIME Limit - usually in hour unit format e.g : 1 , also it will be shown for user friendly format later
UPTIMELIMIT=`mysql -uroot -p$SQLPASS --skip-column-names  -e "use radius; SELECT timeunitonline FROM rm_services WHERE srvid = '$SRVID';"`

# Get UPTIME limit in seconds so that it can etner correctly in user propfile, seconds format also required for MYSQL TABLE for correct entry
FUPTIME=`echo "$UPTIMELIMIT*60*60" | bc`

# Welcome Message for NEW or returning User
if [ "$MOBILE" = "$USRVALID" ]; then
echo "Welcome back Mr. $FIRSTNAME $LASTNAME!"
else
echo "Welcome NEW User"
fi

# Check for registered Date, if no previous registeration date found, then treat user as NEW
if [ -z "$REGDATEWOD" ]; then
echo "No registration Date found previosly, treating it as a new user. proceed to new create new user"

# Add user in MYSQL TABLE now
mysql -u$SQLUSER -p$SQLPASS -e "use radius; INSERT INTO rm_users (
username, password, downlimit, uplimit, comblimit, firstname, lastname, company,
phone, mobile, email, address, city, zip, country, state, comment, mac, expiration,
enableuser, usemacauth, uptimelimit, srvid, staticipcm, staticipcpe, ipmodecm, ipmodecpe,
createdon, acctype, createdby, taxid, maccm, credits, owner, groupid, custattr, poolidcm, poolidcpe,
contractid, contractvalid, gpslong, gpslat, alertemail, alertsms, lang)
VALUES (
'$MOBILE', MD5('$MOBILE'), '0', '0', '0', '$FIRSTNAME', '$LASTNAME', '',
'', '', '', '', '', '', '', '', '', '', '$NEXTEXPIRYADD',
'1', '', '$FUPTIME', '$SRVID', '', '', '', '0', NOW(), '0',
'admin', '', '', '0.00', 'admin', '1', '', '', '',
'', '', '', '', 1, 1, 'English' );"

# Add user access in RADCHECK Table and SYSLOG as well
mysql -u$SQLUSER -p$SQLPASS -e "use radius; INSERT INTO radcheck (UserName, Attribute, op, Value) VALUES ('$MOBILE', 'Cleartext-Password', ':=', '$MOBILE');"
mysql -u$SQLUSER -p$SQLPASS -e "use radius; INSERT INTO radcheck (UserName, Attribute, op, Value) VALUES ('$MOBILE', 'Simultaneous-Use', ':=', '1');"
mysql -u$SQLUSER -p$SQLPASS -e "use radius; INSERT INTO rm_syslog (datetime, ip, name, eventid, data1) VALUES (NOW(), '$IP', 'ROBOT', '0', '$MOBILE');"
#mysql -uroot -p$SQLPASS -e "use radius; INSERT INTO tempuser (mobile, firstname, lastname, cnic, rendate) VALUES ('zaib888mobile', 'testfirst', 'testlast', '234567890', '$CURDATE $TIME');"

# Look for EXPIRATION for NEW User (account created above)
CUREXPIRY=`mysql -uroot -p$SQLPASS --skip-column-names  -e "use radius; SELECT expiration FROM radius.rm_users WHERE rm_users.username = '$MOBILE';"`

# OUTPUT RESULT FOR USER on SCREEN / OR BETTER TO SEND IT TO USER VIA SMS
echo "Dear $FIRSTNAME $LASTNAME,

Your NEW account have been successfuly registered on $COMPANY System.
You can use following login information to connect.
Your IP  = $IP
User ID  = $MOBILE
Password = $MOBILE
Validity = $CUREXPIRY
Uptime   = $UPTIMELIMIT Hours

Regard's
$COMPANY" > /tmp/$MOBILE.sms

cat /tmp/$MOBILE.sms

echo "Sending SMS to the registered Mobile number"

# Sending NEW ACCOUNT CREATION INFO  SMS to user mobile numbers
curl "http://$KHOST:13013/cgi-bin/sendsms?username=$KID&password=$KPASS&to=$MOBILE" -G --data-urlencode text@/tmp/$MOBILE.sms

else

#######################################
# Check for ACCOUNT status for ACTIVE OR EXPIRED
STATUS=""
ESTATUS=""
LTATUS=""

QSTATUS=`mysql -uroot -p$SQLPASS --skip-column-names  -e "use radius; SELECT SQL_CALC_FOUND_ROWS username, firstname, lastname, address, city, zip, country, state, phone, mobile,
email, company, taxid, srvid, downlimit, uplimit, comblimit, expiration, uptimelimit, credits, comment,
enableuser, staticipcpe, staticipcm, ipmodecpe, ipmodecm, srvname, limitdl, limitul, limitcomb, limitexpiration,
limituptime, createdon, verifycode, verified, selfreg, acctype, maccm, LEFT(lastlogoff, 10)
, IF (limitdl = 1, downlimit - COALESCE((SELECT SUM(acctoutputoctets) FROM radacct
WHERE radacct.username = tmp.username) -
(SELECT COALESCE(SUM(dlbytes), 0) FROM rm_radacct
WHERE rm_radacct.username = tmp.username), 0), 0),

IF (limitul = 1, uplimit - COALESCE((SELECT SUM(acctinputoctets) FROM radacct
WHERE radacct.username = tmp.username) -
(SELECT COALESCE(SUM(ulbytes), 0) FROM rm_radacct
WHERE rm_radacct.username = tmp.username), 0), 0),

IF (limitcomb =1, comblimit - COALESCE((SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct
WHERE radacct.username = tmp.username) -
(SELECT COALESCE(SUM(ulbytes + dlbytes), 0) FROM rm_radacct
WHERE rm_radacct.username = tmp.username), 0), 0),

IF (limituptime = 1, uptimelimit - COALESCE((SELECT SUM(acctsessiontime) FROM radacct
WHERE radacct.username = tmp.username) -
(SELECT COALESCE(SUM(acctsessiontime), 0) FROM rm_radacct
WHERE rm_radacct.username = tmp.username), 0), 0)

FROM
(
SELECT username, firstname, lastname, address, city, zip, country, state, phone, mobile, email, company,
taxid, rm_users.srvid, rm_users.downlimit, rm_users.uplimit, rm_users.comblimit, rm_users.expiration,
rm_users.uptimelimit, credits, comment, enableuser, staticipcpe, staticipcm, ipmodecpe, ipmodecm, srvname, limitdl,
limitul, limitcomb, limitexpiration, limituptime, createdon, verifycode, verified, selfreg, acctype, maccm,
mac, groupid, contractid, contractvalid, rm_users.owner, srvtype, lastlogoff
FROM rm_users
JOIN rm_services USING (srvid)

ORDER BY username ASC
) AS tmp
WHERE 1
AND username LIKE '$MOBILE%'
AND (tmp.acctype = '0'  OR tmp.acctype = '6' )
AND tmp.enableuser = 1 AND
(IF (limitdl = 1, downlimit - (SELECT COALESCE(SUM(acctoutputoctets), 0)
FROM radacct WHERE radacct.username = tmp.username) - (SELECT COALESCE(SUM(dlbytes), 0)
FROM rm_radacct WHERE rm_radacct.username = tmp.username) , 1) <= 0
OR
IF (limitul = 1, uplimit - (SELECT COALESCE(SUM(acctinputoctets), 0)
FROM radacct WHERE radacct.username = tmp.username) - (SELECT COALESCE(SUM(ulbytes), 0)
FROM rm_radacct WHERE rm_radacct.username = tmp.username) , 1) <= 0
OR
IF (limitcomb = 1, comblimit -
(SELECT COALESCE(SUM(acctinputoctets + acctoutputoctets), 0)
FROM radacct WHERE radacct.username = tmp.username) +
(SELECT COALESCE(SUM(ulbytes + dlbytes), 0)
FROM rm_radacct WHERE rm_radacct.username = tmp.username) , 1) <= 0
OR
IF (limituptime = 1, uptimelimit - (SELECT COALESCE(SUM(acctsessiontime), 0)
FROM radacct WHERE radacct.username = tmp.username) + (SELECT COALESCE(SUM(acctsessiontime), 0)
FROM rm_radacct WHERE rm_radacct.username = tmp.username) , 1) <= 0
OR
IF (limitexpiration=1, UNIX_TIMESTAMP(expiration) - UNIX_TIMESTAMP(NOW()), 1) <= 0)

LIMIT 0, 50;"`


# Store STATUS for ACTIVE OR EXPIRED in VARIABLE
if [ -z "$QSTATUS" ]; then
FSTATUS="ACTIVE"
else
FSTATUS="EXPIRED"
fi

# IF user registered today, then DONOT RE_REGISTER the USER and EXIT THE SCRIPT / zaib
if [ "$REGDATEWOD" -eq "$CURDATEWOD" ]; then
echo "Dear Mr. $FIRSTNAME $LASTNAME

INFO: This mobile number is already allowed to use intenret for today!

Account Details:
USER ID = $MOBILE
STATUS = $FSTATUS
Expiration    = $CUREXPIRY
Uptime Limit = $UPTIMELIMIT - Hours

For same day, you cannot register new account or Renew old account on same mobile number

$COMPANY
" > /tmp/$MOBILE.sms

cat /tmp/$MOBILE.sms

echo "Sending SMS to the registered Mobile number"

# Sending DENIAL (already registered and ACTIVE)  SMS to user mobile numbers
curl "http://$KHOST:13013/cgi-bin/sendsms?username=$KID&password=$KPASS&to=$MOBILE" -G --data-urlencode text@/tmp/$MOBILE.sms

exit 0
fi

# IF Account is ACTIVE AND VALID FOR TODAY, then INFORM USER AND EXIT THE SCRIPT

if [ "$FSTATUS" = "ACTIVE" ]; then
echo "Account Already ACTIVE
Validity = $CURDATE
Uptime   = $UPTIMELIMIT Hours"

exit 0
fi

# IF USER is ALREADY IN DB, AND STATUS IS EXPIRED, AND VALID FOR RENEWAL (24 HOURS PASSWED) THEN RENEW THE USER : ) / zaib
mysql -uroot -p$SQLPASS --skip-column-names  -e "use radius; UPDATE rm_users SET downlimit = '0', uplimit = '0', comblimit = '0', expiration = '$NEXTEXPIRYADD', uptimelimit = '$FUPTIME', warningsent = 0 WHERE username = '$MOBILE';"
# or IF uptime doesn't added properly, you can zero its accounting status so that no need to use uptime

# use this command update radacct set acctsessiontime=0 where username='$MOBILE';

# Add Renewal Info in SYSLOG
mysql -u$SQLUSER -p$SQLPASS -e "use radius; INSERT INTO rm_syslog (datetime, ip, name, eventid, data1) VALUES (NOW(), '$IP', 'ROBOT', '0', '$MOBILE');"

# OUTPUT RESULT FOR USER on SCREEN / OR BETTER TO SEND IT TO USER VIA SMS
echo "Dear $FIRSTNAME $LASTNAME,

INFO: Your account have been renewed successfully.
You can use following login information.

User ID  = $MOBILE
Password = $MOBILE
Validity = $CURDATE
Uptime   = $UPTIMELIMIT Hour

Regard's
$COMPANY"  > /tmp/$MOBILE.sms

# Output DATA on screen for user
cat /tmp/$MOBILE.sms

echo "Sending SMS to the registered Mobile number"
# Sending Renewal SMS to user mobile numbers
curl "http://$KHOST:13013/cgi-bin/sendsms?username=$KID&password=$KPASS&to=$MOBILE" -G --data-urlencode text@/tmp/$MOBILE.sms

fi

### THE END, SCRIPT ENDS HERE ###
### MADE BY SYED JAHANZAIB ###
### AACALBE AT HOTMAIL DOT COM ###
### https://aacable.wordpress.com ###


 

SCRIPTS OUTPUT :

 

1- User Registration Page

1- web login form

UPON SUBMISSION YOU MAY GET BELOW RESULTS (AS PER THE INPUT PROVIDED BY THE USER)

finger


 

2- Successful registration for the NEW user

2- new user registered successfully

and on SMS

m 1


 

3- Deny registration of same user for the same DATE

3- already registered account and still active

and on SMS

m 2


 

4- Allow renewal for the OLD users  if there account status is EXPIRED and registration date is not same.

4- old account got reneewedand on sms

m 3

 

DONE !


 

Regard’s
Syed Jahanzaib

February 9, 2016

Sending SMS/Email Alert For Reseller Account Renewal/Deposit

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

Following script is used to send SMS and Email alert to reseller and admin about the renewal or deposit in RE-SELLER account in radius manager.

Components used in the script:

  • Ubuntu 12.4 32bit
  • Kannel as SMS Gateway installed on radius manager server
  • Teltonika COM10 Serial Modem with local mobile operator SIM
  • sendEmail utility for sending Email

 


Note: Few other related guides.

For Kannel Installation
https://aacable.wordpress.com/2012/11/26/howto-configure-your-local-http-gateway-using-kannel-on-ubuntu/

for sendEmail Utility
https://aacable.wordpress.com/2015/11/26/bash-scheduled-script-to-check-linux-service-status-and-smsemail-while-preventing-repeated-alerts/

 


 

 

Following script is schedule to run after every 5 minutes , if it found any reseller account renewal, it will send SMS to admin and reseller plus email to admin as well , just for record purposes.


#!/bin/sh
#set -x
# BASH base SMS/EMAIL script for RESELLER ACCOUNT RENEWAL nottification 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
# CREATED on : 17th August, 2015
# Last Updated : 9-Aug-2016 / remove Balance showing time bug
SQLUSER="SQLUSER"
SQLPASS="SQLPASS"
MNT="5"
CURDATE=$(date +"%Y-%m-%d")
TIME=$(date +"%T")
COMPANY="ZABBO Company"
TO1="YOUREMAIL@hotmail.com"
FROM="YOURGMAIL@gmail.com"
GMAILPASS="GMAILPASS"
MSG="/tmp/dealer_renewal.html"
MSG2="/tmp/dealer_renewal_final.html"
CONTENT_TYPE="text"
KPASS="kannelpass"

# SMS
cell1="03333021909"


# Admin Email on which dealer renewal mail will be sent"
# Empty Previous TEMP File
> $MSG

# Add HTML TAG to preserve TAB etc

# Check User Validation, if not found exit with error , else continue
USRVALID=`mysql -u$SQLUSER -p$SQLPASS  -e  "use radius; SELECT managername, username, price, date, service from rm_invoices WHERE date >= NOW() - INTERVAL $MNT MINUTE;" |grep Reseller`
if [ ! -n "$USRVALID" ]; then
echo  "INFO: No RESELLER account have been updated in last $MNT minutes ! $COMPANY"

# Add entry in SYSLOG
#logger "INFO: No RESELLER account have been updated in last $MNT minutes ! $COMPANY"
exit 0
fi

# Fetch user account details which were created in last 5 minutes by catchign description name Reseller from rm tables in a temp file which loop will use later, by jahanzaib
mysql -u$SQLUSER -p$SQLPASS  -e  "use radius; SELECT managername, username, price, balance, mobile, service from rm_invoices WHERE date >= NOW() - INTERVAL $MNT MINUTE;" |grep Reseller > /tmp/temp

# Apply Count Loop Formula
num=0
cat /tmp/temp | while read users
do
num=$[$num+1]
mgrname=`echo $users | awk '{print $1}'`
dealer=`echo $users | awk '{print $2}'`
price=`echo $users | awk '{print $3}' |cut -f1 -d"."`
balance=`echo $users | awk '{print $4}' |cut -f1 -d"."`
mobile=`echo $users | awk '{print $5}'`
comment=`echo $users | awk '{print $6}'`

echo "Galaxy Info: Dealer Account Renewed.
Date/Time = $CURDATE $TIME
Dealer = $dealer
Amount Added = $price Rs
New Balance = $balance Rs
Added By = $mgrname
$COMPANY.
=========" >> $MSG

# Print information for screening purposes
echo "Galaxy Info: Dealer Account Renewed.
Date/Time = $CURDATE $TIME
Dealer = $dealer
Mobile = $mobile
Amount Added = $price Rs
New Balance = $balance Rs
Added By = $mgrname
$COMPANY.
========="

# Finally Send Email, if required
#/temp/sendEmail-v1.56/sendEmail -t $TO1 -u "$CURDATE / GT Billing INFO on Dealer Account Renewal" -o tls=yes -s smtp.gmail.com:587 -xu $FROM -xp $GMAILPASS -f $FROM -o message-file=$MSG  -o message-content-type=$CONTENT_TYPE
#/temp/sendEmail-v1.56/sendEmail -t $TO2 -u "$CURDATE / GT Billing INFO on Dealer Account Renewal" -o tls=yes -s smtp.gmail.com:587 -xu $FROM -xp $GMAILPASS -f $FROM -o message-file=$MSG  -o message-content-type=$CONTENT_TYPE

# Finally Send SMS if required, enabled right now
cat $MSG | curl "http://localhost:13013/cgi-bin/sendsms?username=kannel&password=$KPASS&to=$mobile+$cell1" -G --data-urlencode text@-

done

# Script Ends Here.
# Syed Jahanzaib / aacable at hotmail dot com
 

Results are as follows …

1 - email alert
2- mobile


Regard's
Syed Jahanzaib

%d bloggers like this: