Zabbix 创建月度统计报表脚本(学习笔记十六)
一、脚本：
#!/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
二、报表格式如下：
三、zabbix表参考：
https://www.cnblogs.com/learningJAVA/p/7886164.html
https://blog.csdn.net/hy_timer/article/details/72082004
