zabbix内存CPU使⽤率每周报表脚本python 环境：python3.7
需要pip安装环境：pymysql,xlsxwriter
#!/usr/bin/python
#encoding:utf-8
import pymysql
import time,datetime
import xlsxwriter
import smtplib
眼镜清洗剂import traceback
from email.mime.multipart import MIMEMultipart
from import MIMEText
from email.header import Header
zdbhost = '192.'
zdbuser = 'zabbix'
zdbpass = 'zabbix'
zdbport = 3306
虹膜识别芯片zdbname = 'zabbix'
#⽣成⽂件名称：
linuxfilename = 'Linux_Report_Cpu_Mem_%s.xlsx' % time.strftime("%Y-%m-%d", time.localtime())人工天竺黄
windowsfilename = 'Windows_Report_Cpu_Mem_%s.xlsx' % time.strftime("%Y-%m-%d", time.localtime())
#⽣成zabbix哪个分组报表
groupname = 'Linux'
groupname_2 = 'Windows'
#需要查询的key列表 [名称，表名，key值，取值，格式化，数据整除处理] keys = [
['CPU平均空闲值','trends','system.cpu.util[,idle]','avg','%.2f',1],
['CPU最⼩空闲值','trends','system.cpu.util[,idle]','min','%.2f',1],
['物理内存⼤⼩(单位G)','trends_uint','vm.memory.size[total]','avg','',1048576000],
['可⽤平均内存(单位G)','trends_uint','vm.memory.size[available]','avg','',1048576000],
['swap空闲率%','trends','system.swap.size[,pfree]','avg','%.2f',1],
]
keys_2=[
['windows CPU平均使⽤率','trends',r'perf_counter[\\Processor(_Total)\\% Processor Time]','avg','%.2f',1],
['windows CPU最⼤使⽤率','trends',r'perf_counter[\\Processor(_Total)\\% Processor Time]','max','%.2f',1],
['物理内存⼤⼩(单位G)','trends_uint','vm.memory.size[total]','avg','',1048576000],
['可⽤平均内存(单位G)','trends_uint','vm.memory.size[free]','avg','',1048576000],
['虚拟内存空闲率%','trends','vm.vmemory.size[pavailable]','avg','%.2f',1],
]
class ReportForm:
def __init__(self,groupname):
#打开数据库连接
< = t(host=zdbhost,user=zdbuser,passwd=zdbpass,port=zdbport,db=zdbname,cursorclass=pymysql.cursors.DictCursor) self.cursor = ursor()
#获取IP信息：
self.IpInfoList =HostList(groupname)
#根据zabbix组名获取该组所有IP
def getgroupid(self,groupname):
#查询组ID:
sql = '''select groupid from groups where name = '%s' ''' % groupname
ute(sql)
groupid =self.cursor.fetchone()['groupid']
return groupid
#根据groupid查询该分组下⾯的所有主机ID（hostid）：
def gethostid(self,groupname):
groupid =upid(groupname)
sql = '''select hostid from hosts_groups where groupid = %s''' % groupid
ute(sql)
hostlist = self.cursor.fetchall()
return hostlist
#⽣成IP信息字典：结构为{'119.146.207.19':{'hostid':10086L,},}
def getHostList(self,groupname):
hostlist =hostid(groupname)
IpInfoList = {}
for i in hostlist:
hostid = i['hostid']
sql = '''select host from hosts where status = 0 and available=1 and hostid = %s''' % hostid ret =ute(sql)焊接应力
if ret:
IpInfoList[self.cursor.fetchone()['host']] = {'hostid':hostid}
return IpInfoList
#获取itemid
def getItemid(self,hostid,itemname):
sql = '''select itemid from items where hostid = %s and key_ = '%s' ''' % (hostid, itemname) if ute(sql):
itemid =self.cursor.fetchone()['itemid']
else:
itemid = None
return itemid
#查询trends表的值,type的值为min,max,avg三种
def getTrendsValue(self,type,itemid, start_time, stop_time):
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)
ute(sql)
result =self.cursor.fetchone()['result']
if result == None:
result = 0
return result
#查询trends_uint表的值,type的值为min,max,avg三种
def getTrends_uintValue(self,type,itemid, start_time, stop_time):
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) ute(sql)
result =self.cursor.fetchone()['result']
if result:
result = int(result)
else:
result = 0
return result
#根据hostid,itemname获取该监控项的值
def getLastWeekData(self,type,hostid,table,itemname):
#获取上周的第⼀天和最后⼀天
self.fromdays=7+day().weekday()
ts_first =int(time.mktime((day()-datetime.timedelta(days=self.fromdays)).timetuple()))
ts_last =int(time.mktime((day()-datetime.timedelta(days)).timetuple()))
itemid =Itemid(hostid, itemname)
function =getattr(self,'get%sValue' % table.capitalize())
return function(type,itemid, ts_first, ts_last)
def getInfo(self):
#循环读取IP列表信息
for ip,resultdict in linux.IpInfoList.items():
print("正在查询 IP:%-15shostid:%5d 的信息！" % (ip, resultdict['hostid']))
#循环读取keys，逐个key统计数据：
#ip 192.168.10.106 resultdict{'hostid': 10134L}
for value in keys:
测量温度表#['CPU最⼩空闲值','trends','system.cpu.util[,idle]','min','%.2f',1]
#print ("\t正在统计key_:%s" % value[2])
if not value[2] in linux.IpInfoList[ip]:
linux.IpInfoList[ip][value[2]] = {}
data = LastWeekData(value[3],resultdict['hostid'],value[1],value[2])
linux.IpInfoList[ip][value[2]][value[3]] = data
#{'192.168.10.213':{'vfs.fs.size[/,total]': {'avg': 52844687360}, 'hostid': 10285L,'vm.memory.size[total]':la-5
{'avg': 33615073280}, 'system.swap.size[,total]':{'avg': def getInfo2(self):
#循环读取IP列表信息
for ip,resultdict in windows.IpInfoList.items():
print("正在查询 IP:%-15shostid:%5d 的信息！" % (ip, resultdict['hostid']))
#循环读取keys，逐个key统计数据：
#ip 192.168.10.106 resultdict{'hostid': 10134L}
for value in keys_2:
#['CPU最⼩空闲值','trends','system.cpu.util[,idle]','min','%.2f',1]
#print ("\t正在统计key_:%s" % value[2])
if not value[2] in windows.IpInfoList[ip]:
windows.IpInfoList[ip][value[2]] = {}
data1 = LastWeekData(value[3],resultdict['hostid'],value[1],value[2])
windows.IpInfoList[ip][value[2]][value[3]] = data1
#{'192.168.1.213':{'vfs.fs.size[/,total]': {'avg': 52844687360}, 'hostid': 10285L,'vm.memory.size[total]': {'avg': 33615073280}, 'system.swap.size[,total]':{'avg': 1
#⽣成xls⽂件
def writeToXls(self,xlsfilename,keysname):
#创建⽂件
workbook =xlsxwriter.Workbook(xlsfilename)
#⽣产时间区间
start_day=day()-datetime.timedelta(days=self.fromdays)
end_day=day()-datetime.timedelta(days)
#创建⼯作薄
worksheet =workbook.add_worksheet('%s⾄%s' % (start_day,end_day))
#设置列宽
worksheet.set_column('A:G', 18)
#创建单元格格式
format1 = workbook.add_format()
format1.set_fg_color('#003063')
format1.set_font_color('white')
format1.set_border(1)
format2 = workbook.add_format()
format2.set_border(1)
#写⼊第⼀列：
worksheet.write(0,0,"主机名称IP",format1)
i = 1
for ip in self.IpInfoList:
worksheet.write(i,0,ip,format2)
i = i + 1
#写⼊其他列（如果列数为5，则写内存百分⽐）：
i = 1
k = 1
for value in keysname:
if i == 5:
k=i+1
worksheet.write(0,i,"可⽤内存百分⽐%",format1)
worksheet.write(0,k,value[0],format1)
else:
k=i
worksheet.write(0,k,value[0],format1)
#写⼊该列内容（内存百分⽐数据由前两列数据相除获得）：
j = 1
for ip,result in self.IpInfoList.items():
if i==5:
worksheet.write(j,5, '=ROUND(E%s/D%s*100,2)'%(j+1, j+1),format2)
if value[4]:
worksheet.write(j,k, value[4] % result[value[2]][value[3]],format2)
else:
worksheet.write(j,k, "{:.2f}".format(result[value[2]][value[3]] / value[5]),format2) j = j + 1
i = i + 1
workbook.close()
#关闭数据库连接
def __del__(self):
self.cursor.close()
#发送邮件
def sendmail():
#设置smtplib所需的参数
smtpserver = 'mail.qq'
sender='123@qq'
#收件⼈为多个收件⼈，逗号分隔
to_mail=['123@qq']
cc_mail=['123@qq']
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议