!/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

{HOST1} -u

{USER1} -p

{PASSWORD1} -D

{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 '\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(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 +%Y) mm=$ (date +%m)

aa=cat /tmp/zabbix_statics.csv

mv /tmp/zabbix_statics.csv /tmp/zabbix_

{yy}

{mm}_statics.csv