主要涉及两个sql
sql1:
select t1.hostid,t1.name from hosts t1 join hosts_groups t2 on t1.hostid=t2.hostid where t1.available=1 and t1.status=0 and t2.groupid in(1,2);
t2.groupid in(1,2)为主机群组的groupid,这里举例groupid为1和2,可以直接查询groups表获取groupid。如果不区分群组可以去掉这个条件。
sql2:
SELECT t3.name,t1.value FROM history t1 join items t2 on t1.itemid=t2.itemid join hosts t3 on t3.hostid=t2.hostid WHERE t2.key_="vfs.fs.size[/export,pfree]" and t3.hostid=%s order by t1.clock DESC LIMIT 1;
t2.key_是监控项的表达式,这里是vfs.fs.size[/data,pfree],也就是/data路径下的剩余空间的占比,更改为自己想要的路径即可,也可以使用其他监控项,比如内存cpu等等。t3.hostid的值是sql1查询出的hostid,循环查询即可。
以下是根据这两个sql写的python脚本,可以直接得到结果
import pymysqldef execute():disk_info = []sql1 = """select t1.hostid,t1.name from hosts t1 join hosts_groups t2 on \t1.hostid=t2.hostid where t1.available=1 and t1.status=0 and t2.groupid in(1,1);"""db = pymysql.connect(host='10.10.10.10', user='root', password='mypassword',database='zabbix',port=3306, charset='utf8')cursor1 = db.cursor()cursor1.execute(sql1)host_info = cursor1.fetchall()for i in host_info:sql2 = """SELECT t3.name,t1.value FROM history t1 join items t2 on t1.itemid=t2.itemid \join hosts t3 on t3.hostid=t2.hostid WHERE t2.key_="vfs.fs.size[/export,pfree]" \and t3.hostid=%s order by t1.clock DESC LIMIT 1;""" % i[0]cursor2 = db.cursor()cursor2.execute(sql2)disk_info2 = cursor2.fetchall()disk_info.append(disk_info2)db.close()for j in disk_info:try:print(j[0])except:print(j)execute()