mysql与nagios的结合使用
一、 对mysql建库建表，并测试数据基本信息：
库名：nh_nagios
表名：nagios_alerts
# mysql -u root -p
Enter password:123456
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cactidb |
| mysql |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> create database nh_nagios;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cactidb |
| mysql |
| nh_nagios |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use nh_nagios
Database changed
mysql> create table nagios_alerts
-> (
->nagios_id int not null auto_increment,
->nagios_name char(50) not null,
->nagios_number int not null,
->primary key(nagios_id)
-> )engine=innod default charset=utf8 auto_increment=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> show tables;
+---------------------+
| Tables_in_nh_nagios |
+---------------------+
| nagios_alerts |
+---------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO nagios_alerts (nagios_name,nagios_number)VALUES("serviceTotalsPROBLEMS",2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from nagios_alerts;
+-----------+-----------------------+---------------+
| nagios_id | nagios_name | nagios_number |
+-----------+-----------------------+---------------+
| 1 | serviceTotalsPROBLEMS | 2 |
+-----------+-----------------------+---------------+
1 row in set (0.00 sec)
mysql> INSERT INTO nagios_alerts (nagios_name,nagios_number)VALUES("serviceTotalsPROBLEMS",10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from nagios_alerts;
+-----------+-----------------------+---------------+
| nagios_id | nagios_name | nagios_number |
+-----------+-----------------------+---------------+
| 1 | serviceTotalsPROBLEMS | 2 |
| 2 | serviceTotalsPROBLEMS | 10 |
+-----------+-----------------------+---------------+
2 rows in set (0.00 sec)
mysql> exit
二、在shell中执行插入数据进mysql的测试
# vim show_nh_nagios_alerts.sh
#!/bin/bash
mysql -uroot -hlocalhost -p123456 <<EOF
use nh_nagios;
#select * from nagios_alerts;
INSERT INTO nagios_alerts (nagios_name,nagios_number)VALUES("serviceTotalsPROBLEMS",30);
EOF
# chmod 700 show_nh_nagios_alerts.sh
# ./show_nh_nagios_alerts.sh
nagios_id nagios_name nagios_number
1 serviceTotalsPROBLEMS 2
2 serviceTotalsPROBLEMS 10
# mysql -u root -p
Enter password:123456
mysql> use nh_nagios
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from nagios_alerts;
+-----------+-----------------------+---------------+
| nagios_id | nagios_name | nagios_number |
+-----------+-----------------------+---------------+
| 1 | serviceTotalsPROBLEMS | 2 |
| 2 | serviceTotalsPROBLEMS | 10 |
| 3 | serviceTotalsPROBLEMS | 30 |
+-----------+-----------------------+---------------+
3 rows in set (0.00 sec)
mysql> exit
使用语句自动取nagios_number的数据，即nagios内serviceTotalsPROBLEMS的值
# vim 1.sh
#! /bin/sh
alerts=`curl -u nagiosadmin:password -s http://192.168.5.10/nagios/cgi-bin/status.cgi?host=all | \
grep serviceTotalsPROBLEMS |\
grep -o '[[:digit:]]'`
if [ "$alerts" = "" ]
then
echo "alerts is empty"
else
echo $alerts
fi
# ./1.sh
2
测试成功
# vim show_nh_nagios_alerts.sh
#!/bin/bash
test_get_nagios_service_alerts=`curl -u nagiosadmin:password -s http://192.168.5.10/nagios/cgi-bin/status.cgi?host=all | \
grep serviceTotalsPROBLEMS |\
grep -o '[[:digit:]]'`
#此处加入判断当无报警时，设get_nagios_service_alerts=0
if [ "$test_get_nagios_service_alerts" = "" ];
then
get_nagios_service_alerts=0
else
get_nagios_service_alerts=$test_get_nagios_service_alerts
fi
mysql -uroot -hlocalhost -p123456 <<EOF
use nh_nagios;
#select * from nagios_alerts;
INSERT INTO nagios_alerts (nagios_name,nagios_number)VALUES("serviceTotalsPROBLEMS",$get_nagios_service_alerts);
EOF
写入3条数据进mysql中
# ./show_nh_nagios_alerts.sh
# ./show_nh_nagios_alerts.sh
# ./show_nh_nagios_alerts.sh
# mysql -u root -p
Enter password:123456
mysql> use nh_nagios
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from nagios_alerts;
+-----------+-----------------------+---------------+
| nagios_id | nagios_name | nagios_number |
+-----------+-----------------------+---------------+
| 1 | serviceTotalsPROBLEMS | 2 |
| 2 | serviceTotalsPROBLEMS | 10 |
| 3 | serviceTotalsPROBLEMS | 30 |
| 4 | serviceTotalsPROBLEMS | 2 |
| 5 | serviceTotalsPROBLEMS | 2 |
| 6 | serviceTotalsPROBLEMS | 2 |
| 7 | serviceTotalsPROBLEMS | 2 |
| 8 | serviceTotalsPROBLEMS | 2 |
| 9 | serviceTotalsPROBLEMS | 1 |
| 10 | serviceTotalsPROBLEMS | 1 |
| 11 | serviceTotalsPROBLEMS | 1 |
| 12 | serviceTotalsPROBLEMS | 1 |
| 13 | serviceTotalsPROBLEMS | 0 |
| 14 | serviceTotalsPROBLEMS | 0 |
| 15 | serviceTotalsPROBLEMS | 0 |
| 16 | serviceTotalsPROBLEMS | 0 |
| 17 | serviceTotalsPROBLEMS | 0 |
| 18 | serviceTotalsPROBLEMS | 0 |
| 19 | serviceTotalsPROBLEMS | 0 |
| 20 | serviceTotalsPROBLEMS | 0 |
| 21 | serviceTotalsPROBLEMS | 2 |
| 22 | serviceTotalsPROBLEMS | 2 |
| 23 | serviceTotalsPROBLEMS | 0 |
| 24 | serviceTotalsPROBLEMS | 0 |
| 25 | serviceTotalsPROBLEMS | 0 |
| 26 | serviceTotalsPROBLEMS | 2 |
| 27 | serviceTotalsPROBLEMS | 2 |
| 28 | serviceTotalsPROBLEMS | 2 |
| 29 | serviceTotalsPROBLEMS | 2 |
| 30 | serviceTotalsPROBLEMS | 2 |
+-----------+-----------------------+---------------+
30 rows in set (0.00 sec)
mysql> exit
到此，通过shell写入nagios的数据进mysql成功。后继可考虑将shell写入cron，让它每隔一分钟就写入数据进mysql中。
三、取服务状态为CRITICAL的IP地址与服务名
# curl -u nagiosadmin:password -s http://192.168.5.10/nagios/cgi-bin/status.cgi?host=all | grep CRITICAL | \
grep -E '{1,3}\.{1,3}\.{1,3}\.{1,3}' > nagios.txt
# cat nagios.txt
<TD ALIGN=LEFT valign=center CLASS='statusBGCRITICAL'><A HREF='extinfo.cgi?type=2&host=192.168.5.110&service=SSH'>SSH</A></TD></TR>
<TD ALIGN=LEFT valign=center CLASS='statusBGCRITICAL'><A HREF='extinfo.cgi?type=2&host=192.168.5.110&service=check-host-alive'>check-host-alive</A></TD></TR>
# awk -F'&' '{print $2,$3}' nagios.txt
host=192.168.5.110 service=SSH'>SSH</A></TD></TR>
host=192.168.5.110 service=check-host-alive'>check-host-alive</A></TD></TR>
# awk -F'&' '{print $2,$3}' nagios.txt | sed 's#</A></TD></TR>##g'
host=192.168.5.110 service=SSH'>SSH
host=192.168.5.110 service=check-host-alive'>check-host-alive
# awk -F'&' '{print $2,$3}' nagios.txt | sed 's#</A></TD></TR>##g' | sed "s#\'>.*##g"
host=192.168.5.110 service=SSH'>SSH
host=192.168.5.110 service=check-host-alive'>check-host-alive
# awk -F'&' '{print $2,$3}' nagios.txt | sed 's#</A></TD></TR>##g' | sed "s#\'>.*##g" > nagios2.txt
# cat nagios2.txt
host=192.168.5.110 service=SSH'>SSH
host=192.168.5.110 service=check-host-alive'>check-host-alive
# sed "s/'>.*$//g" nagios2.txt
host=192.168.5.110 service=SSH
host=192.168.5.110 service=check-host-alive
四、对nagios_alerts表加入一记录生成时间的字段，并成功记录数据
#新增一生成时间字段
mysql> alter table nagios_alerts add COLUMN createtime datetime NOT NULL;
Query OK, 0 rows affected (0.06 sec)
Records: 0Duplicates: 0Warnings: 0
#查看表结构
mysql> show columns from nagios_alerts;
+---------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+----------------+
| nagios_id | int(11)| NO | PRI | NULL | auto_increment |
| nagios_name | char(50) | NO | | NULL | |
| nagios_number | int(11)| NO | | NULL | |
| createtime | datetime | NO | | NULL | |
+---------------+----------+------+-----+---------+----------------+
4 rows in set (0.04 sec)
========================================
#生成时间的shell，但在mysql中，可以使用NOW()函数来做
# var=`date "+%Y-%m-%d %H:%M:%-S"`
# echo $var
2014-03-30 21:36:53
========================================
# vim show_nh_nagios_alerts.sh
#!/bin/bash
test_get_nagios_service_alerts=`curl -u nagiosadmin:skylink -s http://192.168.5.10/nagios/cgi-bin/status.cgi?host=all | \
grep serviceTotalsPROBLEMS |\
grep -o '[[:digit:]]'`
create_time=`date "+%Y-%m-%d%H:%M:%-S"`
if [ "$test_get_nagios_service_alerts" = "" ];
then
get_nagios_service_alerts=0
else
get_nagios_service_alerts=$test_get_nagios_service_alerts
fi
mysql -uroot -hlocalhost -p123456 <<EOF
use nh_nagios;
#select * from nagios_alerts;
INSERT INTO nagios_alerts (nagios_name,nagios_number,createtime)VALUES("serviceTotalsPROBLEMS",$get_nagios_service_alerts,NOW()); #加入生成时间
EOF
====================================
#显示表内容
# vim show_nh_nagios_alerts_mysql.sh
#!/bin/bash
mysql -uroot -hlocalhost -p123456 <<EOF
use nh_nagios;
select * from nagios_alerts;
EOF
页:
[1]