Syed Jahanzaib Personal Blog to Share Knowledge !

November 29, 2011

Howto Save Mikrotik/Cisco Logs to Remote SYSLOG Server


log-title


Part # 1 – Howto Save Mikrotik/Cisco Logs to Remote SYSLOG Server << You are here

Part # 2 – Centralized Syslog-ng logging to MySql DB


First Published Date: Nov 29, 2011 @ 11:58
Last Modified : Nov 9, 2017


In some situations, you may want to save your mikrotik router or CISCO switches logs (or web proxy logs) for record / tracking purpose regarding Mikrotik activity. In most countries it is required by the law as well to keep record of users public IP assignment, like when you will apply for LICENSE, it is required to have such record at your disposal. Its much better from management point of view to intercept mikrotik info using external Linux base logs server.

This post demonstrate how to send Mikrotik logs to remote Ubuntu/Linux base syslog server. We will use SYSLOG-NG package in this example.

SYSLOG Server  =  192.168.100.1   [OS > Ubuntu 12.4 32 bit] 
Mikrotik Server = 192.168.100.2

First We will configure Mikrotik section


# MIKROTIK CONFIGURATION

In Mikrotik, Open Terminal & paste the following.

Currently we are storing this type of information only from the mikrotik to syslog

  • pppoe info like login/logout/peer not responding/
  • winbox login
  • sysinfo , like rules disabled/enabled

 

/system logging action
set 3 remote=192.168.100.1
add name=ZLogServer remote=192.168.100.1 target=remote
/system logging
add action=ZLogServer topics=pppoe,ppp,info
add action=ZLogServer topics=system,info

[Note:  192.168.100.1 is Linux syslog server ip, Change this ip to match your remote syslog server ip. You can modify the topics as per your requirement, just an example below]

log settings for mikrotik
log2

That’s it for Mikrotik 🙂 Now moving to Linux Section, in this example I used Ubuntu 12.4 You can use any other flavor of your choice.


# UBUNTU 12.4 CONFIGURATION

First we have to install the syslog server. In this example we are using syslog-ng log server.

Install various packages including syslog-ng server, phpmyadmin, mysql server to store logs in DB[if required], supporting libraries etc.

apt-get -y install apache2 mc wget make gcc mysql-server mysql-client curl phpmyadmin libdbd-pgsql aptitude libboost-system-dev libboost-thread-dev libboost-regex-dev syslog-ng  libmongo-client0 libesmtp6 syslog-ng-mod-sql libdbd-mysql

During installation of above packages it may ask you to enter mysql/phpmyadmin password, do so as required. Once all the packages are installed, edit the syslog-ng file by

After installation, edit its configuration file available in /etc/syslog-ng.conf

Use the following command to edit config file.

nano /etc/syslog-ng/syslog-ng.conf

Now paste following lines before SOURCES section.

# Accept connection on UDP
source s_net { udp (); };

# MIKROTIK ###########
# Add Filter to add our mikrotik
filter f_mikrotik { host( "192.168.100.22" ); };
log { source ( s_net ); filter( f_mikrotik ); destination ( df_mikrotik ); };
destination df_mikrotik {
file("/var/log/zlogs/${HOST}.${YEAR}.${MONTH}.${DAY}.log"
template-escape(no));
};

As shown below …

log server.PNG

Now Save & Exit.


IMPORTANT:

  • Create ‘zlogs‘ folder in /var/log and file also, so that mikrotik logs will be saved in separate file.
mkdir /var/log/zlogs

Restart the syslog-ng service to apply changes

service syslog-ng restart

Monitoring the LOGS

Now check the file name in /var/log/mikrotik and monitor it by tail command

tail -f /var/log/zlogs/#HOST.YYMMDD.log

At mikrotik , perform any action, for example open ‘New Terminal‘ OR try to add any new rule, you will see its logs in the tail output.

For example.


log

DONE !


LOG ROTATE !

As we have successfully managed to add the new log file to the system, it is crucial that we must configure log rotation to move / delete older logs otherwise it may fill the disk quickly if its heavily used production system.

To add log rotation edit the syslog-ng configuration file.

nano /etc/logrotate.d/syslog-ng

and add following in the start or before end . . . .

[This will rotate log files on daily basis, it will compress the last day log file. useful if you have receive some heavy logs from the devices.

Note: change folder names as required

/var/log/mikrotik/*.log {
 daily
 rotate 90
 missingok
 compress
 notifempty
 missingok
 sharedscripts
 /etc/init.d/syslog-ng restart
 endscript
# invoke-rc.d syslog-ng reload > /dev/null
}

Save & Exit. and reload the syslog-ng service

service syslog-ng restart

Explanation of above code.

  • daily the logrotation for mikrotik log in /var/log/mikrotik/mikrotik.log file will be don eon daily basis. this value describes the interval of rotation
  • rotate 90 means syslog will keep 90 log file. [number of files]
  • compress log file will be compressed using the gzip format
  • missingok avoids halting on any error
  • notifempty will not rotate log file if its empty

size‘ parameter is  also very important setting if you want to control the sizing of the logs for heavy production server.

A configuration setting of around 50 MB would look like:

size 50M

Note that If both size and rotation interval are set, then size will override rotation parameter



log


Change SYSLOG Log Rotation Time

By default log.rotate starts at 6:47am in the morning. To change it to run in midnight, edit file

/etc/crontab

and change the cron.daily line to following

0 0     * * *   root    test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.daily )

This will run it in mid night. 🙂


DELETE LOG FILES with ZERO SIZE

You may need this ; )

find /var/log/mikrotik/ -name 'mikrotik*' -size 0 -print0 | xargs -0 rm

DELETE LOG FILES OLDER THEN 90 DAYS

This one too ; )

find /var/log/mikrotik/* -daystart -mtime +90-type f -exec rm {} \;

Or better to use complete script as defined here.


Centralized log server to store multiple devices logs

Updated: 9-NOV-2017

In a situation where you want to have centralized log server to log multiple devices logs with separate files, you can use following..

# MIKROTIK ###########
# Accept connection on UDP
source s_net { udp (); };
# Add Filter to add our mikroti
filter f_mikrotik { host( "101.11.11.1" ); };
filter f_mikrotik2 { host( "101.11.11.2" ); };
filter f_ciscoswnoc { host( "101.11.11.3" ); };
filter f_ciscosw2 { host( "101.11.11.4" ); };
# Add destination file where logs will be stored, for each host
destination d_mikrotik { file("/var/log/mikrotik/$HOST.mikrotik.${YEAR}.${MONTH}.${DAY}.log"); };
destination d_mikrotik2 { file("/var/log/mikrotik/$HOST.mikrotik.${YEAR}.${MONTH}.${DAY}.log"); };
destination d_ciscoswnoc { file("/var/log/mikrotik/$HOST.ciscosw.${YEAR}.${MONTH}.${DAY}.log"); };
destination d_ciscosw2 { file("/var/log/mikrotik/$HOST.ciscosw.${YEAR}.${MONTH}.${DAY}.log"); };
log { source(s_net); filter(f_mikrotik); destination(d_mikrotik); };
log { source(s_net); filter(f_mikrotik2); destination(d_mikrotik2); };
log { source(s_net); filter(f_ciscoswnoc); destination(d_ciscoswnoc); };
log { source(s_net); filter(f_ciscosw2); destination(d_ciscosw2); };

Make sure to restart syslog-ng server

service syslog-ng restart

 


Check SYSLOG-NG error

/usr/sbin/syslog-ng -F -p /var/run/syslogd.pid

Salam Alykum

Regard’s
Syed Jahanzaib

November 28, 2011

November 25, 2011

Password less Login to Remote Mikrotik & Linux

Filed under: Linux Related, Mikrotik Related — Tags: , , , , — Syed Jahanzaib / Pinochio~:) @ 12:46 PM

~ Article by Syed Jahanzaib ~

ssh.png

By Following this guide , You will be able to Execute Scripts on Mikrotik Router from a Remote Linux machine without requiring password.


SCENARIO# 1

Login From Linux to Mikrotik to execute commands via ssh without Password !!!

[STEP # 1]

First you need to generate public dsa key on your linux bx {which you will upload to mikrotik in later stage}.
At your Linux box, issue the following command.

ssh-keygen -t dsa

This will create a DSA key pair that is compatible with Mikrotik/Linux

 ssh-keygen -t dsa

It will ask you few questions, just press enter , as showed below…

root@zaib-desktop:~# ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/root/.ssh/id_dsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_dsa.
Your public key has been saved in /root/.ssh/id_dsa.pub.
The key fingerprint is:
ed:da:88:da:d1:b1:f0:b5:f2:39:04:85:9d:d0:19:f1 root@zaib-desktop
The key's randomart image is:
+--[ DSA 1024]----+
|        .=o=     |
|        . *.     |
|         .  E    |
|        ..       |
|      . S.o      |
|       + =..     |
|      . =.o      |
|     . o *..     |
|    ..o o +.     |
+-----------------+
root@zaib-desktop:~#

Note: Make sure to leave the passphrase blank if you are going to be using this key in automated scripts. You do not want to be prompted for a password. zaib

 

[STEP # 2]
Now the key have been generated, It’s time to upload it to MIKROTIK using FTP. Make sure FTP service is enabled on mikrotik. Upload this id_dsa.pub key via ftp using below commands.

192.168.2.2 is Mikrotik IP

cd /root/.ssh/
ftp 192.168.2.2
#Connected to 192.168.2.2.
#220 MikroTik FTP server (MikroTik 3.3) ready
Name (192.168.2.2:root): admin
#331 Password required for admin
Password:
#230 User admin logged in
#Remote system type is UNIX.

ftp> put id_dsa.pub
local: id_dsa.pub remote: id_dsa.pub
200 PORT command successful
150 Opening ASCII mode data connection for '/id_dsa.pub'
226 ASCII transfer complete
608 bytes sent in 0.00 secs (2207.2 kB/s)

ftp> exit
221 Closing
root@zaib-desktop:~/.ssh#

OR

[STEP # 3] – MIKROTIK SECTION
Now login to Mikrotik via Winbox, and open Terminal , you need to import the key. to import key, use the below command.

user ssh-keys import file=id_dsa.pub
user: admin

The user field above determines which user account will be logged in when you pass the key, In this example , I am using default admin id.

All Done. You’ve created a key pair and imported the public key into Mikrotik ,

Now you can start running commands from your remote machine without using the password.

Some examples are below, from your Linux box, try the following . . .

(For the first time Login, It will ask you “Are you sure you want to continue connecting (yes/no)?” Type yes to continue)

ssh admin@192.168.2.2  /system resource print
The authenticity of host '192.168.2.2 (192.168.2.2)' can't be established.
DSA key fingerprint is 5f:d5:ee:51:8b:1c:c3:df:4d:3c:29:d8:af:48:35:a5.
Are you sure you want to continue connecting (yes/no)? yes

Again try to execute command and this time it will execute smoothly without asking any thing.

root@zaib-desktop:~# ssh admin@192.168.2.2  /system resource print
uptime: 40m37s
version: "3.3"
free-memory: 40512kB
total-memory: 62276kB
cpu: "Intel(R)"
cpu-count: 1
cpu-frequency: 3200MHz
cpu-load: 1
free-hdd-space: 956832kB
total-hdd-space: 1021408kB
write-sect-since-reboot: 2373
write-sect-total: 2373

OR

You can do so many interesting things using this method, you can link scripts with php or webmin and control your mikrotik / linux box with webmin as Frontend.


SCENARIO # 2

Login From Ubuntu 12.x to Ubuntu 12.x to execute commands via ssh without Password !!!

Assumption:

[LINUX]  ADMIN PC IP  = 192.168.2.1
[LINUX]  REMOTE SERVER IP = 192.168.2.9

Suppose, We want to login from ADMIN PC to REMOTE SERVER without password , or we want to execute command from ADMIN PC to REMOTE SERVER.

[STEP # 1]

You have to first generate DSA public key on ADMIN PC.
You can create it by following [STEP # 1]  in Scenario # 1 of this post.

example:

ssh-keygen -t dsa

If you have already generated it, then skip this Step#1

[STEP # 2]

From Admin PC , issue the following command to upload id_dsa.pub to Remote Server.

scp id_dsa.pub root@192.168.2.9:.ssh/authorized_keys

[It will ask Remote Server Password, type password and hit enter.

If you receive error like “scp: .ssh/authorized_keys: No such file or directory” then run the ssh-keygen -t dsa command on remote server first, then re-run step-2

Now try to Login to REMOTE SERVER using following command

ssh 192.168.2.9

root@zaib-desktop:~/.ssh# ssh 192.168.2.9
Linux test2-proxy 2.6.31-14-generic #48-Ubuntu SMP Fri Oct 16 14:04:26 UTC 2009 i686

To access official Ubuntu documentation, please visit:
http://help.ubuntu.com/

353 packages can be updated.
202 updates are security updates.

Last login: Fri Nov 25 03:01:45 2011 from 192.168.2.1
root@test2-proxy:~#

SUCCESS ! You are now able to Login to remote server without password.

You can Execute any command on remote server from admin pc, For example, you can shutdown / restart or whatever you like . . .

root@zaib-desktop:~/.ssh# ssh 192.168.2.9 df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             3.8G  2.1G  1.5G  59%
/ udev                186M  224K  186M   1%
/dev none             186M  164K  186M   1%
/dev/shm none         186M   88K  186M   1%
/var/run none         186M     0  186M   0%
/var/lock none        186M     0  186M   0%
/lib/init/rw root@zaib-desktop:~/.ssh#

Another easy method to copy file. [Added 29-NOV-2017]

From your Admin PC , issue this command to copy the file to remote Linux server we want to access (without pass)

ssh-copy-id -P 55511 root@192.168.9.2

-P is used if you have SSH listening on different port.


for UBUNTU 16.X

ssh-keygen -t rsa

and on mikrotik, use

/user ssh-keys import user=admin public-key-file=id_rsa.pub

Regard’s
Syed Jahanzaib

November 16, 2011

MySQL DB & HTML files Backup Script


backup using bashs cript

LAST UPDATED:  10-MARCH-2019

Following is a customized backup script [Tested with Ubuntu 12/16] to create backup of your mysql DB and HTML files in local storage & on cloud using DROPBOX (make sure you have dropbox running),

This script will EXCLUDE data in radacct and radacct_archive tables. It will only export table structure without data. to reduce database size, you can modify this function.

NOTE: Using S.S.D disk (or raid) are highly recommended as there are less chances of  media failure, and above all read/write rates is incredibly fast. Plus If you have configured RAID , then it will provide redundancy as well.

Modify the script as per your requirements …

  • I am using KANNEL as sms gateway & sendEmail app to send email via GMAIL.
  • It will add some logs in /var/log/syslog
  • It will make a copy of files in /root/Dropbox folder
  • It will take backup of RADIUS Database excluding radacct , only radacct structure will be exported (to save disk space by lower size of backup)
  • It will also check mysql Service & DB status, if it found inaccessible, it will send email
  • It will also delete files older than 20 days from /backup & root/Dropbox folder
  • It will check File size, if Less than 1 byte it will alert, modify it as required
  • all backups will be saved in /backup & /root/Dropbox folder [dropbox if exists]
  • In the end, It will send email and SMS with details

As usually, like all other scripts of mine it contains lots of junk. Please be-aware that I sometimes modify this file as per network requirements, so you may see revisions in this file. This is just an sample dummy, you can add remove any function in this script as per your requirements 🙂 ~ zaib

Let’s Start …


First create temp folder and create script in it.

mkdir /temp
cd /temp
touch fullbackup.sh
chmod +x fullbackup.sh

Now open the file

nano /temp/fullbackup.sh

and paste the following code.

#!/bin/sh
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/etc
#set -x
# Version 1.1 / 10th January, 2014
# Last Modified / 10-MARCH-2019
# Syed Jahanzaib / Web: https://aacable.wordpress.com / Email: aacable@hotmail.com
# This script creates FULL Backup of MySQL DB (Radius) and MYSQL DBR related DATA files.
# We can adjust it to do incremental basis backup too, but I based on my personnel experiences, I prefer to have FULL backup instead of incremental,
# Because you never know what you will going to need in case of disaster recovery
# Adjust below DATA fields accordingly. remove / add desired folders.
# Settings various VARIABLES for the script
clear
# Colors Config ... [[ JZ ... ]]
ESC_SEQ="\x1b["
COL_RESET=$ESC_SEQ"39;49;00m"
COL_RED=$ESC_SEQ"31;01m"
COL_GREEN=$ESC_SEQ"32;01m"
# IF YOU HAVE FEDORA or CENTOS or new verison of UBUNTU, Change the /var/www to /var/www/html/
# Following is only RADIUS HTML FILES, mysqldb is separate from this, will come later in the script with /usr/bin/mysqldump
TARGET="/var/www /sql_backup /etc/freeradius /etc/mysql"
#TARGET="/tmp"
SAVEDIR_FULL="/backup"
DROPBOX="/root/Dropbox"
HOSTNAME=`hostname`
#MYSQL DETAILS
SRV="mysql"
SQLUSER="root"
SQLPASS="SQLPASS"
export MYSQL_PWD=$SQLPASS
DB="radius"
SQL_ACCOUNTING_TABLE="radacct"
SQL_DIR="sql_backup"
CMD="mysql -u$SQLUSER --skip-column-names -s -e"
#SET DATE TIME
set $(date)
time=`date |awk '{print $4}'`
DT=`date +%d.%b.%Y_time_%H.%M.%S`
DATE=$(date +%Y-%m-%d)
DT_HMS=$(date +'%H:%M:%S')
FULL_DATE=`date`
TODAY=$(date +"%Y-%m-%d")
TODAYYMD=`date +"%d-%b-%Y"`
#Get ip which have default route
IPADD=`ip route get 1 | awk '{print $NF;exit}'`
logger Backup of mysqldb / html SCRTIP has been started @ $DATE / $DT_HMS
# Adding OS level Details in email message
# disk we want to monitor, make sure to change this
DISK=`df -h | sed -n 2p | awk '{print $1}'`
#DISK="/dev/mapper/125--209--89--92--vg-root"
# Get DB size in MB
MYSQLDBSIZE=`mysql -u$SQLUSER --skip-column-names -e "SELECT table_schema "$DB", sum(data_length + index_length)/1024/1024 FROM information_schema.TABLES WHERE table_schema='radius' GROUP BY table_schema;" | cut -f1 -d"." | sed 's/[^0-9]*//g'`
SESSIONS=`mysql -u$SQLUSER --skip-column-names -e "use radius; SELECT username FROM $SQL_ACCOUNTING_TABLE WHERE acctstoptime IS NULL;" |wc -l`
DISKTOT=`df -h $DISK |awk '{print $2}'| sed -n 2p`
DISKUSED=`df -h $DISK |awk '{print $3}'| sed -n 2p`
DISKAVA=`df -h $DISK |awk '{print $4}'| sed -n 2p`
DISKUSEPER=`df -h $DISK |awk '{print $5}'| sed -n 2p`
MEMTOT=`free -m |awk '{print $2}'| sed -n 2p`
MEMUSED=`free -m |awk '{print $3}'| sed -n 2p`
MEMAVA=`free -m |awk '{print $4}'| sed -n 2p`
MEMUSEDPER=`free -m | grep Mem | awk '{print $3/$2 * 100.0}'`
MEMAVAPER=`free -m | grep Mem | awk '{print $4/$2 * 100.0}'`
#Update script execution Time Stamp / specific to PAKRAD billing system made by jz
#$CMD "use $DB; insert into scripts_exec (script_name, lastupdate) VALUES(' fullbackup.sh','$TODAY $DT_HMS') on duplicate key update lastupdate='$TODAY $DT_HMS';"
#GMAIL Details
GMAILID="X@gmail.com"
GMAILPASS="X"
ADMINMAIL1="X@hotmail.com"
ADMINMAIL2="X@gmail.com"
COMPANY="ZAIB.PK"
SMTP="64.233.184.108:587"
# KANNEL SMS Gateway - Details if you want to send SMS
KHOST="kannel.url:13013"
KID="kannel"
KPASS="kpass"
CELL1="03333021909"
EMAILMSG="/tmp/mysql.backup.result.email.txt"
SMSMSG="/tmp/mysql.backup.result.sms.txt"
SRVDOWNEMAIL="/tmp/srvdownemail.txt"
DBDOWNEMAIL="/tmp/dbdownemail.txt"
SQL_FILE="$HOSTNAME.$DB.mysql.db.$DT.sql"
FINAL_TAR_FILE="$HOSTNAME.$DB.final.file.$DT.tgz"
#LOG FILE, IF REQUIRED
SYSLOG="/var/log/syslog"

> $EMAILMSG
> $SMSMSG
> $SRVDOWNEMAIL
> $DBDOWNEMAIL
> $SQL_FILE
> $FINAL_TAR_FILE

# START the BACKUP PROCESS ... #######
# Start counting start time
start_time=`date +%s`
echo "
Welcome ! This is mysql and html Backup Script,it wil backup following folders ... > $TARGET
It will EXCLUDE radacct & radacct_archive folder from the backup to ensure file size of Backup folder is lower
Powered by JZ
"
# Checking if $SAVEDIR_FULL folder is previously present or not ...
{
if [ ! -d "$SAVEDIR_FULL" ]; then
echo "- $SAVEDIR_FULL folder not found, Creating it so all backup's should be placed there ... "
mkdir $SAVEDIR_FULL
else
echo "- $SAVEDIR_FULL folder is already present , so no need to create it, Proceeding further ... "
fi
}

# Checking if $SQL_DIR folder is previously present or not ...
{
if [ ! -d "/$SQL_DIR" ]; then
echo "- /$SQL_DIR folder not found, Creating it so MSQL EXPORT/DUMP backup should be placed there ... "
mkdir /$SQL_DIR
else
echo
echo "- /$SQL_DIR folder is already present , so no need to create it, Proceeding further ..."
fi
}

# Check if $SRV (in this case mysql) is running or not, if NOT, then exit the script
SRVSTATUS=`service $SRV status |grep running |wc -l`
if [ "$SRVSTATUS" -ne 1 ];
#if [ -z "$SRVSTATUS" ];
then
echo "- ALERT: $HOSTNAME - $IPADD - $SRV NOT RESPONDING CHECK - $DATE $DT .Exiting ..."
echo "- ALERT: $HOSTNAME - $IPADD - $SRV NOT RESPONDING CHECK - $DATE $DT .Exiting ..." >> $SYSLOG

# SRV down EMail BODY
echo "- ALERT:

- $HOSTNAME
- $IPADD
- $SRV not responding ***
- $DATE $DT

Exiting ..." > $SRVDOWNEMAIL
sendemail -t $email -u "ALERT: $HOSTNAME - $IPADD - $SRV NOT RESPONDING CHECK - $DATE $DT" -o tls=yes -s $SMTP -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$SRVDOWNEMAIL -o message-content-type=text
sendemail -t $email -u "ALERT: $HOSTNAME - $IPADD - $SRV NOT RESPONDING CHECK - $DATE $DT" -o tls=yes -s $SMTP -t $ADMINMAIL2 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$SRVDOWNEMAIL -o message-content-type=text
exit 1
else
echo "- $SRV service is accessible OK. Proceeding further ..."
fi

# Check if $DB (in this case radius )is accessible or not, if NOT, then exit the script
RESULT=`$CMD "SHOW DATABASES LIKE '$DB'"`
if [ "$RESULT" != "$DB" ]; then
echo "- ALERT: $HOSTNAME - $IPADD - DB $DB not accessible/exists - $DATE $DT"
echo "- ALERT: $HOSTNAME - $IPADD - DB $DB not accessible/exists - $DATE $DT" >>$SYSLOG
echo "- ALERT: $HOSTNAME - $IPADD - DB $DB not accessible/exists - $DATE $DT" >>$DBDOWNEMAIL

# DB down EMail BODY
echo "- ALERT:

- $HOSTNAME
- $IPADD
- Service $SRV is OK BUT
- Database $DB not accessible/exists ***
- $DATE $DT

Exiting ..." > $DBDOWNEMAIL
sendemail -t $email -u "ALERT: $HOSTNAME - $IPADD - DB $DB not accessible/exists - $DATE $DT" -o tls=yes -s $SMTP -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$DBDOWNEMAIL -o message-content-type=text
sendemail -t $email -u "ALERT: $HOSTNAME - $IPADD - DB $DB not accessible/exists - $DATE $DT" -o tls=yes -s $SMTP -t $ADMINMAIL2 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$DBDOWNEMAIL -o message-content-type=text
exit 1
else
echo "- $DB - database exist OK. Proceeding further ..."
fi

echo "
sleeping 10 seconds so that you can stop if you want ...
also it will perform radacct trimming as well
Powered by Syed.Jahanzaib
"
#sleep 5

# CREATE RADACCT.ARCHIVE TABLE
$CMD "use $DB; create table if not exists radacct_archive LIKE radacct;"
# --- Copy data from CURRENT radacct table to new radacct_archive table (for archive purposes)
$CMD "use $DB; INSERT INTO radacct_archive SELECT * FROM radacct WHERE acctstoptime is not null;"
# --- Now Delete data from CURRENT RADACCT table so that it should remain fit and smart ins size
$CMD "use $DB; DELETE FROM radacct WHERE acctstoptime is not null;"
# --- Now Delete data from RADACCT_ARCHIVE table so that it should not grow either more then we required i.e 1 Year - one year archived data is enough IMO
$CMD "use $DB; DELETE FROM radacct_archive WHERE date(acctstarttime)  (CURDATE() - INTERVAL 1 DAY);"

# Creating MYSQL dump of databases & ignore DATA inside some tables
/usr/bin/mysqldump -u$SQLUSER --ignore-table={$DB.radacct,$DB.radacct_archive} $DB > /$SQL_DIR/$SQL_FILE
/usr/bin/mysqldump -u$SQLUSER --no-data $DB radacct >> /$SQL_DIR/$SQL_FILE
/usr/bin/mysqldump -u$SQLUSER --no-data $DB radacct_archive >> /$SQL_DIR/$SQL_FILE

# Check mysql File Size, if its less than 1 byte, consider it invalid
SIZE=`ls -lh /$SQL_DIR/$SQL_FILE | awk '{print $5}'`
SIZEB=`ls -l /$SQL_DIR/$SQL_FILE | awk '{print $5}'`
{
if [ $SIZEB -lt 1 ]
then
echo "SQL file size is invalid"
echo "SQL file size is invalid" >> $SYSLOG
SQL_RESULT="INVALID"
else
echo "SQL file size is OK"
echo "SQL file size is OK" >> $SYSLOG
SQL_RESULT="OK"
fi
}

# GZIP MYSQL D.B & all other files like html etc
echo " - TAR Compressing all Backup Folders to $SAVDIR_FULL ... "
tar -zcf $SAVEDIR_FULL/$FINAL_TAR_FILE $TARGET

# Delete duplicate backup files .sql and .tgz from current folder to avoid file duplication
if [ -f $SQL_FILE ]; then
echo "- Deleting $SQL_FILE to avoid duplication"
rm $SQL_FILE
fi
if [ -f $FINAL_TAR_FILE ]; then
echo "- Deleting $FINAL_TAR_FILE to avoid duplication"
rm $FINAL_TAR_FILE
fi

# Deleting sql db from $SQL_DIR because its zipped with the above command already and now all data available in single file : )
{
if [ -d "/$SQL_DIR" ]; then
rm -fr /$SQL_DIR
fi
}
# Print END time
echo "
- Backup ended at $6-$2-$3 Time $time .
"
echo "- Backup completed to $SAVEDIR_FULL ...
"
echo "- Backup ended at $6-$2-$3 Time $time ...
"
end_time=`date +%s`

# Delete files older then 20 days
echo "- Deleting Older files than 20 Days from $SAVEDIR_FULL and $DROPBOX (if any) to save disk space ..."
{
if [ -d "$SAVEDIR_FULL" ]; then
find $SAVEDIR_FULL/* -mtime +20 -exec rm {} \;
fi
}

# Delete files older then 20 days
{
if [ -d "$DROPBOX" ]; then
echo "- DROPBOX $DROPBOX folder found, making backup file copy in $DROPBOX too ... "
cp $SAVEDIR_FULL/$FINAL_TAR_FILE $DROPBOX
echo "- Deleting Older files then 20 days from $DROPBOX, to save disk space ..."
find $DROPBOX -mtime +20 -exec rm {} \;
fi
}
# Print Complete Timings
echo "- Backup script completion Time was `expr $end_time - $start_time` s. "

# CHECK FILE SIZE AND COMPARE, IF ITS LESS , THEN ALERT
SIZE=`ls -lh $SAVEDIR_FULL/$FINAL_TAR_FILE | awk '{print $5}'`
SIZEB=`ls -l $SAVEDIR_FULL/$FINAL_TAR_FILE | awk '{print $5}'`
{
if [ $SIZEB -lt 1 ]
then
echo "- Final tgz Backup file size is invalid"
echo "- Final tgz Backup file size is invalid" >> $SYSLOG
TGZ_RESULT="INVALID"
else
echo "- Final tgz Backup file size is OK"
echo "- Final tgz Backup file size is OK" >> $SYSLOG
TGZ_RESULT="OK"
fi
}

# List files created Today & TRIM its reulst
LIST=`ls -lh $SAVEDIR_FULL --time-style=+"%d-%b-%Y" |grep $TODAYYMD | awk '{print $5,$6,$7}' | column -t`

#Email File with all details
echo "Mysql/HTML Backup Info for

===============
NETWORK DETAILS:
===============
HOST: $HOSTNAME
IP: $IPADD

==========================
RADIUS SERVER MYSQL REPORT:
==========================
MYSQL 'RADIUS' DB SIZE = $MYSQLDBSIZE MB
RADIUS Online Users = $SESSIONS Users

====================
DIS / STORAGE REPORT:
====================
Total Disk Space = $DISKTOT
Total Disk Space Used = $DISKUSED
Total Disk Space Available = $DISKAVA
Total Disk Space = $DISKUSEPER

==============
MEMORY REPORT:
==============
Total RAM = $MEMTOT MB
Total RAM Used = $MEMUSED MB
Total RAM Available = $MEMAVA MB
Total RAM Used Percent = $MEMUSEDPER %
Total RAM Available Percent = $MEMAVAPER %

- List of files created in Today's Date [$TODAY]

$LIST

- SQL_RESULT = $SQL_RESULT
- TGZ_REULST = $TGZ_RESULT
- Target Folders = $TARGET
- FileName = $SAVEDIR_FULL/$FINAL_TAR_FILE
- Size = $SIZE
- Backup Time = `expr $end_time - $start_time` s.

$COMPANY
Powered by JZ" > $EMAILMSG

#SMS file with lesser details
echo "$HOSTNAME - Backup Info
SQL_RESULT = $SQL_RESULT
TGZ_RESULT = $TGZ_RESULT
FileName = $FINAL_TAR_FILE
Size = $SIZE
Bkp Time = `expr $end_time - $start_time` s.
$COMPANY
Powered by JZ" > $SMSMSG

# PRINT INFO SECTION #########
echo "- Job Log for:
"
# Print Fetched Information on Screen , for info to see
cat $EMAILMSG

# EMAIL SECTION ##############
# Make sure you install sendEMAIL tool and test it properly before using email section.
#SEND EMAIL Alert As well using sendEMAIL tool using GMAIL ADDRESS.
# If you want to send email , use below ...
echo " - Sending SMS/EMAIL ALERT ..."
#curl "http://$KHOST/cgi-bin/sendsms?username=$KID&password=$KPASS&to=$CELL1" -G --data-urlencode text@$SMSMSG
sendemail -t $email -u "$HOSTNAME - $IPADD - mySQL Backup - $DT / Size=$SIZE" -o tls=yes -s $SMTP -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$EMAILMSG -o message-content-type=text
sendemail -t $email -u "$HOSTNAME - $IPADD - mySQL Backup - $DT / Size=$SIZE" -o tls=yes -s $SMTP -t $ADMINMAIL2 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$EMAILMSG -o message-content-type=text
# Print Credits : )
echo "

- Syed Jahanzaib / aacable@hotmail.com / https://aacable.wordpress.com "
DATE=$(date +%Y-%m-%d)
DT_HMS=$(date +'%H:%M:%S')
# log entyr in /var/log/syslog
logger Backup of mysqldb / html have been end @ $DATE / $DT_HMS


Now execute this script  & see if any error occurs

/temp/fullbackup.sh

 


HOWTO SCHEDULE THE SCRIPT TO RUN ON DAILY BASIS

To run this file on daily basis (at 00:00 hours),
Open terminal, (make sure you are logged in with the root user.
type

crontab -e

(if it asks for text editor, select nano ,)

Now paste following code …

@daily  /temp/fullbackup.sh  >/dev/null 2>&1  # Run Daily in night at 00:00 hours

or if you want to run it every 6th hours (and on 15th minute) use following

# Run backup at 4:30 am in morning
#30 00 * * * /temp/fullbackup.sh >/dev/null 2>&1

# Run backup at 15th minute every 6th hours
15 */6 * * * /temp/fullbackup.sh >/dev/null 2>&1

Save & Exit:

Now, based on above selection, cron job will run this command at selected scheduled timings and clear any memory cache


TIP: Remove files older than xx days

When you will implement this script, it will backup every day, and continue to do so, and its a good idea to remove backup files older then 1 month, to save disk space otherwise one day it will chew whole disk space 😀
You can add following in the same backup script at end, so that whenever it will execute backup script, it will delete older files too

echo Deleting Older files then 30 days, to save disk space
# echo Deleting Older files then 30 days, to save disk space  >> /var/log/fullbackup.log
find /backup/* -mtime +30 -exec rm {} \;

 > SCRIPT   EXECUTION   RESULT … [old sample result]

 

b2

> EMAIL   ALERT   EXECUTION … [13-dec-2017 live sample]

 

mail backup sample

> SMS   ALERT   EXECUTION … [old sample]

 

2016-05-29 05.32.34


Regard’s
Syed Jahanzaib

November 12, 2011

Smokeping : Best tool to plot ping / latency graphs


Recently I was troubleshooting a network where concerned Admin complained that they frequently lost connectivity with multiple local server’s and also sometimes with the Internet. Sometimes pings replies works okay but latency gets high or timeout / breaks occurs. So I decided to setup mrtg base ping graph to monitor ping latency. The custom made mrtg ping probe worked fine and can provide an overview on target ping / rtt and Downtime in a nice manner, its sample can be viewed at http://www.billing.onmypc.net/mrtg/ . . .



BUT . . . . . . . . . . . . . . . . . . .

I was thinking far ahead , I was thinking for much more advanced latency and quality graphs which can show ping latency / rtt / loss in much more detailed way. I recalled my memory from old days when I used to monitor my old network with variety of tools and scripts and suddenly a name popped in my mind ” SMOKEPING ” , yes this was the tool I was looking for.

SmokePing generates graphs that can reveal the quality (packet loss and latency variability) & reachability of your IP address from several distributed locations. SmokePing is a network latency monitor. It measures network latency to a configurable set of destinations on the network, and displays its findings in easy-to-read Web pages. It uses RRDtool as its logging and graphing back-end, making the system very efficient. The presentation of the data on the Web is done through a CGI with some AJAX capabilities for interactive graph exploration.

In this article I will show you howto install smokeping on FEDORA 10.

HOWTO INSTALL SMOKEPING on FEDORA 10

Installing Smokeping is relatively simple. Just issue this command as root.

yum -y install smokeping

and It will install smokeping along with all other tools and dependencies it requires. It may download for about 18-20 Mb of data from internet, It may take few minutes depending on your internet connection and system speed.

NOTE:  selinux is the Problem. By default its enabled in Fedora installation. Disable it by editing

nano /etc/selinux/config

and change the enable to disable as following.

SELINUX=disable

(Restart your nix box.)

After installation completes, You can access it via

http://yourip/cgi-bin/sm.cgi

You will see something similar as shown in the following screenshot.


NOTE: If you get “Access Denied: You don’t have permission to access this Folder” error, edit the following line.

nano /etc/httpd/conf.d/smokeping.conf

and remove all lines in this file , and paste the following.

<Directory "/usr/share/smokeping">
order deny,allow
allow from 127.0.0.1
allow from all
</Directory>

<Directory "/var/lib/smokeping">
order deny,allow
allow from 127.0.0.1
allow from all
</Directory>

ScriptAlias /smokeping/sm.cgi  /usr/share/smokeping/cgi/smokeping.cgi
ScriptAlias /smokeping/tr.cgi  /usr/share/smokeping/cgi/tr.cgi
Alias       /smokeping/images  /var/lib/smokeping/images
Alias       /smokeping         /usr/share/smokeping/htdocs

Save & exit. Now restart apache web server by

service httpd restart

Now try to access the smokeping cgi, it will open properly.

http://yourip/cgi-bin/sm.cgi

Now It’s time to add your target in Smokeping for monitoring. The default location for the Smokeping config file is /etc/smokeping/config, Now just for example, we will edit this file.

 nano /etc/smokeping/config

Remove all the lines in it and paste the following lines

*** General ***
owner    = Syed Jahanzaib
contact  = aacable@hotmail.com
mailhost = smtp.ptcl.com
sendmail = /usr/sbin/sendmail
imgcache = /var/lib/smokeping/images
imgurl   = /smokeping/images
datadir  = /var/lib/smokeping/rrd
piddir   = /var/run/smokeping
cgiurl   = http://localhost/smokeping/smokeping.cgi
smokemail = /etc/smokeping/smokemail
tmail     = /etc/smokeping/tmail
syslogfacility = local0
*** Alerts ***
to = root@localhost
from = root@localhost
+someloss
type = loss
pattern = >0%,*12*,>0%,*12*,>0%
comment = loss 3 times  in a row
*** Database ***
step     = 300
pings    = 20
# consfn mrhb steps total
AVERAGE  0.5   1  1008
AVERAGE  0.5  12  4320
MIN  0.5  12  4320
MAX  0.5  12  4320
AVERAGE  0.5 144   720
MAX  0.5 144   720
MIN  0.5 144   720
*** Presentation ***

template = /etc/smokeping/basepage.html

+ charts

menu = Charts
title = The most interesting destinations

++ stddev
sorter = StdDev(entries=>4)
title = Top Standard Deviation
menu = Std Deviation
format = Standard Deviation %f

++ max
sorter = Max(entries=>5)
title = Top Max Roundtrip Time
menu = by Max
format = Max Roundtrip Time %f seconds

++ loss
sorter = Loss(entries=>5)
title = Top Packet Loss
menu = Loss
format = Packets Lost %f

++ median
sorter = Median(entries=>5)
title = Top Median Roundtrip Time
menu = by Median
format = Median RTT %f seconds

+ overview

width = 600
height = 50
range = 10h

+ detail

width = 600
height = 200
unison_tolerance = 2

"Last 3 Hours"    3h
"Last 30 Hours"   30h
"Last 10 Days"    10d
"Last 400 Days"   400d

*** Probes ***
+ FPing

binary = /usr/sbin/fping

*** Slaves ***
secrets=/etc/smokeping/smokeping_secrets
+boomer
display_name=boomer
color=0000ff

+slave2
display_name=another
color=00ff00

*** Targets ***
probe = FPing

menu = Top
title = Network Latency Grapher
remark = Welcome to the SmokePing website of <b>GLASSLINE (Pvt) Ltd.</b> <br> Here you will learn all about the latency of our network.<br><br><br><br><br> This page is maintained by Glassline. (Pvt) ltd . <br><br>Support Email: aacable@hotmail.com<br>Web: https://aacable.wordpress.com

### YOU CAN CHANGE THE FOLLOWING ACCORDING TO YOUR NETWORK ###

+ Ping

menu = WAN Connectivity
title = WAS Side Network

++ yahoo

menu = yahoo
title = yahoo ping report
host = yahoo.com

++ google

menu = google
title = Google ping report
host = google.com

### YOU CAN CHANGE FOLLOWING ACCORDING TO YOUR NETWORK ###
+ Ping2

menu = LAN Connectivity
title = LAN Side Network

++ Mikrotik

menu = Mikrotik
title = Mikrotik PPP ping report
host = 10.10.0.1

++ Proxy

menu = Proxy
title = Proxy Server ping report
host = 10.10.0.2

++ Billing

menu = Billing
title = Radius billing Server ping report
host = 10.10.0.2

Now restart smokeping process by

/etc/init.d/smokeping restart

Default update time for all ping probes are 5 Minutes. Try accessing the main smokeping page after 5-10 minutes so it can create necessary RRD for targets and update its graphs.

http://yourip/cgi-bin/sm.cgi

Now click on LAN connectivity to open graphs. You will see something similar as shown in the following screenshot.


Now, Click on WAN Connectivity

Now click on  the Yahoo Ping Graph showing on Right to get more detailed graph report.

To get more detailed view, click on Last 3 hours graphs, It will open this graph in NAVIGATOR mode. Now here you can select any specific area to zoom to troubleshoot at exact which time the latency problems occurs. For example in the following screenshot you can see the blue dots which shows that ping timeout occurs at specific time.

PULL DATA FROM SPECIFIC TIME

A client complained that the internet worked very slow in the morning at 10:30am. So you can pull data from smokeping graph to validate the complain or to troubleshoot what exactly happened at 10:30am 🙂

As you can see the blue dots showed that the ping breaks occured in the specific timings.

There are so many interesting things you can do with SMOKE-PING.

For some more examples , please visit the author web site @

http://oss.oetiker.ch/smokeping/index.en.html

Take Care, n ALLAH HAFIZ

Regard’s

Syed Jahanzaib
aacable@hotmail.com

November 5, 2011

Howto Protect a web folder on APACHE with Password

Filed under: Linux Related — Tags: , , , , , — Syed Jahanzaib / Pinochio~:) @ 12:19 PM

Protecting your private web folders is important to keep your file privates from unauthorized users. There are many ways you can password protect directories under Apache web server.

In order to create apache password protected directories you need to set follwing:

  • Password file (must be placed in a folder which must not be accessible via oute rworld.
  • And Directory name which you would like to password protect (/var/www/html/mrtg) (Example)

One of the simplest is to add the following to your Apache config file:

<Directory "/var/www/html/mrtg">
AuthType Basic
AuthName "My Personnel Folder - Not for General Public"
AuthUserFile "/var/www/htpasswd"
Require valid-user
</Directory>

Then create the htpasswdfile, and add at least one user to it (as root):

htpasswd -c /var/www/htpasswd username
It will ask for password, just add your desired one. This file should be outside of the directories available from the web.
More info can be found at http://www.cyberciti.biz/faq/howto-setup-apache-password-protect-directory-with-htaccess-file/

November 3, 2011

Server’s Monitoring Status Page a.k.a “SERVER STATUS”


I was looking for a web base server status tool to monitor my connectivity with the ISP and local servers , I did this in the past using simple Javascript which shows ON/OFF status for specific pc’s, But it was not fancy, I was looking for more simple n visually appealing tool/page, After some googling , I came across with “Server Status”  / http://rushland.net/projects.htm

Its a very good tool made in PHP/HTML along with MYSQL support. I used it to monitor WAN connectivity / Local Servers Live Status. It can be use to monitor services status of local or remote servers too. I modified it according to my requirements and the final result is as follows 🙂

%d bloggers like this: