zabbix最新数据导出脚本，10.21修复去重功能
本帖最后由 枫狐狸 于 2021-10-21 11:31 编辑zabbix_report
#脚本基于4.4.7版本编写
#!/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' fromhistory 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 >$Tgroup by c.name order by c.name,a.clock;\"
WHEN 1 THEN \"select distinct(c.name) as hostname,',',a.value as '$object' fromhistory_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 >$Tgroup by c.name order by c.name,a.clock;\"
WHEN 2 THEN \"select distinct(c.name) as hostname,',',a.value as '$object' fromhistory_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 >$Tgroup by c.name order by c.name,a.clock;\"
WHEN 3 THEN \"select distinct(c.name) as hostname,',',a.value as '$object' fromhistory_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 >$Tgroup by c.name order by c.name,a.clock;\"
WHEN 4 THEN \"select distinct(c.name) as hostname,',',a.value as '$object' fromhistory_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 >$Tgroup 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' fromhistory 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' fromhistory_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' fromhistory_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' fromhistory_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' fromhistory_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
真是难得给力的帖子啊。 修复一下去重功能，代码已重帖。 修复一下去重功能，代码已重帖。
页:
[1]