[Bash shell] 纯文本查看 复制代码
#!/bin/bash
clear
echo " 命令格式: "
echo " 1、zabbix_report 监控项名称 主机群组 #按组导出,监控项名称和主机群组不能带空格"
echo " 2、zabbix_report 监控项名称 #监控项名称不能带空格 "
echo " 2、zabbix_report #按提示输入 "
echo -e "\n"
DT=`date +"%Y%m%d_%H%M%S"` #当前时间
let "T=`date +%s`-31600" #6小时前的时间戳
if [ -z $1 ];then
echo -n " 请输入要查询的监控项:"
read object
else
object=$1
fi
if [ -z $2 ];then
echo -n " 请输入主机群组名称:"
read gid
else
gid=$2
fi
if [ -z $gid ];then
mysql -ureport -preport -h 10.239.150.40 zabbix -e "SElECT distinct(CASE value_type
WHEN 0 THEN \"select distinct(c.name) as hostname,',',a.value as '$object' from history a ,items b,hosts c where a.itemid in (select itemid from items where name ='$object') and a.itemid=b.itemid and b.hostid=c.hostid and a.clock >$T group by c.name order by c.name,a.clock;\"
WHEN 1 THEN \"select distinct(c.name) as hostname,',',a.value as '$object' from history_str a ,items b,hosts c where a.itemid in (select itemid from items where name ='$object') and a.itemid=b.itemid and b.hostid=c.hostid and a.clock >$T group by c.name order by c.name,a.clock;\"
WHEN 2 THEN \"select distinct(c.name) as hostname,',',a.value as '$object' from history_log a ,items b,hosts c where a.itemid in (select itemid from items where name ='$object') and a.itemid=b.itemid and b.hostid=c.hostid and a.clock >$T group by c.name order by c.name,a.clock;\"
WHEN 3 THEN \"select distinct(c.name) as hostname,',',a.value as '$object' from history_uint a,items b,hosts c where a.itemid in (select itemid from items where name ='$object') and a.itemid=b.itemid and b.hostid=c.hostid and a.clock >$T group by c.name order by c.name,a.clock;\"
WHEN 4 THEN \"select distinct(c.name) as hostname,',',a.value as '$object' from history_text a,items b,hosts c where a.itemid in (select itemid from items where name ='$object') and a.itemid=b.itemid and b.hostid=c.hostid and a.clock >$T group by c.name order by c.name,a.clock;\"
END) as sqltext FROM items where name='$object';" > /tmp/sqltext_$DT.sql
else
mysql -ureport -preport -h 10.239.150.40 zabbix -e "SElECT distinct(CASE value_type
WHEN 0 THEN \"select distinct(c.name) as hostname,',',a.value as '$object' from history a ,items b,hosts c where a.itemid in (select itemid from items where name ='$object') and c.hostid in (select a.hostid from hosts_groups a,hosts b where b.status=0 and a.hostid=b.hostid and a.groupid=(select groupid from hstgrp where name = '$gid')) and a.itemid=b.itemid and b.hostid=c.hostid and a.clock >$T group by c.name order by c.name,a.clock;\"
WHEN 1 THEN \"select distinct(c.name) as hostname,',',a.value as '$object' from history_str a ,items b,hosts c where a.itemid in (select itemid from items where name ='$object') and c.hostid in (select a.hostid from hosts_groups a,hosts b where b.status=0 and a.hostid=b.hostid and a.groupid=(select groupid from hstgrp where name = '$gid')) and a.itemid=b.itemid and b.hostid=c.hostid and a.clock >$T group by c.name order by c.name,a.clock;\"
WHEN 2 THEN \"select distinct(c.name) as hostname,',',a.value as '$object' from history_log a ,items b,hosts c where a.itemid in (select itemid from items where name ='$object') and c.hostid in (select a.hostid from hosts_groups a,hosts b where b.status=0 and a.hostid=b.hostid and a.groupid=(select groupid from hstgrp where name = '$gid')) and a.itemid=b.itemid and b.hostid=c.hostid and a.clock >$T group by c.name order by c.name,a.clock;\"
WHEN 3 THEN \"select distinct(c.name) as hostname,',',a.value as '$object' from history_uint a,items b,hosts c where a.itemid in (select itemid from items where name ='$object') and c.hostid in (select a.hostid from hosts_groups a,hosts b where b.status=0 and a.hostid=b.hostid and a.groupid=(select groupid from hstgrp where name = '$gid')) and a.itemid=b.itemid and b.hostid=c.hostid and a.clock >$T group by c.name order by c.name,a.clock;\"
WHEN 4 THEN \"select distinct(c.name) as hostname,',',a.value as '$object' from history_text a,items b,hosts c where a.itemid in (select itemid from items where name ='$object') and c.hostid in (select a.hostid from hosts_groups a,hosts b where b.status=0 and a.hostid=b.hostid and a.groupid=(select groupid from hstgrp where name = '$gid')) and a.itemid=b.itemid and b.hostid=c.hostid and a.clock >$T group by c.name order by c.name,a.clock;\"
END) as sqltext FROM items where name='$object';" > /tmp/sqltext_$DT.sql
fi
sed -i '/sqltext/d' /tmp/sqltext_$DT.sql
mysql -ureport -preport -h 10.239.150.40 zabbix -e "source /tmp/sqltext_$DT.sql" #屏显
#mysql -ureport -preport -h 10.239.150.40 zabbix -e "source /tmp/sqltext_$DT.sql" > /tmp/report_$DT.csv #输出到csv文件
rm -f /tmp/sqltext_$DT.sql