#!/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數(shù)據(jù)庫(kù)信息：`
`zdbhost = ``'127.0.0.1'`
`zdbuser = ``'zabbix'`
`zdbpass = ``'zabbix'`
`zdbport = 3306`
`zdbname = ``'zabbix'`
`#生成文件名稱：`
`xlsfilename = ``'Group_Production_Server.xlsx'`
`#需要查詢的key列表 [名稱，表名，key值，取值，格式化，數(shù)據(jù)整除處理]`
`keys = [`
`#  ['CPU核心數(shù)','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],`
`#['內(nèi)存大小(單位G)','trends_uint','vm.memory.size[total]','avg','',1048576000],`
`#['剩余內(nèi)存(單位G)','trends_uint','vm.memory.size[available]','avg','',1048576000],`
`[``'內(nèi)存使用率(%)'``,``'trends'``,``'Memory_used'``,``'avg'``,``'%.2f'``,1],`
`#  ['可用平均內(nèi)存(單位G)','trends_uint','vm.memory.size[available]','avg','',1048576000],`
`#  ['可用最小內(nèi)存(單位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],`
`#  ['根分區(qū)總大小(單位G)','trends_uint','vfs.fs.size[/,total]','avg','',1073741824],`
`#  ['根分區(qū)平均剩余(單位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],`
`#  ['進(jìn)入最大流量（單位Kbps）','trends_uint','net.if.in[eth0]','max','',1000],`
`#  ['進(jìn)入平均流量（單位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):`
`''``'打開數(shù)據(jù)庫(kù)連接'``''`
`self.conn = MySQLdb.connect(host=zdbhost,user=zdbuser,passwd=zdbpass,port=zdbport,db=zdbname)`
`self.cursor = self.conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)`
`#生成zabbix哪個(gè)分組報(bào)表`
`self.groupname = ``'Group_Production_Server'`
`#獲取IP信息：`
`self.IpInfoList = self.__getHostList()`
`def __getHostList(self):`
`''``'根據(jù)zabbix組名獲取該組所有IP'``''`
`#查詢組ID:`
`sql = ``''``'select groupid from groups where name = '``%s``' '``''` `% self.groupname`
`self.cursor.execute(sql)`
`groupid = self.cursor.fetchone()[``'groupid'``]`
`#根據(jù)groupid查詢?cè)摲纸M下面的所有主機(jī)ID（hostid）：`
`sql = ``''``'select hostid from hosts_groups where groupid = '``%s``' '``''` `% groupid`
`self.cursor.execute(sql)`
`hostlist = self.cursor.fetchall()`
`#生成IP信息字典：結(jié)構(gòu)為{'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):`
`''``'根據(jù)hostid,itemname獲取該監(jiān)控項(xiàng)的值'``''`
`#獲取上個(gè)月的第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):`
`#循環(huán)讀取IP列表信息`
`for` `ip,resultdict ``in` `zabbix.IpInfoList.items():`
`print ``"正在查詢 IP:%-15s hostid:%5d 的信息！"` `% (ip, resultdict[``'hostid'``])`
`#循環(huán)讀取keys，逐個(gè)key統(tǒng)計(jì)數(shù)據(jù)：`
`for` `value ``in` `keys:`
`print ``"\t正在統(tǒng)計(jì) 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文件'``''`
`#創(chuàng)建文件`
`workbook = xlsxwriter.Workbook(xlsfilename)`
`#創(chuàng)建工作薄`
`worksheet = workbook.add_worksheet()`
`#寫入第一列：`
`worksheet.write(0,0,``"主機(jī)"``.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'``))`
`#寫入該列內(nèi)容：`
`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):`
`''``'關(guān)閉數(shù)據(jù)庫(kù)連接'``''`
`self.cursor.close()`
`self.conn.close()`
`def Send_Email(self):`
`sender = ``'from@runoob.com'`
`receivers = [``'hejianlai@pci.cn'``] # 接收郵件，可設(shè)置為你的QQ郵箱或者其他郵箱`
`#創(chuàng)建一個(gè)帶附件的實(shí)例`
`message = MIMEMultipart()`
`message[``'From'``] = Header(``"Zabbix_server"``, ``'utf-8'``)`
`message[``'To'``] = Header(``"it"``, ``'utf-8'``)`
`subject = ``'生產(chǎn)環(huán)境虛機(jī)資源使用情況'`
`message[``'Subject'``] = Header(subject, ``'utf-8'``)`
`#郵件正文內(nèi)容`
`message.attach(MIMEText(``'生產(chǎn)環(huán)境虛機(jī)資源使用情況'``, ``'plain'``, ``'utf-8'``))`
`# 構(gòu)造附件1，傳送當(dāng)前目錄下的 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 ``"郵件發(fā)送成功"`
`except smtplib.SMTPException:`
`print ``"Error: 無(wú)法發(fā)送郵件"`
`if` `__name__ == ``"__main__"``:`
`zabbix = ReportForm()`
`zabbix.getInfo()`
`zabbix.writeToXls2()`
`zabbix.Send_Email()`