Zabbix 创建月度统计报表脚本(学习笔记十六)-阿里云开发者社区

开发者社区> 开发与运维> 正文

Zabbix 创建月度统计报表脚本(学习笔记十六)

简介: 一、脚本: #!/bin/bash ############################## # @version 0.1 # @date 2017-12-31 ############################## USER1="zabbi...

一、脚本:

#!/bin/bash

##############################

# @version 0.1

# @date 2017-12-31

##############################

USER1="zabbix

PASSWORD1="123456"

HOST1="localhost"

DATABASE1="zabbix"

######################

rm -rf /tmp/zabbix_statics.csv

mysql -h${HOST1} -u${USER1} -p${PASSWORD1} -D${DATABASE1}

DROP TABLE if exists tmp_itemhost;

DROP TABLE if exists  tmp_trigger;

DROP TABLE if exists  tmp_triggeritem;

CREATE TABLE tmp_itemhost (

   hostid int(20),

   host   varchar(1280),

   hostname varchar(1280),

   itemid  int(20),

   itemname varchar(1280),

   itemkey varchar(1280),

   itemport varchar(128) null,

   itemstate int(20)

) ;

INSERT INTO tmp_itemhost(hostid,host,hostname,itemid,itemname,itemkey,itemport,itemstate)

(

select hosts.hostid,hosts.host,hosts.name,items.itemid,items.name,items.key_,items.port,items.state

from items left join hosts

on items.hostid=hosts.hostid

);

CREATE TABLE tmp_trigger(

triggerid int(20),

itemid int(20),

description varchar(1280),

priority int(20)

);

INSERT INTO tmp_trigger(triggerid,itemid,description,priority)

select triggers.triggerid,functions.itemid,triggers.description,triggers.priority

from triggers left join functions

on triggers.triggerid=functions.triggerid;

CREATE TABLE tmp_triggeritem(

   triggerid int(20),

   triggerdescription varchar(1280),

   triggerpriority int(20),

   hostid int(20),

   host   varchar(1280),

   hostname varchar(1280),

   itemid  int(20),

   itemname varchar(1280),

   itemkey varchar(1280),

   itemport varchar(128) null,

   itemstate int(20)

) ;

INSERT INTO tmp_triggeritem(triggerid,triggerdescription,triggerpriority,hostid,host,hostname,itemid,itemname,itemkey,itemport,itemstate)

(

select tmp_trigger.triggerid,tmp_trigger.description,tmp_trigger.priority,hostid,host,hostname,tmp_itemhost.itemid,itemname,itemkey,itemport,itemstate

from tmp_trigger left join tmp_itemhost

on tmp_trigger.itemid=tmp_itemhost.itemid

);

EOF

mysql -hIP -uroot -p密码 -Dzabbix -e "select count(*),host,hostname,triggerdescription

into outfile '/tmp/zabbix_statics.csv' fields terminated by ',' optionally enclosed by ' ' lines terminated by '\r\n' from events left join tmp_triggeritem

on events.objectid=tmp_triggeritem.triggerid where events.source=0 and events.object=0 and events.value=1

and date_format(FROM_UNIXTIME(clock),'%Y-%m')=date_format(now(),'%Y-%m') group by host,hostname,triggerdescription order by count(*) desc;"

sed -i "1i告警次数,主机名,主机IP,告警消息" /tmp/zabbix_statics.csv

sed -i "s/{HOST.NAME}//g" /tmp/zabbix_statics.csv

sed -i "s#\\\# #g" /tmp/zabbix_statics.csv

yy=$(date +%Y)

mm=$(date +%m)

aa=`cat /tmp/zabbix_statics.csv`

mv /tmp/zabbix_statics.csv /tmp/zabbix_${yy}${mm}_statics.csv


二、报表格式如下:


img_506548de0a19e80fb0c20e8862722241.png

三、zabbix表参考:

https://www.cnblogs.com/learningJAVA/p/7886164.html

https://blog.csdn.net/hy_timer/article/details/72082004

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
开发与运维
使用钉钉扫一扫加入圈子
+ 订阅

集结各类场景实战经验,助你开发运维畅行无忧

其他文章