Python如何自动化导出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()`

相关阅读