Zabbix has no report export function, so it exports ZABBIX data and sends mail concurrently by writing scripts. The results are as follows:
[External Chain Picture Transfer.. (img-YujWHCbu-1565876650802)]
Following is the script, which can be modified according to its own specific circumstances:
`#!/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 database information:`
`zdbhost = ``'127.0.0.1'`
`zdbuser = ``'zabbix'`
`zdbpass = ``'zabbix'`
`zdbport = 3306`
`zdbname = ``'zabbix'`
`#Generate file name:`
`xlsfilename = ``'Group_Production_Server.xlsx'`
`#List of keys to query [name, table name, key value, fetch value, formatting, data division processing]`
`keys = [`
`# ['CPU Core Number','trends_uint','system. cpu. num','avg',', 1],`
`#['Average idle CPU','trends','system.cpu.util[,idle]','avg','%.2f',1],'`
`#['CPU minimum idle value','trends','system.cpu.util[,idle]','min','%.2f',1],,`
`[``'CPU Utilization rate(%)'``,``'trends'``,``'CPU_used'``,``'avg'``,``'%.2f'``,1],`
`#['Memory size (unit G)','trends_uint','vm. memory. size [total],'avg',', 1048576000],'`
`#['Remaining memory (unit G)','trends_uint','vm. memory. size [available],'avg',', 1048576000],,`
`[``'Memory utilization(%)'``,``'trends'``,``'Memory_used'``,``'avg'``,``'%.2f'``,1],`
`# ['Available Average Memory (Unit G)','trends_uint','vm.memory.size [available],'avg',', 1048576000],,`
`# ['Available Minimum Memory (Unit G)','trends_uint','vm.memory.size [available],'min',', 1048576000],'`
`# ['swap total size (unit G)','trends_uint','system. swap. size [, total]','avg',', 1048576000],,`
`# ['swap average residue (unit G)','trends_uint','system. swap. size [, free],'avg',', 1048576000],,`
`# ['Total root partition size (unit G)','trends_uint','vfs.fs.size [/, total]','avg',', 1073741824],,`
`# ['Root partition average residue (unit G)','trends_uint','vfs.fs.size [/, free]','avg',', 1073741824],,`
`#['Total Disk Size (Unit G)','trends_uint','vfs.fs.size [/fs01, total]','avg',', 1073741824],`
`#['Remaining Disk (Unit G)','trends_uint','vfs.fs.size [/fs01, free]','avg',', 1073741824],`
`[``'Disk utilization(%)'``,``'trends'``,``'fs01_used'``,``'avg'``,``'%.2f'``,1],`
`# ['Enter maximum traffic (unit Kbps)','trends_uint','net.if.in [eth0],'max',', 1000],'`
`# ['Entry average traffic (unit Kbps)','trends_uint','net.if.in [eth0],'avg',', 1000],'`
`# ['Maximum outgoing traffic (unit Kbps)','trends_uint','net.if.out [eth0],'max',', 1000],'`
`# ['Average outgoing traffic (per Kbps)','trends_uint','net.if.out [eth0],'avg',', 1000],'`
`]`
`class` `ReportForm:`
`def __init__(self):`
`''``'Open database connection'``''`
`self.conn = MySQLdb.connect(host=zdbhost,user=zdbuser,passwd=zdbpass,port=zdbport,db=zdbname)`
`self.cursor = self.conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)`
`#Which grouping report to generate zabbix`
`self.groupname = ``'Group_Production_Server'`
`#Get IP information:`
`self.IpInfoList = self.__getHostList()`
`def __getHostList(self):`
`''``'according to zabbix Group name gets all of the group IP'``''`
`#Query Group ID::`
`sql = ``''``'select groupid from groups where name = '``%s``' '``''` `% self.groupname`
`self.cursor.execute(sql)`
`groupid = self.cursor.fetchone()[``'groupid'``]`
`#Query all host IDs (hostid s) under the group according to the group ID:`
`sql = ``''``'select hostid from hosts_groups where groupid = '``%s``' '``''` `% groupid`
`self.cursor.execute(sql)`
`hostlist = self.cursor.fetchall()`
`#Generating IP Information Dictionary: Structure: {'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):`
`''``'Obtain 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):`
`''``'query trends_uint Table value,type The value is min,max,avg Three kinds'``''`
`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):`
`''``'query trends_uint Table value,type The value is min,max,avg Three kinds'``''`
`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):`
`''``'according to hostid,itemname Get the value of the monitoring item'``''`
`#Get the 20th and last day of last month`
`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):`
`#Loop read IP list information`
`for` `ip,resultdict ``in` `zabbix.IpInfoList.items():`
`print ``"Enquiry IP:%-15s hostid:%5d Information!"` `% (ip, resultdict[``'hostid'``])`
`#The keys are read in a loop, and key statistics are read one by one:___________`
`for` `value ``in` `keys:`
`print ``"\t In Statistics 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):`
`''``'generate xls file'``''`
`#Create files`
`workbook = xlsxwriter.Workbook(xlsfilename)`
`#Create Workbook`
`worksheet = workbook.add_worksheet()`
`#Write to the first column:`
`worksheet.write(0,0,``"Host"``.decode(``'utf-8'``))`
`i = 1`
`for` `ip ``in` `self.IpInfoList:`
`worksheet.write(i,0,ip)`
`i = i + 1`
`#Write to other columns:`
`i = 1`
`for` `value ``in` `keys:`
`worksheet.write(0,i,value[0].decode(``'utf-8'``))`
`#Write the column contents:`
`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):`
`''``'Close database connection'``''`
`self.cursor.close()`
`self.conn.close()`
`def Send_Email(self):`
`sender = ``'from@runoob.com'`
`receivers = [``'hejianlai@pci.cn'``] # Receive mail, can be set to your QQ mailbox or other mailbox`
`#Create an instance with attachments`
`message = MIMEMultipart()`
`message[``'From'``] = Header(``"Zabbix_server"``, ``'utf-8'``)`
`message[``'To'``] = Header(``"it"``, ``'utf-8'``)`
`subject = ``'Utilization of virtual machine resources in production environment'`
`message[``'Subject'``] = Header(subject, ``'utf-8'``)`
`#Mail body content`
`message.attach(MIMEText(``'Utilization of virtual machine resources in production environment'``, ``'plain'``, ``'utf-8'``))`
`# Construct Annex 1 to transfer the test.txt file in the current directory`
`att1 = MIMEText(open(``'Group_Production_Server.xlsx'``, ``'rb'``).read(), ``'base64'``, ``'utf-8'``)`
`att1[``"Content-Type"``] = ``'application/octet-stream'`
`# The filename here can be written arbitrarily, what names are written, and what names are displayed in the mail.
`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 ``"Successful mail delivery"`
`except smtplib.SMTPException:`
`print ``"Error: Unable to send mail"`
`if` `__name__ == ``"__main__"``:`
`zabbix = ReportForm()`
`zabbix.getInfo()`
`zabbix.writeToXls2()`
`zabbix.Send_Email()`
If you are still confused in the world of programming, you can join our Python learning button qun: 784758214 to see how our predecessors learned! Exchange experience! I am a senior Python development engineer, from basic Python scripts to web development, crawler, django, data mining, etc. To every little friend of Python! Share some learning methods and small details that need attention. Click to join us. python learner gathering place