zabbix磁盘使用率每周报表脚本 python

环境：

python3.7

pip安装pymysql,xlsxwriter模块 

#!/usr/bin/python
#encoding:utf-8

import pymysql
import time,datetime
import xlsxwriter
import smtplib
import traceback
from email.mime.multipart import MIMEMultipart   
from email.mime.text import MIMEText
from email.header import Header  

#zabbix数据库信息：
zdbhost = '192.168.1.xx'
zdbuser = 'zabbix'
zdbpass = 'zabbix'
zdbport = 3306
zdbname = 'zabbix'

#生成文件名称：
linuxfilename = 'Linux_Report_Disk_%s.xlsx' % time.strftime("%Y-%m-%d", time.localtime())
windowsfilename = 'Windows_Report_Disk_%s.xlsx' % time.strftime("%Y-%m-%d", time.localtime())

#生成zabbix哪个分组报表
groupname = 'Linux'
groupname_2 = 'Windows'

#需要查询的key列表 [名称，表名，key值，取值，格式化，数据整除处理]
keys = [
['分区总大小(GB)','trends_uint','total','avg','',1073741824],
['分区平均剩余(GB)','trends_uint','free','avg','',1073741824],
['分区可用率%','trends','pfree','avg','%.2f',1],
]

class ReportForm:

    def __init__(self,groupname):
       
        #打开数据库连接
        self.conn = pymysql.connect(host=zdbhost,user=zdbuser,passwd=zdbpass,port=zdbport,db=zdbname,cursorclass=pymysql.cursors.DictCursor)
        self.cursor = self.conn.cursor()

        #获取IP,目录信息：
        self.HostDirList =self.getHostDirList(groupname)

 
    #根据zabbix组名获取该组所有IP
    def getgroupid(self,groupname):
        #查询组ID:

        sql = '''select groupid from groups where name = '%s' ''' % groupname

        self.cursor.execute(sql)

        groupid =self.cursor.fetchone()['groupid']

        return groupid

 
    #根据groupid查询该分组下面的所有主机ID（hostid）：
    def gethostid(self,groupname):

        groupid =self.getgroupid(groupname)

        sql = '''select hostid from hosts_groups where groupid = %s''' % groupid

        self.cursor.execute(sql)

        hostlist = self.cursor.fetchall()

        return hostlist

 
    #生成IP,Item字典:结构为：{'host': '192.168.52.47', 'hostid': 30478, 'dir': '/boot'}
    def getHostDirList(self,groupname):

        hostlist =self.gethostid(groupname)

        HostDirList = []

        for i in hostlist:

            hostid = i['hostid']

            sql = '''select DISTINCT a.host,a.hostid,substring(b.key_,13,CHAR_LENGTH(b.key_)-18) dirname from hosts a,items b 
                     where a.hostid= %s and a.status = 0 and a.available=1 and a.hostid=b.hostid and b.key_ like 'vfs.fs.size%%' 
                     and b.key_ like '%%,free]' and b.error='' and b.templateid is null ''' % hostid

            self.cursor.execute(sql)
            result = self.cursor.fetchall()

            HostDirList.extend(result)

        return HostDirList

    

    #获取itemid
    def getItemid(self,hostid,itemname):

        sql = '''select itemid from items where hostid = %s and error='' and key_ = '%s' ''' % (hostid, itemname)

        if self.cursor.execute(sql):

            itemid =self.cursor.fetchone()['itemid']

        else:

            itemid = None

        return itemid

    #查询trends表的值,type的值为min,max,avg三种
    def getTrendsValue(self,type,itemid, start_time):

        sql = '''select %s(value_%s) as result from trends where itemid = %s and clock >= %s ''' % (type, type, itemid, start_time)

        self.cursor.execute(sql)

        result =self.cursor.fetchone()['result']

        if result == None:

            result = 0

        return result

 
    #查询trends_uint表的值,type的值为min,max,avg三种
    def getTrends_uintValue(self,type,itemid, start_time):

        sql = '''select %s(value_%s) as result from trends_uint where itemid = %s and clock >= %s ''' % (type, type, itemid, start_time)

        self.cursor.execute(sql)

        result =self.cursor.fetchone()['result']

        if result:

            result = int(result)

        else:

            result = 0

        return result

 

 
    #根据hostid,itemname获取该监控项的值
    def getTodayData(self,type,hostid,table,itemname):

        #获当天时间
        ts_date =int(time.mktime(datetime.date.today().timetuple()))

        itemid =self.getItemid(hostid, itemname)

        function =getattr(self,'get%sValue' % table.capitalize())

 

        return  function(type,itemid, ts_date)

 

    def getInfo(self):

        #循环读取IP列表信息

        for index,item in enumerate(self.HostDirList):


        #循环读取keys，逐个key统计数据：


            for value in keys:

        #['分区总大小(GB)','trends_uint',',total]','avg','',1073741824]
                keyname='vfs.fs.size['+item['dirname']+','+value[2]+']'

                print ("\t正在统计key_:%s" % keyname)

                data =  self.getTodayData(value[3],item['hostid'],value[1],keyname)

                self.HostDirList[index][value[2]] = data
                
 
    #生成xls文件
    def writeToXls(self,xlsfilename,keysname):

            #创建文件
            workbook =xlsxwriter.Workbook(xlsfilename)

            #生产时间区间
            start_day=datetime.date.today()

            #创建工作薄
            worksheet =workbook.add_worksheet('%s' % start_day)
            
            #设置列宽
            worksheet.set_column('A:E', 16)

            #创建单元格格式
            format1 = workbook.add_format()
            format1.set_fg_color('#003063')
            format1.set_font_color('white')
            format1.set_border(1)

            format2 = workbook.add_format()
            format2.set_border(1)


            #写入第一列：
            worksheet.write(0,0,"主机名称IP",format1)
            worksheet.write(0,1,"磁盘目录",format1)
            i = 1
            for item in  self.HostDirList:
                worksheet.write(i,0,item['host'],format2)
                worksheet.write(i,1,item['dirname'],format2)              
                i = i + 1

            #写入其他列：
            i = 2

            for value in keysname:
                worksheet.write(0,i,value[0],format1)

                #写入该列内容：
                j = 1
                for item in self.HostDirList:
                    if value[4]:
                       worksheet.write(j,i, value[4] % item[value[2]],format2)
                    else:
                       worksheet.write(j,i, "{:.2f}".format(item[value[2]] / value[5]),format2)
                    j = j + 1

                i = i + 1
            workbook.close()


    #关闭数据库连接
    def __del__(self):


        self.cursor.close()
        self.conn.close()
        
#发送邮件
def sendmail():
    #设置smtplib所需的参数
    #下面的发件人，收件人是用于邮件传输的
    smtpserver = 'mail.qq.com'
    sender='123@qq.com'
    #收件人为多个收件人,逗号分隔
    to_mail=['123@qq.com']
    cc_mail=['123@qq.com']
        
    #设置标题
    subject = '测试Zabbix监控周报：磁盘'
    subject=Header(subject, 'utf-8').encode()
        
    #构造邮件对象MIMEMultipart对象
    #下面的主题，发件人，收件人，日期是显示在邮件页面上的。
    msg = MIMEMultipart('mixed') 
    msg['Subject'] = subject
    msg['From'] = '123@qq.com'
    #收件人为多个收件人,通过join将列表转换为以;为间隔的字符串
    msg['To'] = ";".join(to_mail)
    msg['Cc'] = ";".join(cc_mail)

        
    #构造文字内容   
    text = "Zabbix监控周报：磁盘\n\n请查收！"    
    text_plain = MIMEText(text,'plain', 'utf-8')    
    msg.attach(text_plain)
        
    #构造附件
    linuxfile=open(r'%s' % linuxfilename,'rb').read()
    text_att = MIMEText(linuxfile, 'base64', 'utf-8') 
    text_att["Content-Type"] = 'application/octet-stream'
    text_att["Content-Disposition"] = 'attachment; filename=%s' % linuxfilename
    msg.attach(text_att)

    windowsfile=open(r'%s' % windowsfilename,'rb').read()
    text_att2 = MIMEText(windowsfile, 'base64', 'utf-8') 
    text_att2["Content-Type"] = 'application/octet-stream'
    text_att2["Content-Disposition"] = 'attachment; filename=%s' % windowsfilename
    msg.attach(text_att2)
        
    #发送邮件
    smtp = smtplib.SMTP()    
    smtp.connect('mail.shanghaitrust.com', '25')
    smtp.sendmail(sender, to_mail+cc_mail, msg.as_string())  
    smtp.quit()
    

if __name__ == "__main__":
    try:
        
        linux = ReportForm(groupname)
        linux.getInfo()
        linux.writeToXls(linuxfilename,keys)

        windows = ReportForm(groupname_2)
        windows.getInfo()
        windows.writeToXls(windowsfilename,keys)
        sendmail()

    except:  
        f=open("zabbix_log.txt",'a')  
        traceback.print_exc(file=f)  
        f.flush()  
        f.close()