ICMP Monitor With MySQL - Linux

Hello everyone, here is some code intended to help you monitor any node of your network using ICMP (ping), but, with a some help, you could load the hosts to test from a MySQL database and register the state of it on the same database.

First Create a database and a table with some fields like an ID, a Description for the node (Just to have a human identification), the HostName or IP Address, and the State (This could have an UP or DOWN state)

CREATE DATABASE MysqlDatabase;
use MysqlDatabase;
 CREATE TABLE `MysqlTable` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Description` VARCHAR( 60 ) NOT NULL ,
`MysqlHostColumn` VARCHAR( 80 ) NOT NULL ,
`MysqlStateColumn` VARCHAR( 30 ) NOT NULL
);

And insert some IP addresses or hostnames with some description for them:

 INSERT INTO `MysqlTable` (`Description`, `MysqlHostColumn`, `MysqlStateColumn`) VALUES ('Gateway', '192.168.1.1', 'DOWN');
INSERT INTO `MysqlTable` (`Description`, `MysqlHostColumn`, `MysqlStateColumn`) VALUES ('Google', 'www.google.com', 'DOWN');
INSERT INTO `MysqlTable` (`Description`, `MysqlHostColumn`, `MysqlStateColumn`) VALUES ('Google DNS', '8.8.8.8', 'DOWN');

Let's see the content that we have just loaded with:

select * from MysqlTable;

That is fair enough, now just create a bash script.

touch mysql_icmp_monitor.sh
vi mysql_icmp_monitor.sh

And insert the following content in it.

#!/bin/bash
#Develped by Bryan P. Saldivar E. bsaldivar.emc2@gmail.com
#Define Working Folder
TMP="$(pwd)/TMP"
#Define Cache File
TFILE="${TMP}/MYSQL.tmp"
#Define Cache IPs File
TFILE_IP="${TMP}/MYSQL_IP.tmp"
#Define MySQL
db_user=MysqlUser
db_pass=MysqlPassword
db_db=MysqlDatabase
db_table=MysqlTable
db_HostColumn=MysqlHostColumn
db_StateColumn=MysqlStateColumn
#Ping Var
PingCount=4
#Quality Range LRT (Loss Rate Threshold) Under this value the node is considered UP
LRT=50
State="DOWN"
#Validate existence of Folder
ExistFolFil() 
{
Folder=${1}
File=${2}

if [ -d ${Folder} ] 
then
if [ ! -f ${File} ] 
then
echo "Creating file ${File}"
touch ${File}
fi
else
echo "Creating Folder ${Folder} "
mkdir ${Folder}
if [ ! -f ${File} ] 
then
echo "Creating file ${File}"
touch ${File}
fi
fi
}

ExistFolFil ${TMP} ${TFILE_IP}

  #Select hosts from database
  mysql -u ${db_user} -p${db_pass} -e "USE "${db_db}"; SELECT "${db_HostColumn}" FROM "${db_table}";" > ${TFILE_IP}

for Host in $(cat ${TFILE_IP} | grep -v ${db_HostColumn})
do
echo "Host encontrado : ${Host}"
if [[ ! -z ${Host} ]]
then
LossRateTmp=$(ping ${Host} -c ${PingCount}  )
LossRate=$(echo ${LossRateTmp} | cut -d "," -f3 | cut -d "%" -f1 )
State="DOWN"
if [[ ! -z ${LossRate} ]]
then
if (( ${LossRate} < ${LRT} ))
then
State="UP"
fi
fi
echo "Host: ${Host} | Loss Rate: ${LossRate} | State: ${State}"
mysql -u ${db_user} -p${db_pass} -e "USE "${db_db}"; UPDATE "${db_table}" SET "${db_StateColumn}"='"${State}"' WHERE "${db_HostColumn}"='"${Host}"' ;"
fi
done

And finally provide the execution permissions to the file and run the script:

chmod +x mysql_icmp_monitor.sh
sh mysql_icmp_monitor.sh

If you want to monitor constantly (Every minute) add the route to the file in the contrab file. If the file is in the directory /root/, add the line (* *     * * *   root sh /root/mysql_icmp_monitor.sh) in the crontab file (/etc/crontab ) 

vi /etc/crontab
* *     * * *   root sh /root/mysql_icmp_monitor.sh

That is all, thank you.


ICMP-MySQL Monitor by Bryan Percy Saldivar Espinoza (bsaldivar.emc2@gmail.com) is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

¿Desea donar?/Do you wish to give some money to the author?

Give money to the author/writer

1 USD $1.00 USD 5 USD $5.00 USD 10 USD $10.00 USD 20 USD $20.00 USD 50 USD $50.00 USD 100 USD $100.00 USD 1000 USD $1,000.00 USD