[ python3 ] 基于zabbix 自动生成xlsx监控文档

　　准备做一个每周自动的巡检报告，数据来源于zabbix，通过python读取zabbix数据库获取数据并制作成excel表格，

本来打算直接邮件发送到指定邮箱，但是都被SMTP过滤掉了，试过126和QQ的，其他的不知道。

操作系统：Cenos 6.7

python版本： python3.5

以下代码是亲测并生产环境使用的：

#!/usr/local/python/bin/python3.5
import pymysql
import time, datetime
import smtplib
import os
from email.mime.text import MIMEText
from email.header import Header
# zabbix数据库信息

zdbhost = 'zabbix服务器ip'
zdbuser = 'zabbix数据库用户名'
zdbpass = 'zabbix数据库密码'
zdbport = 3306
zdbname = 'zabbix'

d = datetime.datetime.now()
day = datetime.date.today()
keys = {
    'trends_uint': [
        'net.if.in[eth0]',
        'net.if.out[eth0]',
        'vfs.fs.size[/,used]',
        'vm.memory.size[available]',
    ],
    'trends': [
        'system.cpu.load[percpu,avg5]',
        'system.cpu.util[,idle]',
    ],
}
class ReportForm:

    def __init__(self):
        self.conn = pymysql.connect(host=zdbhost, user=zdbuser, passwd=zdbpass, port=zdbport, db=zdbname)
        self.cursor = self.conn.cursor()
        self.groupname = 'Linux servers'
        self.IpInfoList = self.__getHostList()
        # return self.IpInfoList

    def __getHostList(self):
        sql = '''select groupid from groups where name = '%s' ''' % self.groupname
        self.cursor.execute(sql)
        groupid = self.cursor.fetchone()[0]
        print(groupid)

        sql = '''select hostid from hosts_groups where groupid = %s''' % groupid
        self.cursor.execute(sql)
        hostlist = self.cursor.fetchall()

        IpInfoList = {}
        for i in hostlist:
            hostid = i[0]
            sql = '''select host from hosts where status = 0 and hostid = %s''' % hostid
            ret = self.cursor.execute(sql)
            if ret:
                IpInfoList[self.cursor.fetchone()[0]] = {'hostid': hostid}
        return IpInfoList

    def __getItemid(self, hostid, itemname):
        sql = '''select itemid from items where hostid = %s and key_ = '%s' ''' % (hostid, itemname)
        if self.cursor.execute(sql):
            itemid = self.cursor.fetchone()[0]
        else:
            itemid = None
        return itemid

    def getTrendsValue(self, itemid, start_time, stop_time):
        resultlist = {}
        for type in ['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()[0]
            if result == None:
                result = 0
            resultlist[type] = result
        return resultlist

    def getTrends_uintValue(self, itemid, start_time, stop_time):
        resultlist = {}
        for type in ['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()[0]
            if result:
                resultlist[type] = int(result)
            else:
                resultlist[type] = 0
        return resultlist



    def get_week(self, d):
        dayscount = datetime.timedelta(days=d.isoweekday())
        dayto = d - dayscount
        sixdays = datetime.timedelta(days=6)
        dayfrom = dayto - sixdays
        date_from = datetime.datetime(dayfrom.year, dayfrom.month, dayfrom.day, 0, 0, 0)
        date_to = datetime.datetime(dayto.year, dayto.month, dayto.day, 23, 59, 59)
        ts_first = int(time.mktime(datetime.datetime(dayfrom.year, dayfrom.month, dayfrom.day, 0, 0, 0).timetuple()))
        ts_last = int(time.mktime(datetime.datetime(dayto.year, dayto.month, dayto.day, 23, 59, 59).timetuple()))
        return ts_first, ts_last

    def getLastMonthData(self, hostid, table, itemname):
        ts_first = self.get_week(d)[0]
        ts_last = self.get_week(d)[1]
        itemid = self.__getItemid(hostid, itemname)
        # function = getattr(self, 'get %s Value' % table.capitalize())
        function = getattr(self, 'get%sValue' % table.capitalize())
        return function(itemid, ts_first, ts_last)

    def getinfo(self):
        for ip, resultdict in zabbix.IpInfoList.items():
            print("正在查询 IP:%-15s hostid:%5d 的信息！" % (ip, resultdict['hostid']))
            for table, keylists in keys.items():
                for key in keylists:
                    print("	正在统计 key_:%s" % key)
                    data = zabbix.getLastMonthData(resultdict['hostid'], table, key)
                    zabbix.IpInfoList[ip][key] = data

    def writeToXls(self):
        dayscount = datetime.timedelta(days=d.isoweekday())
        dayto = d - dayscount
        sixdays = datetime.timedelta(days=6)
        dayfrom = dayto - sixdays
        date_from = datetime.date(dayfrom.year, dayfrom.month, dayfrom.day)
        date_to = datetime.date(dayto.year, dayto.month, dayto.day)
        '''生成xls文件'''
        try:
            import xlsxwriter
            # 创建文件
            workbook = xlsxwriter.Workbook('/usr/monitor/week/%s_%s巡检报告.xlsx' % (date_from, date_to))
            # 创建工作薄
            worksheet = workbook.add_worksheet()
            # 写入标题（第一行）
            i = 0
            for value in ["主机", "CPU平均空闲值", "CPU最小空闲值", "可用平均内存(单位M)", "可用最小内存(单位M)", "CPU5分钟负载", "进入最大流量（单位Kbps）",
                          "进入平均流量（单位Kbps）", "出去最大流量（单位Kbps）", "出去平均流量（单位Kbps）"]:
                worksheet.write(0, i, value)
                i = i + 1
            # 写入内容：
            j = 1
            for ip, value in self.IpInfoList.items():
                worksheet.write(j, 0, ip)
                worksheet.write(j, 1, '%.2f' % value['system.cpu.util[,idle]']['avg'])
                worksheet.write(j, 2, '%.2f' % value['system.cpu.util[,idle]']['min'])
                worksheet.write(j, 3, '%dM' % int(value['vm.memory.size[available]']['avg'] / 1024 / 1024))
                worksheet.write(j, 4, '%dM' % int(value['vm.memory.size[available]']['min'] / 1024 / 1024))
                worksheet.write(j, 5, '%.2f' % value['system.cpu.load[percpu,avg5]']['avg'])
                worksheet.write(j, 6, value['net.if.in[eth0]']['max'] / 1000)
                worksheet.write(j, 7, value['net.if.in[eth0]']['avg'] / 1000)
                worksheet.write(j, 8, value['net.if.out[eth0]']['max'] / 1000)
                worksheet.write(j, 9, value['net.if.out[eth0]']['avg'] / 1000)
                j = j + 1
            workbook.close()
        except Exception as e:
            print(e)
    def __del__(self):
        '''关闭数据库连接'''
        self.cursor.close()
        self.conn.close()

    def sendmail(self):
        sender = '发送者邮箱'
        receiver = ['接收者1', '接收者2', '接收者3']
        subject = '上周巡检报告'
        smtpserver = 'smtp.126.com'
        username = '发送者邮箱'
        password = 'smtp认证密码'

        msg = MIMEText('上周巡检报告已生成，请检查。
报告目录:/usr/monitor
由于邮件无法发送excel文档，请使用ftp登录'
                       '查看。
ftp地址:xxx:端口
ftp用户名：xxx
', 'plain', 'utf-8')  # 中文需参数‘utf-8'，单字节字符不需要
        msg['Subject'] = Header(subject, 'utf-8')
        msg['From'] = 'Robot<发送者邮箱>'
        msg['To'] = "接收者名字"
        smtp = smtplib.SMTP()
        smtp.connect('smtp.126.com')
        smtp.login(username, password)
        smtp.sendmail(sender, receiver, msg.as_string())
        print('发送成功！')
        smtp.quit()

if __name__ == "__main__":
    zabbix = ReportForm()
    zabbix.getinfo()
    zabbix.writeToXls()
    zabbix.sendmail()

　　说明： 由于邮件无法发送附件为excel，所以我这里写了一个发送通知邮件的方法，需要通过ftp登录并下载。

附上生成execl部分截图：