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

Python  /  管理员 发布于 7年前   189

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 = ``'[email protected]'``receivers = [``'[email protected]'``] # 接收邮件，可设置为你的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自动化导出zabbix数据并发邮件脚本,希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！