python导出zabbix数据并发邮件脚本

Zabbix没有报表导出的功能，于是通过编写脚本导出zabbix数据并发邮件。效果如下：

下面是脚本，可根据自己的具体情况修改：

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

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header

 
 
#zabbix数据库信息：
zdbhost = '127.0.0.1'
zdbuser = 'zabbix'
zdbpass = 'zabbix'
zdbport = 3306
zdbname = 'zabbix'
 
#生成文件名称：
xlsfilename = 'Group_Production_Server.xlsx'
 
#需要查询的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],
    ['CPU使用率(%)','trends','CPU_used','avg','%.2f',1],
    #['内存大小(单位G)','trends_uint','vm.memory.size[total]','avg','',1048576000],
    #['剩余内存(单位G)','trends_uint','vm.memory.size[available]','avg','',1048576000],
    ['内存使用率(%)','trends','Memory_used','avg','%.2f',1],
#    ['可用平均内存(单位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],
    #['磁盘总大小(单位G)','trends_uint','vfs.fs.size[/fs01,total]','avg','',1073741824],
    #['磁盘剩余(单位G)','trends_uint','vfs.fs.size[/fs01,free]','avg','',1073741824],
    ['磁盘使用率(%)','trends','fs01_used','avg','%.2f',1],
#    ['进入最大流量（单位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 = 'Group_Production_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获取该监控项的值'''
        #获取上个月的第20天和最后1天
        ts_first = int(time.mktime(datetime.date(datetime.date.today().year,datetime.date.today().month-1,20).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 getNowData(self):
        nowtime = datetime.datetime.now().strftime('%Y-%m-%d')
        return nowtime
 
    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 "	正在统计 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文件'''
            #创建文件
        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
        workbook.close()
 
 
 
    def __del__(self):
        '''关闭数据库连接'''
        self.cursor.close()
        self.conn.close()
    def Send_Email(self):
        sender = 'from@runoob.com'
	receivers = ['hejianlai@pci.cn']  # 接收邮件，可设置为你的QQ邮箱或者其他邮箱
	 
	#创建一个带附件的实例
	message = MIMEMultipart()
	message['From'] = Header("Zabbix_server", 'utf-8')
	message['To'] =  Header("it", 'utf-8')
	subject = '生产环境虚机资源使用情况'
	message['Subject'] = Header(subject, 'utf-8')
	 
	#邮件正文内容
	message.attach(MIMEText('生产环境虚机资源使用情况', 'plain', 'utf-8'))
	 
	# 构造附件1，传送当前目录下的 test.txt 文件
	att1 = MIMEText(open('Group_Production_Server.xlsx', 'rb').read(), 'base64', 'utf-8')
	att1["Content-Type"] = 'application/octet-stream'
	# 这里的filename可以任意写，写什么名字，邮件中显示什么名字
	att1["Content-Disposition"] = 'attachment; filename="Group_Production_Server.xlsx"'
	message.attach(att1)
	 
	try:
		smtpObj = smtplib.SMTP('localhost')
		smtpObj.sendmail(sender, receivers, message.as_string())
		print "邮件发送成功"
	except smtplib.SMTPException:
		print "Error: 无法发送邮件"
 
if __name__ == "__main__":
    zabbix = ReportForm()
    zabbix.getInfo()
    zabbix.writeToXls2()
    zabbix.Send_Email()