ZABBIX3.2使用python脚本实现监控报表

 脚本：

#!/usr/bin/python  
#coding:utf-8  
   
import MySQLdb  
import time,datetime  

#zabbix数据库信息：  
zdbhost = '172.16.8.200'  
zdbuser = 'zabbix'  
zdbpass = 'zabbix'  
zdbport = 3306  
zdbname = 'zabbix'  
   
#生成文件名称：  
xlsfilename = 'zabbix.xls'  
   
#需要查询的key列表 [名称，表名，key值，取值，格式化，数据整除处理]  
keys = [  
    ['CPU核心数','trends_uint','system.cpu.num','avg','',1],  
    ['CPU平均空闲值','trends','system.cpu.util[,idle]','avg','%.2f',1],  
    ['CPU最小空闲值','trends','system.cpu.util[,idle]','min','%.2f',1],  
    ['CPU5分钟负载','trends','system.cpu.load[percpu,avg5]','avg','%.2f',1],  
    ['物理内存大小(单位G)','trends_uint','vm.memory.size[total]','avg','',1048576000],  
    ['可用平均内存(单位G)','trends_uint','vm.memory.size[available]','avg','',1048576000],  
    ['可用最小内存(单位G)','trends_uint','vm.memory.size[available]','min','',1048576000],  
    ['swap总大小(单位G)','trends_uint','system.swap.size[,total]','avg','',1048576000],  
    ['swap平均剩余(单位G)','trends_uint','system.swap.size[,free]','avg','',1048576000],  
    ['根分区总大小(单位G)','trends_uint','vfs.fs.size[/,total]','avg','',1073741824],  
    ['根分区平均剩余(单位G)','trends_uint','vfs.fs.size[/,free]','avg','',1073741824],  
    ['进入最大流量（单位Kbps）','trends_uint','net.if.in[eth0]','max','',1000],  
    ['进入平均流量（单位Kbps）','trends_uint','net.if.in[eth0]','avg','',1000],  
    ['出去最大流量（单位Kbps）','trends_uint','net.if.out[eth0]','max','',1000],  
    ['出去平均流量（单位Kbps）','trends_uint','net.if.out[eth0]','avg','',1000],  
]  
   
   
class ReportForm:  
   
    def __init__(self):  
        '''''打开数据库连接'''  
        self.conn = MySQLdb.connect(host=zdbhost,user=zdbuser,passwd=zdbpass,port=zdbport,db=zdbname)  
        self.cursor = self.conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)  
   
        #生成zabbix哪个分组报表  
        self.groupname = 'zabbix server'  
   
        #获取IP信息：  
        self.IpInfoList = self.__getHostList()  
   
    def __getHostList(self):  
        '''''根据zabbix组名获取该组所有IP'''  
   
        #查询组ID:  
        sql = '''''select groupid from groups where name = '%s' ''' % self.groupname  
        self.cursor.execute(sql)  
        groupid = self.cursor.fetchone()['groupid']  
   
        #根据groupid查询该分组下面的所有主机ID（hostid）：  
        sql = '''''select hostid from hosts_groups where groupid = %s''' % groupid  
        self.cursor.execute(sql)  
        hostlist = self.cursor.fetchall()  
   
        #生成IP信息字典：结构为{'119.146.207.19':{'hostid':10086L,},}  
        IpInfoList = {}  
        for i in hostlist:  
            hostid = i['hostid']  
            sql = '''''select host from hosts where status = 0 and hostid = %s''' % hostid  
            ret = self.cursor.execute(sql)  
            if ret:  
                IpInfoList[self.cursor.fetchone()['host']] = {'hostid':hostid}  
        return IpInfoList  
   
    def __getItemid(self,hostid,itemname):  
        '''''获取itemid'''  
        sql = '''''select itemid from items where hostid = %s and key_ = '%s' ''' % (hostid, itemname)  
        if self.cursor.execute(sql):  
            itemid = self.cursor.fetchone()['itemid']  
        else:  
            itemid = None  
        return itemid  
   
    def getTrendsValue(self,type, itemid, start_time, stop_time):  
        '''''查询trends_uint表的值,type的值为min,max,avg三种'''  
        sql = '''''select %s(value_%s) as result from trends where itemid = %s and clock >= %s and clock <= %s''' % (type, type, itemid, start_time, stop_time)  
        self.cursor.execute(sql)  
        result = self.cursor.fetchone()['result']  
        if result == None:  
            result = 0  
        return result  
   
    def getTrends_uintValue(self,type, itemid, start_time, stop_time):  
        '''''查询trends_uint表的值,type的值为min,max,avg三种'''  
        sql = '''''select %s(value_%s) as result from trends_uint where itemid = %s and clock >= %s and clock <= %s''' % (type, type, itemid, start_time, stop_time)  
        self.cursor.execute(sql)  
        result = self.cursor.fetchone()['result']  
        if result:  
            result = int(result)  
        else:  
            result = 0  
        return result  
   
   
    def getLastMonthData(self,type,hostid,table,itemname):  
        '''''根据hostid,itemname获取该监控项的值'''  
        #获取上个月的第一天和最后一天  
        ts_first = int(time.mktime(datetime.date(datetime.date.today().year,datetime.date.today().month-1,1).timetuple()))  
        lst_last = datetime.date(datetime.date.today().year,datetime.date.today().month,1)-datetime.timedelta(1)  
        ts_last = int(time.mktime(lst_last.timetuple()))  
   
        itemid = self.__getItemid(hostid, itemname)  
   
        function = getattr(self,'get%sValue' % table.capitalize())  
   
        return  function(type,itemid, ts_first, ts_last)  
   
    def getInfo(self):  
        #循环读取IP列表信息  
        for ip,resultdict in  zabbix.IpInfoList.items():  
            print "正在查询 IP:%-15s hostid:%5d 的信息！" % (ip, resultdict['hostid'])  
            #循环读取keys，逐个key统计数据：  
            for value in keys:  
                print "\t正在统计 key_:%s" % value[2]  
                if not value[2] in zabbix.IpInfoList[ip]:  
                    zabbix.IpInfoList[ip][value[2]] = {}  
                data =  zabbix.getLastMonthData(value[3], resultdict['hostid'],value[1],value[2])  
                zabbix.IpInfoList[ip][value[2]][value[3]] = data  
   
   
    def writeToXls2(self):  
        '''''生成xls文件'''  
        try:  
            import xlsxwriter  
   
            #创建文件  
            workbook = xlsxwriter.Workbook(xlsfilename)  
   
            #创建工作薄  
            worksheet = workbook.add_worksheet()  
   
            #写入第一列：  
            worksheet.write(0,0,"主机".decode('utf-8'))  
            i = 1  
            for ip in self.IpInfoList:  
                worksheet.write(i,0,ip)  
                i = i + 1  
   
            #写入其他列：  
            i = 1  
            for value in keys:  
                worksheet.write(0,i,value[0].decode('utf-8'))  
   
                #写入该列内容：  
                j = 1  
                for ip,result in self.IpInfoList.items():  
                    if value[4]:  
                        worksheet.write(j,i, value[4] % result[value[2]][value[3]])  
                    else:  
                        worksheet.write(j,i, result[value[2]][value[3]] / value[5])  
                    j = j + 1  
   
                i = i + 1  
        except Exception,e:  
            print e  
   
   
   
    def __del__(self):  
        '''''关闭数据库连接'''  
        self.cursor.close()  
        self.conn.close()  
   
if __name__ == "__main__":  
    zabbix = ReportForm()  
    zabbix.getInfo()  
    zabbix.writeToXls2()