Syed Jahanzaib Personal Blog to Share Knowledge !

August 20, 2015

Re-seller Daily Sales Activity Report Via Email in Billing System

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

This post is my personnel notes (for future retrieval or reference) on a script that can be used to query billing system (in this example Radius Manager) and gather data for all re-seller’s yesterday sales activity and summarize it in a file and email it to Administrator. It comes handy to get idea which dealer made how much sale with number of activated users, sale amount, balance and summarize it in the end for admin view.

As showed in the image below …

 

1

 

2

1

 


 

SCRIPT

dealer_renewal_yesterday.sh

  • mkdir /temp
  • touch /temp/dealer_renewal_yesterday.sh
  • chmod +x /temp/dealer_renewal_yesterday.sh
  • nano /temp/dealer_renewal_yesterday.sh

Paste the following data [but do make sure you modify the data like id password or other before deploying it.]


# Script to query all re-seller's account for yesterday's sale and there balances. 
# and at end, email the results to admin in html format .
# last updated: 25/08/2015
#!/bin/bash
#set -x
clear
# MYSQL USER ID PASSWORD
SQLUSER="root"
SQLPASS="YOUR_SQLPASS"

# DATE RELATED STUFF
TODAY=`date +"%Y-%m-%d"`
YESTERDAY=`date +"%Y-%m-%d" -d '-1 days'`
CURDATE=`date`

# EMAIL RELATED STUFF
TO1="aacable @ hotmail . com"
GMAILID="YOURGMAIL_ID@gmail.com"
GMAILPASS="YOURGMAIL_PASS"
CONTENT_TYPE="text/html"

# LOG FILES
FILE="/tmp/dealer_renewal_today.html"
FINALFILE="/tmp/dealer_renewal_today_final.html"
CSHORT="YOUR_COMPANY_NAME"
COMPANY="$CSHORT_Pvt_Ltd.<br>This System is powered by Syed_Jahanzaib aacable @ hotmail.com"
BODY_TITLE="<h1>Report&nbsp;For&nbsp;Dealer&nbsp;Account&nbsp;asof&nbsp;$YESTERDAY</h1>"


> $FILE
> $FINALFILE

echo "<pre>" > $FILE
echo "<b>$BODY_TITLE</b>" >> $FILE
echo "<b>DEALER&nbsp;            User's_Activated             Used_Amount             &Tab;Balance</b><br>" >> $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 -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT SQL_CALC_FOUND_ROWS rm_invoices.managername, rm_invoices.username, rm_invoices.date, rm_invoices.expiration, rm_invoices.service, rm_invoices.amount, rm_invoices.price FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$YESTERDAY' AND date <= '$TODAY' AND (paymode = '0'  OR paymode = '2' ) AND (invgroup = '0'  OR invgroup = '1' ) AND invnum != '' AND rm_invoices.managername = '$DEALER' ORDER BY id LIMIT 0, 500;" | sed '/credited/d' | wc -l`
USEDAMOUNT=`mysql -u$SQLUSER -p$SQLPASS --skip-column-names -e "use radius; SELECT SQL_CALC_FOUND_ROWS rm_invoices.price, rm_invoices.id, rm_invoices.invnum, rm_invoices.managername, rm_invoices.username, rm_invoices.date, rm_invoices.bytesdl, rm_invoices.bytesul, rm_invoices.bytescomb, rm_invoices.downlimit, rm_invoices.uplimit, rm_invoices.comblimit, rm_invoices.time, rm_invoices.uptimelimit, rm_invoices.days, rm_invoices.expiration, rm_invoices.comment, rm_invoices.service, rm_invoices.amount, rm_invoices.paid, rm_invoices.paymentopt, rm_invoices.paymode, rm_invoices.tax, rm_invoices.balance, rm_invoices.invgroup FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$YESTERDAY' AND date <= '$TODAY' AND (paymode = '0'  OR paymode = '2' ) AND (invgroup = '0'  OR invgroup = '1' )  AND invnum != '' AND rm_invoices.managername = '$DEALER'  ORDER BY id  LIMIT 0, 500;" | 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';" | sed '/credited/d' |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 -u$SQLUSER -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 >= '$YESTERDAY' AND date <= '$TODAY'  AND  rm_invoices.managername = '$DEALER' AND (paymode = '0'  OR paymode = '2' ) AND (invgroup = '0' ) AND invnum != ''  ORDER BY id LIMIT 0, 50;" | 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  &Tab;$BALANCE
------------------------------------------------------------------------"  >> $FILE
else

# PRINT ALL GATHERED DATA INTO FILE
echo "<b>$DEALER</b>  $ACTIVEUSERSNO  $USEDAMOUNT  &Tab;$BALANCE
<br>
Details&nbsp;of&nbsp;Services&nbsp;Activated:<br>Qty&Tab;Service&nbsp;Name<br>
$SRV
<br>------------------------------------------------------------------------" >> $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$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 >= '$YESTERDAY' AND date <= '$TODAY'  AND (paymode = '0'  OR paymode = '2' ) AND (invgroup = '0' ) AND invnum != ''  ORDER BY id LIMIT 0, 50;" | sed '/credited/d' | awk '{print $1}' | wc -l`
SALES=`mysql -uroot -p$SQLPASS --skip-column-names -e "use radius; SELECT SQL_CALC_FOUND_ROWS rm_invoices.price FROM rm_invoices LEFT JOIN rm_users ON rm_users.username = rm_invoices.username WHERE date >= '$YESTERDAY' AND date <= '$TODAY'  AND (paymode = '0'  OR paymode = '2' ) AND (invgroup = '0' ) AND invnum != ''  ORDER BY id LIMIT 0, 50;" | awk '{ sum+=$1} END {print sum}'`
echo "Total Users Activated/Renewed on $YESTERDAY     = <b>$TOTNO</b>" >> $FINALFILE
echo "Total SALES Done on $YESTERDAY                  = <b>$SALES</b>" >> $FINALFILE
echo "<br><b>$COMPANY</b>" >> $FINALFILE
echo "Generated on $CURDATE" >> $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: $CSHORT DEALERS DAILY BILLING INFO for $YESTERDAY" -o tls=yes -s smtp.gmail.com:587 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$FINALFILE  -o message-content-type=$CONTENT_TYPE

# Print and copy files as sales.html into www folder so any1 can view from webbrowser
cat $FINALFILE
cp $FINALFILE /var/www/sales.html

 

Install sendEmail Tool

mkdir /temp
cd /temp
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 [Life is really easy on ubuntu but with some glitches)

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

TEST SENDING EMAIL

Try to send email using command line: Example

/temp/sendEmail-v1.56/sendEmail -t TO_YOURMAIL@hotmail.com -u "Test Email" -s smtp.gmail.com:587 -xu YOURMGAILID@gmail.com -xp YOURGMAILPASSWORD -f  YOURMGAILIDgmail.com -o tls=yes

If you get message something like “sendEmail[xxxx]: Email was sent successfully!”, then you are good to GO LIVE !


 

Regard’s

Syed Jahanzaib

August 12, 2015

Passing PHP variables to Shell Script with CAPTCHA code [Example renew account via web]

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


For my personnel archive purpose only:

All of these tests were made in lab and later on tested on production network as well and worked perfectly. BUT before deploying it in production, one must ensure security , specially try to host it on https server, MUST add captcha in form to prevent BOTS attack, + one should consider BASH security and trimming + some functions to match with real live environment. all can be done easily if you have some knowledge on html/php/bash.


 

Scenario:

A simple portal page is required where user can input there user name and refill code in order to renew there internet account on billing system [in this example radius manager is being used]. then this html page will pass the user name and card number variable to php page which will execute an shell script to trigger renewal action based on the supplied variables. The shell script will check for following

  • Check for Valid Users name in Billing
  • Check for Valid Card number in billing refill card database
  • Check if card is used or not
  • Check the user current package and compare it with the card value
  • If all OK, renew the user account for next 30 days (or whatever actions is required)
  • Output the result to browser

 


 

Following file will present FORM where user can enter there user name and pin code/refill code.

input.html

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Refill your account ! </title>
</head>
<body>
<h1>Refill your account using scratch code:</h1>
<form method="post" action="function.php">
User Name: <br />
<input type="text" name="USERNAME" size="35" />
<br />
Card No: <br />
<input type="text" name="CARDNO" size="35" />
<br /> <br />
<input type="submit" value="Submit:" />
<br />
</form>
</body>
</html>

Following file will execute the SHELL script with the supplied username and pincode variable and echo there result in the browser.

function.php

<?php
$USERNAME = $_POST[‘USERNAME’];
$CARDNO = $_POST[‘CARDNO’];

if(empty($USERNAME ) || empty($CARDNO )) {
echo “<h2>You must fill in all fields</h2>\n” ;
die (“Click Back to start again.”);
}
echo “<h2>You have entered the following information:</h2>”;
echo “<pre>Customer name\t=\t$USERNAME <br></pre> “;
echo “<pre>Card No\t\t=\t$CARDNO</pre>”;

echo “<h2>BILLING RESPONSE</h2>”;
echo “======================”;
$var = shell_exec(“TERM=xterm /var/www/html/renew.sh $USERNAME $CARDNO”);
echo “<pre>$var</pre>”;
?>



BASH Shell script which will be executed by the function.php file

Contents of /var/www/html/renew.sh

{lab testing version, working ok, it may contain lot of junk or it can be trimmed, it’s upto you to make it look pro}

#!/bin/bash
#set -x
# SCRIPT TO RENEW USER ACCOUNT IN RADIUS MANAGER VIA WEB PORTAL
SQLUSER=”root”
SQLPASS=”zaib1234″
echo $1 $2 > /tmp/user-card
USR=`cat /tmp/user-card | awk {‘ print $1 ‘}`
CARD=`cat /tmp/user-card | awk {‘ print $2 ‘}`
NEXTEXPIRYADD=$(date +”%Y-%m-%d” -d “+31 days”)

#LOOK FOR EMPTY CARD NO IF ENTERED , EXIT
if [ “$1” == “” ]; then
echo -e “ERROR: ENTER USER NAME WITH CARD NUMBER PLEASE!”
exit 0
fi

#LOOK FOR VALID USER IN RADIUS
USRVALID=`mysql -u$SQLUSER -p$SQLPASS -e “use radius; SELECT srvid FROM radius.rm_users WHERE rm_users.username = ‘$USR’;”`
if [ “$USRVALID” == “” ]; then
echo -e “ERROR: USER NOT FOUND IN BILLING SYSTEM!!”
exit 0
fi

#LOOK FOR EMPTY CARD NO IF ENTERED , EXIT
if [ “$2” == “” ]; then
echo -e “ERROR: PLEASE ENTER CARD NUMBER!!”
exit 0
fi

# LOOK FOR USED CARDS
CARDSTATUS=`mysql -u$SQLUSER -p$SQLPASS -e “use radius; SELECT SQL_CALC_FOUND_ROWS cardnum, used, revoked, expiration, value, date, owner FROM rm_cards WHERE cardtype = ‘1’ AND cardnum = ‘$2’  ORDER BY cardnum ASC LIMIT 0, 50;” |  awk {‘print $8}’`
if [ -n “$CARDSTATUS” ]; then
echo -e “CARD IS ALREADY USED”
exit 0
fi

######################
# ACCOUNT EXPIRY CHECK
######################

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’`
SRVEXPIRYFULLD=`mysql -u$SQLUSER -p$SQLPASS -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:.*//’`
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”.”`
CARDPRICE=`mysql -u$SQLUSER -p$SQLPASS -e “use radius;  SELECT value FROM rm_cards WHERE cardnum = $CARD;” |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))
# Acount Registration FIRST n LAST NAME
USERFLNAME=`mysql -u$SQLUSER -p$SQLPASS -e “use radius; SELECT firstname,lastname FROM radius.rm_users WHERE rm_users.username = ‘$1’;” |awk ‘FNR == 2 {print $1,$2,$3}’;`

# LOOK FOR VALID REFILL CARD CODE IN RADIUS CARDS LIST
CARDVALIDATION=`mysql -u$SQLUSER -p$SQLPASS -e “use radius; SELECT value, expiration FROM rm_cards WHERE cardnum = ‘$CARD’ AND used = ‘0000-00-00 00:00:00’;”`
if [ “$CARDVALIDATION” == “” ]; then
echo -e “ERROR: INVALID CARD NUMBER!”
exit 0
else

# IF CARD VALUE IS LESS THEN CURRENT PACKAGE PRICE THEN PRINT ERROR AND GOTO END
if [ $CARDPRICE -lt $SRVPRICE ]
then
echo -e “ERROR: CARD PRICE IS NOT SUFFICIENT TO REFRESH $PKGNAME SERVICE”
exit 0
else

# IF CARD VALUE IS EQUAL OR HIGHER  THEN CURRENT PACKAGE PRICE THEN OK
if [ $CARDPRICE -eq $SRVPRICE ]
then
echo
fi

########### ACCOUNT STATUS EXPIRED TODAY ACTION ############
if [ $SRVEXPIRY -eq $TODAYDIGIT ]
then
echo “Account have been EXPIRED TODAY! Last LOGOUT date was $LOGOFFDATE”
NEXTEXPIRYADD=$(date +”%Y-%m-%d” -d “+31 days”)

# PRINT FETCHED VALUES , JUST FOR INFO / ZAIB
echo User Account  = $USR
echo User Actual Package at Billing = $PKGNAME PKR
echo Service Price at Billing = $SRVPRICE PKR
echo This Card Value is    = $CARDPRICE PKR
echo -e “Next Expiry =  $NEXTEXPIRYADD”

# 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 scratch code by SMS’ 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’, ‘SMSUSER_$USR’, ‘$USR’, ‘$USR renewd service > $PKGNAME’);”

# ADD ENTRY FOR CURRENT DATE TIME IN REFIL CARD TO PREVENT RE-USAGE OF SAME CARD NUMBER
mysql -u$SQLUSER -p$SQLPASS -e “use radius; UPDATE rm_cards SET owner = ‘$USR’, used = NOW() WHERE cardnum = ‘$CARD’;”

########### ACCOUNT STATUS EXPIRED IN PAST ACTION ############

elif [ $SRVEXPIRY -lt $TODAYDIGIT ]
then
echo “ACCOUNT WAS EXPIRED on $SRVEXPIRYFULL !  Last LOGOUT date was $LOGOFFDATE”
NEXTEXPIRYADD=$(date +”%Y-%m-%d” -d “+31 days”)

# PRINT FETCHED VALUES , JUST FOR INFO / ZAIB
echo User Account  = $USR
echo User Actual Package at Billing = $PKGNAME PKR
echo Service Price at Billing = $SRVPRICE PKR
echo This Card Value is    = $CARDPRICE PKR
echo -e “Next Expiry =  $NEXTEXPIRYADD”

# 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 scratch code by SMS’;”

# ADD SYSLOG ENTRY
mysql -u$SQLUSER -p$SQLPASS -e “use radius; INSERT INTO rm_syslog (datetime, ip, name, eventid, data1) VALUES (NOW(), ‘n/a’, ‘SMSUSER_$USR’, ‘$USR’, ‘$USR renewd service > $PKGNAME’);”

# ADD ENTRY FOR CURRENT DATE TIME IN REFIL CARD TO PREVENT RE-USAGE OF SAME CARD NUMBER
mysql -u$SQLUSER -p$SQLPASS -e “use radius; UPDATE rm_cards SET owner = ‘$USR’, used = NOW() WHERE cardnum = ‘$CARD’;”

# Update QUOTA for the USER
mysql -u$SQLUSER -p$SQLPASS -e “use radius; UPDATE rm_users SET comblimit = ‘$PKGQUOTAB’ WHERE username = ‘$USR’;”

else
########### ACCOUNT STATUS OK! ACTION ############

echo -e “User Billing Info:”
echo “Account STATUS= OK!”

NEXTEXPIRYADD=`mysql -u$SQLUSER -p$SQLPASS -e “use radius; select DATE_ADD(expiration, INTERVAL 31 DAY) as x from rm_users where username= ‘$USR’;” |awk ‘FNR == 2’`

# PRINT FETCHED VALUES , JUST FOR INFO / ZAIB
echo User Account  = $USR
echo Owner = $USERFLNAME
echo User Actual Package at Billing = $PKGNAME PKR
echo Service Price at Billing = $SRVPRICE PKR
echo This Card Value is    = $CARDPRICE PKR
echo -e “Next Expiry =  $NEXTEXPIRYADD”

NEXTEXPIRYADD=`mysql -u$SQLUSER -p$SQLPASS -e “use radius; select DATE_ADD(expiration, INTERVAL 31 DAY) as x from rm_users where username= ‘$USR’;” |awk ‘FNR == 2’`

# 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 scratch code by SMS’ 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’, ‘SMSUSER_$USR’, ‘$USR’, ‘$USR renewd service > $PKGNAME’);”

# ADD ENTRY FOR CURRENT DATE TIME IN REFIL CARD TO PREVENT RE-USAGE OF SAME CARD NUMBER
mysql -u$SQLUSER -p$SQLPASS -e “use radius; UPDATE rm_cards SET owner = ‘$USR’, used = NOW() WHERE cardnum = ‘$CARD’;”

fi
fi
fi

########### ACCOUNT STATUS EXPIRED TODAY ACTION ############
if [ $PKGQUOTA -eq 0 ]
then
echo -e “Total Quota Allowed = No Quota”
else
echo -e “Total Quota Allowed = $PKGQUOTAB GB”
fi
echo -e “Done/Note: Card Number $CARD is marked as used in DB to prevent re-usege”


 

RESULTS:

1- enter details


 

If the script found that the user name not valid in the billing , spit the error

0- user not found


 

If the script found that the card number is not available in the billing , spit the error

2- invalid number


 

If the script found that the card number entered is already used , spit the error

3- card already used


 

If the script found both fields blank, spit the error

4- you must fill in all fields


 

If the script found user name and card matches, then proceed to renew the account

5- if all ok renew the account

You can also take different actions like send Email / SMS to ADMIN, and user both or any other action.


 


 


 


 


 

re-captcha

ADDING CAPTCHA SECURITY IN FORM

To add captcha security in html form, (which should be must in my opinion for security reasons)

Download secureimage and unzip in your web folder like /var/www/html/secureimage

mkdir /temp

cd /temp

wget https://www.phpcaptcha.org/latest.tar.gz

tar zxvf latest.tar.gz

mv securimage/ /var/www/html/

Now edit the html form to add the captcha facility

TEST.HTML [Red highlighted are our code for captcha]

<html>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″ />
<title>Refill your account ! </title>
</head>
<body>
<h1>Refill your account using scratch code:</h1>
<form method=”post” action=”test.php”>
User Name: <br />
<input type=”text” name=”USERNAME” size=”35″ />
<br />
Card No: <br />
<input type=”text” name=”CARDNO” size=”35″ />
<br /> <br />
<input type=”submit” value=”Submit:” />
<br />
</body>
<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>
</html>

TEST.PHP [Red highlighted are our code for captcha]

<?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;
}
$USERNAME = $_POST[‘USERNAME’];
$CARDNO = $_POST[‘CARDNO’];
if(empty($USERNAME ) || empty($CARDNO )) {
echo “<h2>You must fill in all fields</h2>\n” ;
die (“Click Back to start again.”);
}
echo “<h2>You have entered the following information: zaib</h2>”;
echo “<pre>Customer name\t=\t$USERNAME <br></pre> “;
echo “<pre>Card No\t\t=\t$CARDNO</pre>”;
echo “<h2>BILLING RESPONSE</h2>”;
echo “======================”;
$var = shell_exec(“TERM=xterm /var/www/html/renew.sh $USERNAME $CARDNO”);
echo “<pre>$var</pre>”;
?>

Now result would be as follow

captcha

captcha-wrong


Regard’s
Syed JAHANZAIB

August 7, 2015

CENTOS: Sending Email using SENDMAIL, Relay via GMAIL

Filed under: Linux Related — Tags: , — Syed Jahanzaib / Pinochio~:) @ 9:44 AM

gmail-logo

 

on request.

To send Email from Linux (Centos) shell using simple mail commands, you need to setup sendmail and use G-MAIL as relay (comes in handy). This was also required in radius manager setup where radius system sends various alerts to user like expiry alerts, quota alerts and my own customized alerting events.

First install required items.

yum -y install sendmail mailutils mailx sendmail-bin sendmail-cf cyrus-sasl-plain

Now issue following command to create Gmail authentication file in a folder in which you will add Gmail user name and password.

mkdir -m 700 /etc/mail/authinfo/

cd /etc/mail/authinfo/

Next we need to create an authentication file with following contents. You can name it as you like.
In this example I have configured it gmail-idpass:

Create file

nano gmail-idpass

and add following

AuthInfo: "U:root" "I:YOURGMAILID@GMAIL.COM" "P:YOURGMAILPASS"

[Note: Replace the above with your gmail id pass]

Save and Exit.

In the next step we will need to create a hash map for the above authentication file:

 makemap hash gmail-idpass < gmail-idpass 

Configure your sendmail

Now add bellow lines into your /etc/mail/sendmail.mc configuration file. Make sure you add them at end, but right above first “MAILER” definition line:

Example your file may look like this before editing (last few lines)


dnl MASQUERADE_DOMAIN(localhost)dnl
dnl MASQUERADE_DOMAIN(localhost.localdomain)dnl
dnl MASQUERADE_DOMAIN(mydomainalias.com)dnl
dnl MASQUERADE_DOMAIN(mydomain.lan)dnl

MAILER(smtp)dnl
MAILER(procmail)dnl
dnl MAILER(cyrusv2)dnl

You need to add the following lines above MAILER(smtp)dnl line

nano /etc/mail/sendmail.mc 

now paste following

# Adding config for gmail #
define(`SMART_HOST',`[smtp.gmail.com]')dnl
define(`RELAY_MAILER_ARGS', `TCP $h 587')dnl
define(`ESMTP_MAILER_ARGS', `TCP $h 587')dnl
define(`confAUTH_OPTIONS', `A p')dnl
TRUST_AUTH_MECH(`EXTERNAL DIGEST-MD5 CRAM-MD5 LOGIN PLAIN')dnl
define(`confAUTH_MECHANISMS', `EXTERNAL GSSAPI DIGEST-MD5 CRAM-MD5 LOGIN PLAIN')dnl
FEATURE(`authinfo',`hash -o /etc/mail/authinfo/gmail-idpass.db')dnl
# End config for gmail #

Save and exit.

Now your sendmail.mc file will look a like as following after editing (last few lines)


dnl MASQUERADE_DOMAIN(localhost)dnl
dnl MASQUERADE_DOMAIN(localhost.localdomain)dnl
dnl MASQUERADE_DOMAIN(mydomainalias.com)dnl
dnl MASQUERADE_DOMAIN(mydomain.lan)dnl

# Adding config for gmail #
define(`SMART_HOST',`[smtp.gmail.com]')dnl
define(`RELAY_MAILER_ARGS', `TCP $h 587')dnl
define(`ESMTP_MAILER_ARGS', `TCP $h 587')dnl
define(`confAUTH_OPTIONS', `A p')dnl
TRUST_AUTH_MECH(`EXTERNAL DIGEST-MD5 CRAM-MD5 LOGIN PLAIN')dnl
define(`confAUTH_MECHANISMS', `EXTERNAL GSSAPI DIGEST-MD5 CRAM-MD5 LOGIN PLAIN')dnl
FEATURE(`authinfo',`hash -o /etc/mail/authinfo/gmail-idpass.db')dnl
# End config for gmail #

MAILER(smtp)dnl
MAILER(procmail)dnl
dnl MAILER(cyrusv2)dnl

Now we need to re-build sendmail’s configuration. Run following command to do so.

 make -C /etc/mail 

Reload sendmail service:

 /etc/init.d/sendmail reload 

TEST VIA SENDING MAIL FROM SHELL

 

Now you can send email from your command line using mail command:


echo  "Mail Body - Test Message from CENTOS Shell by zaib" | mail -s "Subjct is Mail Sending from CLI" -r  yourgmailid  to-address@hotmail.com

This will send email to aacable @ hotmail.com

TO check its status via Logs, you can view file

tail -f /var/log/mailllog

and you may see something following on successfully sent message


Aug  6 08:51:10 radius-master sendmail[2800]: t76FpAGJ002800: from=yourgmailid@gmail.com, size=379, class=0, nrcpts=1, msgid=<55c3826e.HFMzaRe3xPHSfUV4%yourgmailid@gmail.com>, relay=root@localhost
Aug  6 08:51:11 radius-master sendmail[2801]: t76FpAS5002801: from=<yourgmailid@gmail.com>, size=527, class=0, nrcpts=1, msgid=<55c3826e.HFMzaRe3xPHSfUV4%yourgmailid@gmail.com>, proto=ESMTP, daemon=MTA, relay=localhost [127.0.0.1]
Aug  6 08:51:11 radius-master sendmail[2800]: t76FpAGJ002800: to=to-address@hotmail.com, ctladdr=yourgmailid@gmail.com (0/0), delay=00:00:01, xdelay=00:00:01, mailer=relay, pri=30379, relay=[127.0.0.1] [127.0.0.1], dsn=2.0.0, stat=Sent (t76FpAS5002801 Message accepted for delivery)

Aug  6 08:51:12 radius-master sendmail[2803]: STARTTLS=client, relay=gmail-smtp-msa.l.google.com., version=TLSv1/SSLv3, verify=FAIL, cipher=ECDHE-RSA-AES128-GCM-SHA256, bits=128/128
Aug  6 08:51:13 radius-master sendmail[2803]: t76FpAS5002801: to=<to-address@hotmail.com>, delay=00:00:03, xdelay=00:00:02, mailer=relay, pri=120527, relay=gmail-smtp-msa.l.google.com. [64.233.167.108], dsn=2.0.0, stat=Sent (OK 1438920803 by17sm6409593wib.18 - gsmtp)


 

You can also mail any command output like this

uptime | mail -s “UPTIME status for your server” -r  yourgmailid@gmail.com  to-address@hotmail.com

1


 

Done.

TIP: For radius manager mail function, as far as i remember, you dont need to modify any setting, rm will use localhost as mail sending feature which will afterwards relayed by sendmail using gmail.

 

Regard’s
Syed Jz

August 6, 2015

Enabling Authentication Logs in Freeradius

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

logs-error

Sometimes in freeradius base billing system, user is unable to authenticate with the system. To quickly investigate the issue, its better to enable freeradius authentication logs to see if its the user end id password issue or something else.

To enable Free- Radius LOGS to get additional information on users authentication ,

Edit /usr/local/etc/raddb/radiusd.conf

nano /usr/local/etc/raddb/radiusd.conf

and modify following

auth = no
auth_badpass = no
auth_goodpass = no

to following

auth = yes
auth_badpass = yes
auth_goodpass = yes

Save and Exit.

Now restart radius service by

service radiusd restart

Check Logs by

tail -f /usr/local/var/log/radius/radius.log

and you will AUTH logs for Good and Bad Login Attempts, It helps a lot in troubleshooting troubled users.

Thu Aug  6 14:52:06 2015 : Auth: Login OK: [usernameX/username] (from client CCR-GW port 15747979 cli xx:D1:11:64:B8:39)
Thu Aug  6 14:52:07 2015 : Auth: Login OK: [usernameX/username] (from client CCR-GW port 15747975 cli xx:44:76:72:A7:9C)
Thu Aug  6 14:52:08 2015 : Auth: Login OK: [usernameX/username] (from client CCR-GW port 15747978 cli xx:44:76:72:9E:9C)

Thu Aug  6 14:58:48 2015 : Auth: Login incorrect: [usernameY<via Auth-Type = mschap>] (from client pppoe2 port 16056177 cli xx:DE:27:2F:23:95)
Thu Aug  6 14:58:49 2015 : Auth: Login incorrect: [usernameZ/<via Auth-Type = mschap>] (from client pppoe1 port 15819569 cli xx:F3:C1:AD:70:17)

 

Regard’s

Syed Jahanzaib

 

 

July 30, 2015

playSMS Send SMS via Script/CLI using webservices token and PHP

Filed under: Linux Related — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 10:56 AM

playSMS_logo_full

If you want to send SMS via playSMS using bash script or CLI, use the WEB SERVICES options in playSMS to facilitate this function. Its very useful in many scenarios.

Example:

I wanted to schedule a bash script which should run daily twice , this script collects various information of different servers, routers and then summarize it and send to admin via SMS. I could do it with GAMMU or other sms tools too, but the issue was that the server already have KANNEL with gsm modem configured and (in general standard) Kannel dont accept message with special characters, spaced, and strange formats that this script output, (with urlencoding possible but i dont want to do it) or I had to use sed awk and other tools to proper format them which was overhead work. and I wanted to utilized KANNEL or playSMS.

Mr. Anton from playSMS suggested to use web-services of playSMS and it worked beautifully :)

This is how it was done.


 

1- Enable WEB SERVICES & GENERATE TOKEN

Goto My Account > User Configuration

Select YES from drop down menu for “Enable webservices” and “Renew webservices token

Click on SAVE.

As showed in the following image below …

 

1- token


 

Now on same page you will now see the TOKEN number as showed in the following image below …

2 token

Copy / note down this this number, It will serve as a kind of password to let us send sms via php script.


 

2- Create & schedule  PHP script to execute your shell script

Now we have to create a PHP script which will use the above TOKEN to let us run the bash script and send it’s output to admin mobile number :)

mkdir /temp
touch /temp/dailysms.php
chmod +x /temp/dailysms.php
nano /temp/dailysms.php

now paste the following date , but be sure to modify the token number as per your own setup, and the script which you want to execute via php.

#!/usr/bin/php -q
# Script provided by mr.anton
# https://forum.playsms.org/t/schedule-message-to-run-sms-command/194/2
# https://aacable.wordpress.com
# Dated: 30th July, 2015
# Syed Jahanzaib

<?php

/**
 * cli2sms.php by Anton Raharja (antonrd@gmail.com)
 * Example script to get data from shell script and send it as SMS via playSMS
 * You need to have a configured and working playSMS
 * In this example playSMS is accessible from http://localhost/playsms
 * Don't forget to chmod +x cli2sms.php to use it from Linux shell
 *
 * You may modify this script to suit your needs
 *
 * Example usage:
 * - get stat data (eg: uptime) and send it periodically (using cron) to admin's mobile phones
 * - https://aacable.wordpress.com/2015/07/30/playsms-send-sms-via-scriptcli-using-webservices-token/
 */

// suppress error message
error_reporting(0);

// playSMS username/account for sending SMS
$username = 'admin';

// Webservices token for above username
$token = '1194df9e20d06c3790f0c6fef49f174a';

// playSMS Webservices URL
$playsms_ws = 'http://localhost/playsms/index.php?app=ws';

// destination numbers, comma seperated or use #groupcode for sending to group
// $destinations = '#devteam,+6200123123123,+6200456456456';
// $destinations = '+6200123123123,+6200456456456'; # for multiple recipients</pre>
<pre>$destinations = '03333021909';</pre>
<pre>
// get message to send from another shell script or Linux command, for example 'uptime'
// $message = trim(shell_exec('uptime'));
$message = trim(shell_exec('/temp/dailysms.sh'));</pre>
<pre>
// send via playSMS HTTP API
if ($message) {
    $ws = $playsms_ws . '&u=' . $username . '&h=' . $token . '&op=pv';
    $ws .= '&to=' . urlencode($destinations) . '&msg='.urlencode($message) . '&nofooter=1';
    $ret = @file_get_contents($ws);

    // echo $ret;
    echo "OK: message sent" . PHP_EOL;
} else {
    echo "ERROR: message is empty" . PHP_EOL;
}

// end of script

 

 


TEST

Now test by running the php file we just created above.

/temp/dailysms.php

and you should see something like below if every thing goes smoothly as planned

root@radius:/temp# ./dailysms.php

OK: message sent

 

and you will soon receive the SMS on your Mobile.

3- mobile


Update: / TIPS
Later I found howto submit sms via kannel CLI as well. Described in the below link.

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

How to Send SMS via HTTP command

Open your browser and paste the following command.

http://101.11.11.250:13013/cgi-bin/sendsms?username=kannel&password=kannel&to=03333021909&text=ZAIB+KANNEL+WORK

Howto send SMS via GAMMU CLI

gammu sendsms TEXT 03333021909 -text "Test Msg from GAMMU  .."

Howto send USSD CODES via GAMMU CLI

For balance check (below example is for zong balacne check)

gammu --getussd *222#

Howto send sms via CLI using KANNEL

curl "http://localhost:13013/cgi-bin/sendsms?username=kannel&password=KANNELPASS&to=03333021909&text=Welcome+Jahanzaib+Test+Message"

Sending mesage with new line using %0A code using KANNEL

curl "http://localhost:13013/cgi-bin/sendsms?username=kannel&password=KANNELPASS&to=03333021909&text=Welcome+Jahanzaib+Test+Message%0ANew+line"

Howto send sms using FILE via CLI using KANNEL

curl "http://127.0.0.1:13013/cgi-bin/sendsms?username=kannel&password=kannelpassword&to=03333333333" -G --data-urlencode text@/temp/message.txt

Howto send command output  sms via CLI using KANNEL

ls -l | curl "http://127.0.0.1:13013/cgi-bin/sendsms?username=kannel&password=kannelpassword&to=03333333333" -G --data-urlencode text@-

 

Regard’s

Syed Jahanzaib

July 24, 2015

Rough Notes on Sending Email Alert Upon Service(s) Failure in Linux

Filed under: Linux Related — Tags: — Syed Jahanzaib / Pinochio~:) @ 10:18 AM

1

[Not for all as its just a messy type of temporary work] This script was made for an operator who was facing strange problem that in random timings , the mysql or some times radiusd service stopped automatically, unfortunately the error was not sorted out as it occurs in awkward timings when no one is at NOC, so I made an workaround in the form of this script so that we can diagnose the issue later in details and in the meanwhile manually startup of services should not be required. The script was scheduled to run after every 10 minutes.

Note: This is not a solution, Just a workaround for very small period of time.

This script will check one or more services status, if found stopped, it will try to re-start them, and then check there status again and output there status in email.

In this example I took MYSQL and RADIUS services.

Sections:

  1. Create Required Scripts
  2. Download sendEmail tool to send email alerts
  3. Check Service status
  4. Another version of script [spicework] with prevention of repeated email in case of scheduler

 

1- Create Required Scripts

Create New  script checkstatus.sh which will be check the required services status and will output there result in text file which will be later used to send email (only if services found stopped)

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

Now add following contents in it

#!/bin/bash
SRV1="radiusd"
SRV2="mysqld"
SRV2D="mysql"
pid1=`pidof $SRV1`
pid2=`pidof $SRV2`
CURDATE=`date`

# Delete existing mail file
rm -fr  /temp/radiusstatus

# Check for SRV1 - RADIUSD - If not running then add its entry to local file for mail
if [ "$pid1" == "" ]; then
echo -e "$SRV1 service is NOT running, Trying to start . . ."
echo -e "$CURDATE $SRV1 service is NOT running, Trying to start . . ." >> /temp/radiusstatus
var1=$SRV1
service $SRV1 restart

# If SRV1 is running then Ignore and check for next service
else
echo $SRV1 service is Running OK , no further action required, EXITING. Script by Syed Jahanzaib
fi

# Check for SRV2 - MYSQL - If not running then add its entry to local file for mail
if [ "$pid2" == "" ]; then
echo -e "$SRV2D service is NOT running, Trying to start . . ."
echo -e "$CURDATE $SRV2D service is NOT running, Trying to start . . ." >> /temp/radiusstatus
var2=$SRV2
service $SRV2D restart

# If SRV2 is running then Ignore and check for next service
else
echo $SRV2 service is Running OK , no further action required, EXITING. Script by Syed Jahanzaib
fi

# Emal the results to admin if file exists
if [ ! -f /temp/radiusstatus ]
then
echo -e "$SRV1 and $SRV2D Services seems to be running ..."
else
echo -e "ALERT ... Services seems to be stopped, check email "

# Check Services status after starting them by calling external script
echo -e "=========================================" >> /temp/radiusstatus
echo -e "Current Status After running this script" >> /temp/radiusstatus
/temp/checkservice.sh $SRV1 >> /temp/radiusstatus
/temp/checkservice.sh $SRV2 >> /temp/radiusstatus

#Finally send email with all the data gathered
/temp/sendEmail-v1.56/sendEmail -t aacable@hotmail.com -u "$CURDATE / Billing Alert: $var1 $var2 Services found stopped and restarted now" -o tls=yes -s smtp.gmail.com:587 -xu YOURGMAIL_ID@gmail.com -xp YOURGMAILPASSWORD -f YOUTGMAIL_ID@gmail.com -o message-file=/temp/radiusstatus  -o message-content-type=text
fi

Save and Exit.


 

Now create another script checkservice.sh which will be called by above parent script to get the final status of services.

touch /temp/checkservice.sh
chmod +x /temp/checkservice.sh
nano /temp/checkservice.sh

Now add following contents in it
#!/bin/bash
PID=`pidof $1`
if [ `pgrep $1` ]
then
echo "$1 = OK   with pid $PID"
else
echo "$1 = NOT RUNNING"
fi

Save & Exit.


 

 

2- 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

TESTING SEND EMAIL

Try to send email using command line: Example

/temp/sendEmail-v1.56/sendEmail -t YOURMAIL@hotmail.com -u "Test Email" -s smtp.gmail.com:587 -xu YOURMGAILID@gmail.com -xp YOURGMAILPASSWORD -f  YOURMGAILIDgmail.com -o tls=yes

if you get message something like “sendEmail[xxxx]: Email was sent successfully!”, then you are good to GO LIVE !


 

3- Check Service status

Now run the script like (make sure mysql and radiusd services are running)

/temp/checkstatus.sh

and you will see following result if servies are already running

3- ok

and if any service found to be stopped, it will try to start it and will send you the result.
(If one service found stopped it will only try to start that service, if both services found stopped it will try to start them both)

now for test stop the mysql or radiusd service and run the script again, this time you will see the result as follows

1- statu

and at email you will see the following result

2- result

You can then schedule the service to run after every xx minutes in CRON

crontab -l

# Run after every 10 minutes , Check for stopped services of mysql and radius
*/10 * * * * /temp/checksrv.sh

4- Another version of script [spicework] with prevention of repeated email in case of scheduler

http://unix.stackexchange.com/questions/218117/howto-prevent-service-down-repeated-email-sent-alerts-via-bash

#!/bin/bash
if pgrep "mysql" > /dev/null
then
    echo "MYSQL Running"
    rm -f /var/run/.mysql_mail_sent
else
    echo "mysqld ALERT Stopped"
    if [ ! -f /var/run/.mysql_mail_sent ]; then
echo -e "Send your mysql mail here"
        date > /var/run/.mysql_mail_sent
    fi
fi
if pgrep "radiusd" > /dev/null
then
    echo "radiusd Running"
    rm -f /var/run/.radiusd_mail_sent
else
    echo "radiusd ALERT Stopped"
    if [ ! -f /var/run/.radiusd_mail_sent ]; then
echo -e "Send your radiusd mail here"
        date > /var/run/.radiusd_mail_sent
    fi
fi

 

To DO Task:

Add check to prevent re-sending of email alerts if service have permanent failure or cannot be started in any case, the script will keep sending the email :D , so a check is required. Will add it later in some free time.

 

Regard’s
Syed Jahanzaib

 

 

 

July 16, 2015

June 26, 2015

RADIUS Redundancy by using MYSQL Master-Master Replication

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

master-master

In this Guide, I will show you howto create replica of your radius server so that in case of any server failure , you can instantly switch to backup server with the latest data available. In this model we will use MYSQL master-master concept in which whatever changes / records you make on any server, it will replicate to other as well. Also in mikrotik we can use primary and secondary radius server entries OR we can make a script to detect both radius status and act accordingly, all depend on your network requirements & infrastructure.

Scenario:

In this example we have RADIUS MANAGER billing system which uses freeradius and MYSQL DB as its backend engine,  installed (with basic level of installation) on two servers. Now we want to create redundancy by replicating radius DB to each other so that in case of one server failure, second server should come to rescue.

Requirements:

  • I assume that you have working radius manager installed on both PC and tested its working by creating users in it.

Components Used:

  • SERVER1 NAME = MASTER-RADIUS
    OS = Centos 6.5 32bit
    IP = 101.11.11.241
  • SERVER2 NAME = REPLICA-RADIUS
    OS = Centos 6.5 32bit
    IP = 101.11.11.245
  • MIKROTIK PPPOE SERVER = Mikrotik
    OS = Mikrotik 5.xx
    IP = 101.11.11.255

Let’s Start

 

Step – 1

Server1 = ‘master-radius’ Configuration

Open mysql config file

nano /etc/my.cnf

and add following under [mysqld] section

log-bin=mysql-bin
binlog-do-db=radius
server-id=1
auto_increment_increment = 2
auto_increment_offset = 1

SAVE and EXIT.

Now restart mysqld service so changes can take effect.

service mysqld restart

Now we need to create a user that will be used by mysql for replicating data between our two radius (or mysql) servers. As an example I am using id “zaib”. Replace “password” with the password you wish to use for replication.

create user 'zaib'@'%' identified by 'password';
grant replication slave on *.* to 'zaib'@'%'; 

Now we need to get some information about the current MySQL instance which we will later provide to server2 (replica).

The following command will output a few pieces of important information, which we will need to make note of:

show master status;

The output will look similar to the following, and will have two pieces of critical information: [file and position note it down)

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      336 | radius       |                  |
+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

We need to make a note of the file and position which will be used in the next step.


 

Step – 2

Server2 = ‘replica-radius’ Configuration

 

Open mysql config file

nano /etc/my.cnf

and add following under [mysqld] section

log-bin=mysql-bin
binlog-do-db=radius
server-id=2
auto_increment_increment = 2
auto_increment_offset = 2

Make sure server-id is different then primary server

SAVE and EXIT.

Now restart mysqld service so changes can take effect.

service mysqld restart

Here we are going to create the user which will be responsible for the replication. Replace “password” with the password you wish to use.

create user 'zaib'@'%' identified by 'password';
grant replication slave on *.* to 'zaib'@'%'; 

The next step involves taking the information that we took a note of earlier and applying it to our mysql instance. This will allow replication to begin. The following should be typed at the mysql shell:

slave stop;

CHANGE MASTER TO MASTER_HOST = '101.11.11.241', MASTER_USER = 'zaib', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 336;

slave start; 

Your values for MASTER_LOG_FILE and MASTER_LOG_POS may differ than those above. You should copy the values that “SHOW MASTER STATUS” returns on Server-1.

 

The last thing we have to do before we complete the mysql master-master replication is to make note of the master log file and position to use to replicate in the other direction (from Server 2 to Server 1).

We can do that by typing the following:

SHOW MASTER STATUS; 

The output will look similar to the following:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      125 | radius       |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Take note of the file and position, as we will have to enter those on server 1, to complete the two-way replication.

The next step will explain how to do that.

 

Step – 3

Completing Replication on Server1 [Master-radius]

Back on Server 1, we need to finish configuring replication on the command line.

Running this command will replicate all data from Server 2.

slave stop;
CHANGE MASTER TO MASTER_HOST = '101.11.11.245', MASTER_USER = 'zaib', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000002', MASTER_LOG_POS = 125;
slave start; 

Keep in mind that your values may differ from those above. Please also replace the value of MASTER_PASSWORD with the password you created when setting up the replication user.

The output will look similar to the following:

Query OK, 0 rows affected (0.01 sec)

 

Now test the status by issuing command to mysql cli

show slave status\G

and you should see something similar to this. [don’t get confused with different numbers of log file file and position number, as this snap was taken in another lab]

replica-status


 

TEST

The last thing to do is to test that replication is working on both servers.

Open server1 radius panel, and try to create new user, after creation, it will be automatically replicated to server2 : )

As showed in the images below …

At a moment no users have been created.

server1-empty

 

Now create test user

server1-users-create

 

After creation, Goto Server2 (Replica) and check Users List, and you will find the user replicated.

server2-new0user0replicate-ok

and when you will create any user , it will replicate back to server1.


Adding both Radius Server entries in Mikrotik

Add both radius server

add-radius

and at radius manager, add the NAS (mikrotik)

add-nas

Don’t forget to rebuild clients.conf (from the menu) at secondary radius as well.

Now test by connecting any client , once successful, disconnect the primary radius, and try to connect the client again, once mikrotik will be unable to find primary entry, it will auto contact secondary server. as showed in the images below …

2radius

I will add few more details later….

 

Regard’s
Syed Jahanzaib

 

June 18, 2015

Freeradius/mysql Account Expiry SMS notification Script using ‘itelservices.net’ bulk SMS Gateway

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

sms

This post is somewhat very specific to PK base bulk sms provider API. Its a simple bash script made on someone’s request [who had a custom billing system based on freeeradius/mysql] and it can be used to send account expiry notifications to users using freeradius/mysql account query  , BUT specifically using HTTP base SMS Gateway services from http://itelservices.net/

However this specific SMS gateway was a bit different as compared to our KANNEL base gw.

  1. It requires ‘Unique transaction ID’ for each sms, therefore i used current time/seconds with username as Transaction ID
  2. The number should be in international format like 923333021909 and the problem was that the operator had simple format for mobile numbers like 03333021909 is all accounts, and it was not acceptable from the API provider, therefore as a workaround, I used awk/sed tools to remove 0 and then in curl added 92 before every number.

At the moment there are two scripts

1- SMS for account expiry notification
2- SMS for new account creation with user details if possible

You must modify the script as required. This is just a simple way to achieve this task, however there are more sophisticated method like using php or other programing language, I just prefer to select the BASH route !

 

Posting it for   H U M A S   as I love them, They’re Amazing ! :)


1- SMS for account expiry notification

 

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

Now paste the following code.

#!/bin/sh
# set -x
# BASH base SMS script for sending expiry notification for Freeradius/mysql users
# the simple logic can be applied for about any other task as well.
# I tried to make it as simple as it can be
# By Syed Jahanzaib
# Created on : 8th June, 2015
# Modified on : 18th june, 2015
# This script was specially modified for APITEL http sms gateway services
# which requires unique transaction ID each time, so i used datetimesecond feature as jugaar
# made for KHI

# MYSQL root id and password
SQLUSER="root"
SQLPASS="sqlpass"
DB="radiusdb"

# APITEL User Name & Password, must be filled
APIUSER="xxxx"
APIPASS="xxxx"
API="YOURSENDERNAME"

# Date functions to find current date, month year and Transaction id using seconds ; ) jugaar way ; )
NOW=$(date)
TID=$(date +"-%s")

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

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

# Remove 0 if any in mobile number and export it to final list
cat /tmp/list | awk '{gsub("^0","",$2); print $1,$2}' > /tmp/finallist

# Add DATE TIME in sms.log to separate date wise entries / zaib
echo ====================================================== >> /var/log/sms.log
echo $NOW >> /var/log/sms.log
echo ====================================================== >> /var/log/sms.log

# Add DATE TIME in smsapi.log to separate date wise entries WITH API STATUS for cross verification / zaib
echo ====================================================== >> /var/log/smsapi.log
echo $NOW >> /var/log/smsapi.log
echo ====================================================== >> /var/log/smsapi.log

# 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/finallist |sed '1d' |awk 'NF > 1' | while read users
do
num=$[$num+1]
username=`echo $users |awk '{print $1}'`
mobile=`echo $users | awk '{print $2}'`

# SMS Body
BODY="Soft+Reminder:+Dear+$username,+Your+Internet+Service++Will+Expire+after+$EXPIRY+days++++zaibisp"

echo "$NOW ! Expiry Notification have been sent to $username, on cell number 0$mobile"
echo "$NOW ! Expiry Notification have been sent to $username, on cell number 0$mobile" >> /var/log/sms.log

# Add action like send sms or email as per required or designed / zaib
# Sending sms via APITEL API SMS Gatewy / syed jahanzaib / aacable@hotmail.com

curl "http://api1.itelservices.net/send.php?transaction_id=$TID$username&user=$APIUSER&pass=$APIPASS?&number=%2B92$mobile&text=$BODY&from=$API" >> /tmp/smsapi.log
done

sed 's/\(Status\)/\n\1/g' /tmp/smsapi.log >> /var/log/smsapi.log
echo ======================================================
echo Result for SMSAPI , so that you can verify that how much sms are actually sent with the status codes
cat  /var/log/smsapi.log



 

CRON JOB TO RUN IT DAILY IN NIGHT

Now set cron job to run it daily in night

@daily /temp/sms.sh


 LOGS

you can view log files in following location
/var/log/sms.log

Sample:

Thu Jun 18 11:43:20 PKT 2015 ! Expiry Notification have been sent to USER1, on cell number 033333333333
Thu Jun 18 11:43:20 PKT 2015 ! Expiry Notification have been sent to USER2, on cell number 0333132121211

/var/log/smsapi.log

Results with status from api gateway services (Useful to track the messages are actually sent or having errors from provider like server down, credit finished etc etc)

Sample:

Status: 013, Id: -1434609800USER1, Number: +923452266605
Status: 013, Id: -1434609800USER2, Number: +923222656143


2- SMS for NEW Account Creation

1

mkdir /temp
touch /temp/sms-new-account.sh
chmod +x /temp/sms-new-account.sh
nano /temp/sms-new-account.sh

#!/bin/sh
# set -x
# BASH base SMS script for NEW ACCOUNTnotification for Freeradius/mysql users
# the simple logic can be applied for about any other task as well.
# I tried to make it as simple as it can be
# By Syed Jahanzaib
# CREATED on : 19th june, 2015
# This script was specially modified for APITEL http sms gateway services
# which requires unique transaction ID each time, so i used datetimesecond feature as jugaar
# made for KHI/PK

# MYSQL root id and password
SQLUSER="root"
SQLPASS="pass"
DB="radius-db"

# APITEL User Name & Password
APIUSER="APIUSER"
APIPASS="APIPASS"
API="SENDERID"

# Date functions to find current date, month year and Transaction id using seconds ; ) jugaar way ; )
NOW=$(date)
TID=$(date +"-%s")

# Check Account which are created before this number of MINUTES
CREATION=5

touch /tmp/sms-new-account.log
touch /tmp/sms-new-account-api.log
> /tmp/sms-new-account.log
> /tmp/sms-new-account-api.log

# Export usernames and mobile from the mysql table in a file,  which Expiry is after 3 days
USRVALID=`mysql -u$SQLUSER -p$SQLPASS -e "use $DB; select creationdate,login,package,expirydate,mobile from users WHERE creationdate >= NOW() - INTERVAL $CREATION MINUTE;"`
mysql -u$SQLUSER -p$SQLPASS -e "use $DB; select creationdate,login,package,expirydate,mobile from users WHERE creationdate >= NOW() - INTERVAL $CREATION MINUTE;" > /tmp/newact

# Check User Validation, if not found exit with error , else continue
echo
if [ "$USRVALID" == "" ]; then
echo -e "No new user created in last minutes, so nothign to do , zaib !"
else
echo -E "user Created found , proceeding..."

# Remove 0 if any in mobile number and export it to final list
cat /tmp/newact | awk '{gsub("^0","",$7); print $1,$2,$3,$4,$5,$6,$7}' > /tmp/newactfinal

# Add DATE to separate entries in sms-new-account.log
echo ================================ >> /var/log/sms-new-account.log
echo $NOW >> /var/log/sms-new-account.log
echo ================================ >> /var/log/sms-new-account.log

echo ================================ >> /var/log/sms-new-account-api.log
echo $NOW >> /var/log/sms-new-account-api.log
echo ================================ >> /var/log/sms-new-account-api.log

# Apply Count Loop Formula while deleting first line which have simple text, and also any line which dont have mobile number [in second column]
# 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/newactfinal |sed '1d' |awk 'NF > 6' | while read users
do
num=$[$num+1]
username=`echo $users |awk '{print $3}'`
mobile=`echo $users | awk '{print $7}'`
pkg=`echo $users | awk '{print $4}'`
exp=`echo $users | awk '{print $5}'`
#echo "Welcome to MYNET Broadband Services! Your account details are as follow...
#Username = $username
#Package = $pkg
#Expiry = $exp
#Cell No = $mobile"

# SMS Body
BODY="Welcome+to+MYISP+Services,+Your+account+details+are:++id=$username+/+Package=+$pkg+/+Expiry=+$exp+/+Cell=+0$mobile++++MYISP+BROADBAND"

echo "$NOW ! New Acount Creation Notification have been sent to $username, on cell number 0$mobile"
echo "$NOW ! New Acount Creation Notification have been sent to $username, on cell number 0$mobile" >> /var/log/sms-new-account.log

# Add action like send sms or email as per required or designed / zaib
# Sending sms via APITEL API SMS Gatewy / syed jahanzaib / aacable@hotmail.com

curl "http://api1.itelservices.net/send.php?transaction_id=$TID$username&user=$APIUSER&pass=$APIPASS?&number=%2B92$mobile&text=$BODY&from=$API" >> /tmp/sms-new-account-api.log
sed 's/\(Status\)/\n\1/g' /tmp/sms-new-account-api.log >> /var/log/sms-new-account-api.log
echo
echo Result for SMSAPI , so that you can verify that how much sms are actually sent with the status codes
#cat  /var/log/sms-new-account.log
done

fi

Cron it to run after every 5 minutes

*/5 * * * * /temp/sms-new-account.sh


 3- SMS for ALL users (I deployed it for Webmin usage)


#!/bin/bash
# set -x
# Script to send GENERAL SMS via WEBMIn
# Syed Jahanzaib
# aacable @ hotmail.com
# https://aacable.wordpress.com
# Created on 24th June, 2015

SQLUSER="root"
SQLPASS="mysqlpassword"
DB="radiusdb"

# APITEL User Name & Password
APIUSER="xxxx"
APIPASS="xxxxx"
API="xxxx"

######################
# ACCOUNT EXPIRY CHECK
######################

# Date functions to find current date, month year and Transaction id using seconds ; ) jugaar way ; )
NOW=$(date)
TID=$(date +"-%s")

# Adding files
touch /tmp/smspanel.log
touch /tmp/smapanel-api.log
> /tmp/smspanel.log
> /tmp/smapanel-api.log

mysql -uroot -pgatewayb3 -e "use mynet; SELECT login,mobile FROM users;"  > /tmp/smspanellist

# Remove 0 if any in mobile number and export it to final list
cat /tmp/smspanellist | awk '{gsub("^0","",$2); print $1,$2}' > /tmp/smspanellistfinal

# Add DATE TIME in /tmp/smspanel.log to separate date wise entries / zaib
echo ====================================================== >> /var/log/smspanel.log
echo $NOW >> /var/log/smspanel.log
echo ====================================================== >> /var/log/smspanel.log

# Add DATE TIME in /tmp/smspanel-api.log to separate date wise entries WITH API STATUS for cross verification / zaib
echo ====================================================== >> /var/log/smspanel-api.log
echo $NOW >> /var/log/smspanel-api.log
echo ====================================================== >> /var/log/smspanel-api.log

# 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
# remove first line which have simple text, then remove dash in second column which is mobile numbers
cat /tmp/smspanellistfinal |sed '1d' |awk 'NF > 1' | awk '{gsub("-","",$2)}1' | while read users
do
num=$[$num+1]
username=`echo $users |awk '{print $1}'`
mobile=`echo $users | awk '{print $2}'`

# SMS Body in local file and remove new lines and replace spaces with plus sign for api acceptance
BODY=`cat /tmp/smspanelmsg.txt  |tr '\r\n' ' ' | sed -e "s/\s\{1,\}/+/g"`

#echo "$NOW ! $BODY ---- MSG was sent to $username, on cell number 0$mobile"
echo "$NOW ! Your MSG was sent to $username, on cell number 0$mobile" >> /var/log/smspanel.log

# Sending sms via APITEL API SMS Gatewy / syed jahanzaib / aacable@hotmail.com

curl "http://api1.itelservices.net/send.php?transaction_id=$TID$username&user=$APIUSER&pass=$APIPASS?&number=%2B92$mobile&from=$API&text=$BODY" >> /tmp/smspanel-api.log
sed 's/\(Status\)/\n\1/g' /tmp/smspanel-api.log >> /var/log/smspanel-api.log
done

 

ITELSERVICES.NET related information

Sample of URL to send SMS

http://api1.itelservices.net/send.php?transaction_id=message1&user=bilal&pass=bilal2015?&number=%2B923333021909&text=hello&from=MyNet

Please note that the transaction id must be unique for each sms, example message1, message2 and so on any word is acceptable, i used date time as transaction id, you may use your own.

 

INFORMATION AND ERROR CODES related to API

For the information/error codes

 

1

 

2

 

3


 

Regard’s
Syed Jahanzaib

June 15, 2015

Sending Email on Router Reboot with Logs and LASTSEEN time !

Filed under: Mikrotik Related — Syed Jahanzaib / Pinochio~:) @ 2:05 PM

email


 

Task:

To send email when the router reboots because of any power or other issue, the email should include the time of router failure and its restore time in the email subject, [for quick viewing] . Also it should include last few logs entries in the email body for diagnostic purposes.

Disclaimer: Main script was taken from Mikrotik WIKI
http://wiki.mikrotik.com/wiki/Send_email_about_reboot , and later I modified it to meet local requirement. Also I created second script which keep updates of time in a file to track the time when router got lost,

Following are two scripts which does the following

Script#1 – Update-last-seen-time-on-each-minute
This script will update date+time in a local file called lastseen.txt so that even if a router reboot, the time preserves in this file to print the lastseen time in email or where ever required.

Script#2 – email-on-reboot
This script will run after router reboot/restarts. It will first disable the script-1 schedule to prevent overwritten of lastseen time in the file lastseen.txt, then it will fetch the data from this file and will also get current time and send email with last 60 Seconds logs in the email body.

Script#2 is taken from WIKI and modified it as per my requirement. (to display Last seen time as well in the email so that I don’t have to go through whole logs in search of —–)

Exported Version of both scripts. for copy/pasting, donot go blindly, read them before deploying


####################################
#1st Script: update-last-seen-time-on-each-minute
####################################

/system script
add name=email-on-reboot policy=ftp,reboot,read,write,policy,test,winbox,password,sniff,sensitive,api source="# Disable LASTSEEN script schduler to prevent overwritten of time\"\r\
\n:log error \"Disabling Lastseen Update Time Scheudle to preserve Last REBOOT time\"\r\
\n/sys scheduler disable update-last-seen-time-on-each-minute\r\
\n\r\
\n:delay 30s\r\
\n:global gmailsmtp\r\
\n:set gmailsmtp [:resolve \"smtp.gmail.com\"];\r\
\n\r\
\n# Set your GMAIL Account Password\r\
\n:local gmailpass\r\
\n:set gmailpass GMASILPASS\r\
\n\r\
\n# Set your email where you want to receieve the alert \r\
\n:local mailsendto\r\
\n:set mailsendto aacable@hotmail.com\r\
\n\r\
\n# Wait for NTP Time Synchronization to pritn current timestamp in mail subject, NTP is required in RB's \r\
\n:while ( [/system ntp client get status]!=\"synchronized\" ) do={ :delay 1s }\r\
\n\r\
\n# Set Email Subject\r\
\n:local es \"\$[/system identity get name] got LOST at \$[/file get lastseen.txt contents] & back online at \$[/system clock get date] \$[/system clock get time]\"\r\
\n\r\
\n# Load Last LOGS Entries of 60 seconds\r\
\n:delay 60s\r\
\n:local eb \"Mikrotik Log contents (with 60 seconds delay):\\r\\n\"\r\
\n:foreach le in=[/log print as-value] do={\r\
\n  :set eb (\$eb.[:tostr [(\$le->\"time\")]].\" \".[:tostr [(\$le->\"topics\")]].\": \".[:tostr [(\$le->\"message\")]].\"\\r\\n\")\r\
\n}\r\
\n\r\
\n# Finally send email \r\
\n/tool e-mail send to=\$mailsendto subject=\$es body=\$eb server=\$gmailsmtp tls=yes password=\$gmailpass\r\
\ndelay 5s\r\
\n\r\
\n# Enable back the update time schdule for lastseen value \r\
\n:log warning \"Email Done! Enabling Lastseen Update Time Scheudle ...\"\r\
\n/sys scheduler enable update-last-seen-time-on-each-minute\r\
\n\r\
\n# Syed Jahanzaib\r\
\n# aacable [at] hotmail.com"

####################################
#2nd Script: update-last-seen-time-on-each-minute
####################################

add name=update-last-seen-time-on-each-minute policy=ftp,reboot,read,write,policy,test,winbox,password,sniff,sensitive,api source=":local date;\r\
\n:local time;\r\
\n:set date [/system clock get date];\r\
\n:set time [/system clock get time];\r\
\n/file print file=lastseen.txt where name=lastseen.txt;\r\
\n/file set lastseen.txt contents=\"\$date \$time\"\r\
\n#:local lastdt ([/file get lastseen.txt contents])\r\
\n#:log warning \"Updaintg Last Seen > \$lastdt\"\r\
\n}"

Print View of Both Scripts


/sys scr pr

0   name="email-on-reboot" owner="admin" policy=ftp,reboot,read,write,policy,test,winbox,password,sniff,sensitive,api last-started=jun/15/2015 13:17:47 run-count=39
source=
# Disable LASTSEEN script schduler to prevent overwritten of time"
:log error "Disabling Lastseen Update Time Scheudle to preserve Last REBOOT time"
/sys scheduler disable update-last-seen-time-on-each-minute

:delay 30s
:global gmailsmtp
:set gmailsmtp [:resolve "smtp.gmail.com"];

# Set your GMAIL Account Password
:local gmailpass
:set gmailpass GMAILPASS

# Set your email where you want to receive the alert
:local mailsendto
:set mailsendto aacable@hotmail.com

# Wait for NTP Time Synchronization to pritn current timestamp in mail subject, NTP is required in RB's
:while ( [/system ntp client get status]!="synchronized" ) do={ :delay 1s }

# Set Email Subject
:local es "$[/system identity get name] got LOST at $[/file get lastseen.txt contents] & back online at $[/system clock get date] $[/system clock get time]"

# Load Last LOGS Entries of 60 seconds
:delay 60s
:local eb "Mikrotik Log contents (with 60 seconds delay):\r\n"
:foreach le in=[/log print as-value] do={
:set eb ($eb.[:tostr [($le->"time")]]." ".[:tostr [($le->"topics")]].": ".[:tostr [($le->"message")]]."\r\n")
}

# Finally send email
/tool e-mail send to=$mailsendto subject=$es body=$eb server=$gmailsmtp tls=yes password=$gmailpass
delay 5s

# Enable back the update time schdule for lastseen value
:log warning "Email Done! Enabling Lastseen Update Time Scheudle ..."
/sys scheduler enable update-last-seen-time-on-each-minute

# Syed Jahanzaib
# aacable [at] hotmail.com

#########################
##### Second Script View
########################
1   name="update-last-seen-time-on-each-minute" owner="admin" policy=ftp,reboot,read,write,policy,test,winbox,password,sniff,sensitive,api last-started=jun/15/2015 13:49:18 run-count=307
source=
:local date;
:local time;
:set date [/system clock get date];
:set time [/system clock get time];
/file print file=lastseen.txt where name=lastseen.txt;
/file set lastseen.txt contents="$date $time"
#:local lastdt ([/file get lastseen.txt contents])
#:log warning "Updaintg Last Seen > $lastdt"
}

 

Schedule both scripts

/system scheduler
# First script which will run once only upon system restart
add disabled=no interval=0s name=email-on-reboot on-event=email-on-reboot policy=ftp,reboot,read,write,policy,test,winbox,password,sniff,sensitive,api start-time=startup

# Second script which will run after each minute to update the date/time in local file
add disabled=no interval=1m name=update-last-seen-time-on-each-minute on-event=update-last-seen-time-on-each-minute policy=ftp,reboot,read,write,policy,test,winbox,password,sniff,sensitive,api \
start-date=jun/15/2015 start-time=05:40:08

Configure Email Example

Also don’t forget to set email tools. like

/tool e-mail
set address=173.194.69.109 from=YOURGMAILID@gmail.com password=YOURGMAILPASS port=587 starttls=no user=YOURGMAILID

 


 

SCRIPTS iN ACTiON

Now power off the router , and after few minutes power it on.

After a while, you should see the scripts in action.
As showed in the image below …

email-mikrotik-reboot-emial

 

email

.

Regard’s
Syed Jahanzaib

Older Posts »

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 2,873 other followers