zabbix生成月度统计报表

Z时代
2024-01-10
分类：综合

database

zabbix生成月度统计报表

SkTj

2018.10.20 09:59:14

字数 369

阅读 2,575

!/bin/bash

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

@version 0.1

@date 2017-12-31

##############################
USER1="zabbix327"
PASSWORD1="mm
HOST1="ip"
DATABASE1="zabbix"
TABLE="students"
######################
rm -rf /tmp/zabbix_statics.csv
mysql -h{USER1} -p{DATABASE1}<<EOF #2>/dev/null
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 -h172.16.50.21 -uroot -pXmrbi3967968@ -Dzabbix -e "select count(),host,hostname,triggerdescription
into outfile "/tmp/zabbix_statics.csv" fields terminated by "," optionally enclosed by " " lines terminated by "

" 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(date +"%Y%m%d" -d "-1 days","%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 +%m)
aa=cat /tmp/zabbix_statics.csv
mv /tmp/zabbix_statics.csv /tmp/zabbix_{mm}_statics.csv

本文转载自：https://www.jianshu.com/p/28718b2f70c4

打赏

微博

微信

打印

How I Simplified Oracle Database 12c and 11g Installations on Oracle Linux 6

ZABBIX数据库表结构解析

rootliu

粉丝 7

博文 481

码字总数 3022

作品 0

海淀

数据库管理员

关注

私信

提问

加载中

请先登录后再评论。

删除一条评论

评论删除后，数据将无法恢复

取消

确定

zabbix生成月度统计报表

zabbix生成月度统计报表

!/bin/bash

@version 0.1

@date 2017-12-31

rootliu

OSCHINA 社区

在线工具

微信公众号

OSCHINA APP

zabbix生成月度统计报表

zabbix生成月度统计报表

!/bin/bash

@version 0.1

@date 2017-12-31

rootliu

OSCHINA 社区

在线工具

微信公众号

OSCHINA APP

其他人也看了：