通过zabbix数据库批量查询服务器最新磁盘剩余空间

主要涉及两个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 pymysql


def 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()