python3获取zabbix磁盘数据并发送邮件

python3获取zabbix磁盘数据并发送邮件

测试的系统环境

Python3.8.3

Windows10

Zabbix 4.0.19
zabbix磁盘数据

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

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

        #打开数据库连接
        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 hstgrp 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

    #根据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 mail():
            #设置smtplib所需的参数
            #下面的发件人，收件人是用于邮件传输的
            smtpserver = '163.com'
            sender='sz@163.com'
             #收件人为多个收件人,逗号分隔
            to_mail=[suixin@163.com']
            cc_mail=['suixin1@163.com','suixin2@163.com']
                    
                #设置标题
                subject = 'Zabbix监控周报：磁盘'
                subject=Header(subject, 'utf-8').encode()
                    
                #构造邮件对象MIMEMultipart对象
                #下面的主题，发件人，收件人，日期是显示在邮件页面上的。
                msg = MIMEMultipart('mixed')
                msg['Subject'] = subject
                msg['From'] = 'suixin@163.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('smtp.163.com', '25')
                smtp.login('suixin@163.com', 'KEY')
                smtp.sendmail(sender, to_mail+cc_mail, msg.as_string())
                print('发送成功！')
                smtp.quit()

需要完整的代码，可以私信我要。