mysql与nagios的结合使用
一、 对mysql建库建表,并测试数据
基本信息:
库名:nh_nagios
表名:nagios_alerts
[root@nhserver2 ~]# 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的测试
[root@nhserver2 ~]# 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
[root@nhserver2 ~]# chmod 700 show_nh_nagios_alerts.sh
[root@nhserver2 ~]# ./show_nh_nagios_alerts.sh
nagios_id nagios_name nagios_number
1 serviceTotalsPROBLEMS 2
2 serviceTotalsPROBLEMS 10
[root@nhserver2 ~]# 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的值
[root@nhserver2 ~]# 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
[root@nhserver2 ~]# ./1.sh
2
测试成功
[root@nhserver2 ~]# 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中
[root@nhserver2 ~]# ./show_nh_nagios_alerts.sh
[root@nhserver2 ~]# ./show_nh_nagios_alerts.sh
[root@nhserver2 ~]# ./show_nh_nagios_alerts.sh
[root@nhserver2 ~]# 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地址与服务名
[root@nhserver2 ~]# curl -u nagiosadmin:password -s http://192.168.5.10/nagios/cgi-bin/status.cgi?host=all | grep CRITICAL | \
grep -E '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}' > nagios.txt
[root@nhserver2 ~]# 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>
[root@nhserver2 ~]# 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>
[root@nhserver2 ~]# 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
[root@nhserver2 ~]# 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
[root@nhserver2 ~]# awk -F'&' '{print $2,$3}' nagios.txt | sed 's#</A></TD></TR>##g' | sed "s#\'>.*##g" > nagios2.txt
[root@nhserver2 ~]# cat nagios2.txt
host=192.168.5.110 service=SSH'>SSH
host=192.168.5.110 service=check-host-alive'>check-host-alive
[root@nhserver2 ~]# 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: 0 Duplicates: 0 Warnings: 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()函数来做
[root@nhserver2 ~]# var=`date "+%Y-%m-%d %H:%M:%-S"`
[root@nhserver2 ~]# echo $var
2014-03-30 21:36:53
========================================
[root@nhserver2 ~]# 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
====================================
#显示表内容
[root@nhserver2 ~]# vim show_nh_nagios_alerts_mysql.sh
#!/bin/bash
mysql -uroot -hlocalhost -p123456 <<EOF
use nh_nagios;
select * from nagios_alerts;
EOF
最新文章
- Linux shell运算符
- JQuery-UI Dialog下使用服务器端按钮失效
- 欧拉工程第56题:Powerful digit sum
- 【转】java中静态代码块的用法 static用法详解
- 大型分布式C++框架《三:序列化与反序列化》
- tomcat配置不用访问工程名
- 如何使用Vue
- 逆向与Bof基础
- 关于SELECT LAST_INSERT_ID()的使用规则
- RocketMQ环境搭建
- 星文快投v2全新升级
- android studio常用控件
- 在linux下安装wordpress
- 在服务器上运行db:seed数据填充时,出错的问题解决
- vmware player 在windows下nat模式中的端口映射
- ThinkPHP5.0框架事务处理操作简单示例
- JAVA实现多线程的四种方式
- AGC006
- jQuery多库共存处理$.noConflict()
- Win10《芒果TV》更新v3.8.0初夏版:全新视觉体验,即刻分享视频
热门文章
- Java与算法之(3) - 斐波那契数列
- CCNA笔记(1)
- AtCoder Grand Contest 019
- LibreOJ β Round #2 E. 数论只会 GCD
- POJ1635:Subway tree systems
- hihoCoder #1094 : Lost in the City(枚举,微软苏州校招笔试 12月27日 )
- hihoCoder #1015 : KMP算法【KMP裸题,板子】
- 二分查找c++简单模板
- DFS+打表
- HDU--2018