Python自动化:导出zabbix数据并发邮件脚本

作者: IT派森 | 来源:发表于2019-08-15 22:44 被阅读5次

    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 ``"\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文件'``''`
    
    `#创建文件`
    
    `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()`
    
    

    如果你依然在编程的世界里迷茫,可以加入我们的Python学习扣qun:784758214,看看前辈们是如何学习的!交流经验!自己是一名高级python开发工程师,从基础的python脚本到web开发、爬虫、django、数据挖掘等,零基础到项目实战的资料都有整理。送给每一位python的小伙伴!分享一些学习的方法和需要注意的小细节,点击加入我们的 python学习者聚集地

    相关文章

      网友评论

        本文标题:Python自动化:导出zabbix数据并发邮件脚本

        本文链接:https://www.haomeiwen.com/subject/fqptsctx.html