python3获取zabbix机器利用率，以及每周报警级别的统计

python3获取zabbix机器利用率，以及每周报警级别的统计

背景 ：在公司机器都放在监控当中，分不同的业务线，每个业务线的机器资源利用率也不一样，但是从运维角度出发，我们要资源利用率最大化，减少资源成本，但是每个业务线去找人比较麻烦，我们可以通过zabbix去获取所有机器的信息，这边可以分析，吧机器资源整合。

脚本在链接：链接：提取码：53er

“”“#!/usr/local/python/bin/python3.5import pymysqlimport time, datetimeimport smtplibimport osfrom email.mime.text import MIMETextfrom email.header import Header

zabbix数据库信息

zdbhost = '10.20.2.21'zdbuser = 'zabbix'zdbpass = 'zabbix'zdbport = 3306zdbname = 'zabbix'

d = datetime.datetime.now()day = datetime.date.today()keys = {'trends_uint': ['net.if.in[eth0]','net.if.out[eth0]','vfs.fs.size[/,used]','vm.memory.size[available]',],'trends': ['system.cpu.load[percpu,avg5]','system.cpu.util[,idle]',],}class ReportForm:

def __init__(self): self.conn = pymysql.connect(host=zdbhost, user=zdbuser, passwd=zdbpass, port=zdbport, db=zdbname) self.cursor = self.conn.cursor() self.groupname = 'K8s机器监控' #这里分组一定要修改不该查不到 self.IpInfoList = self.__getHostList() # return self.IpInfoList def __getHostList(self): sql = '''select groupid from groups where name = '%s' ''' % self.groupname self.cursor.execute(sql) groupid = self.cursor.fetchone()[0] print(groupid) sql = '''select hostid from hosts_groups where groupid = %s''' % groupid self.cursor.execute(sql) hostlist = self.cursor.fetchall() IpInfoList = {} for i in hostlist: hostid = i[0] sql = '''select host from hosts where status = 0 and hostid = %s''' % hostid ret = self.cursor.execute(sql) if ret: IpInfoList[self.cursor.fetchone()[0]] = {'hostid': hostid} return IpInfoList def __getItemid(self, hostid, itemname): sql = '''select itemid from items where hostid = %s and key_ = '%s' ''' % (hostid, itemname) if self.cursor.execute(sql): itemid = self.cursor.fetchone()[0] else: itemid = None return itemid def getTrendsValue(self, itemid, start_time, stop_time): resultlist = {} for type in ['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()[0] if result == None: result = 0 resultlist[type] = result return resultlist def getTrends_uintValue(self, itemid, start_time, stop_time): resultlist = {} for type in ['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()[0] if result: resultlist[type] = int(result) else: resultlist[type] = 0 return resultlist def get_week(self, d): dayscount = datetime.timedelta(days=d.isoweekday()) dayto = d - dayscount sixdays = datetime.timedelta(days=6) dayfrom = dayto - sixdays date_from = datetime.datetime(dayfrom.year, dayfrom.month, dayfrom.day, 0, 0, 0) date_to = datetime.datetime(dayto.year, dayto.month, dayto.day, 23, 59, 59) ts_first = int(time.mktime(datetime.datetime(dayfrom.year, dayfrom.month, dayfrom.day, 0, 0, 0).timetuple())) ts_last = int(time.mktime(datetime.datetime(dayto.year, dayto.month, dayto.day, 23, 59, 59).timetuple())) return ts_first, ts_last def getLastMonthData(self, hostid, table, itemname): ts_first = self.get_week(d)[0] ts_last = self.get_week(d)[1] itemid = self.__getItemid(hostid, itemname) # function = getattr(self, 'get %s Value' % table.capitalize()) function = getattr(self, 'get%sValue' % table.capitalize()) return function(itemid, ts_first, ts_last) def getinfo(self): for ip, resultdict in zabbix.IpInfoList.items(): print("正在查询 IP:%-15s hostid:%5d 的信息！" % (ip, resultdict['hostid'])) for table, keylists in keys.items(): for key in keylists: print("\t正在统计 key_:%s" % key) data = zabbix.getLastMonthData(resultdict['hostid'], table, key) zabbix.IpInfoList[ip][key] = data def writeToXls(self): dayscount = datetime.timedelta(days=d.isoweekday()) dayto = d - dayscount sixdays = datetime.timedelta(days=6) dayfrom = dayto - sixdays date_from = datetime.date(dayfrom.year, dayfrom.month, dayfrom.day) date_to = datetime.date(dayto.year, dayto.month, dayto.day) '''生成xls文件''' try: import xlsxwriter # 创建文件 workbook = xlsxwriter.Workbook('/usr/monitor/week/%s_%s巡检报告.xlsx' % (date_from, date_to)) # 创建工作薄 worksheet = workbook.add_worksheet() # 写入标题（第一行） i = 0 for value in ["主机", "CPU平均空闲值", "CPU最小空闲值", "可用平均内存(单位M)", "可用最小内存(单位M)", "CPU5分钟负载", "进入最大流量（单位Kbps）", "进入平均流量（单位Kbps）", "出去最大流量（单位Kbps）", "出去平均流量（单位Kbps）"]: worksheet.write(0, i, value) i = i + 1 # 写入内容： j = 1 for ip, value in self.IpInfoList.items(): worksheet.write(j, 0, ip) worksheet.write(j, 1, '%.2f' % value['system.cpu.util[,idle]']['avg']) worksheet.write(j, 2, '%.2f' % value['system.cpu.util[,idle]']['min']) worksheet.write(j, 3, '%dM' % int(value['vm.memory.size[available]']['avg'] / 1024 / 1024)) worksheet.write(j, 4, '%dM' % int(value['vm.memory.size[available]']['min'] / 1024 / 1024)) worksheet.write(j, 5, '%.2f' % value['system.cpu.load[percpu,avg5]']['avg']) worksheet.write(j, 6, value['net.if.in[eth0]']['max'] / 1000) worksheet.write(j, 7, value['net.if.in[eth0]']['avg'] / 1000) worksheet.write(j, 8, value['net.if.out[eth0]']['max'] / 1000) worksheet.write(j, 9, value['net.if.out[eth0]']['avg'] / 1000) j = j + 1 workbook.close() except Exception as e: print(e) def __del__(self): '''关闭数据库连接''' self.cursor.close() self.conn.close() def sendmail(self): sender = 'lxftty@163.com' receiver = ['lxftty@163.com', '602497030@qq.com'] subject = '上周巡检报告' smtpserver = 'smtp.163.com' username = 'lxftty@163.com' password = '123asdPP' msg = MIMEText('上周巡检报告已生成，请检查。\n报告目录:/usr/monitor\n由于邮件无法发送excel文档，请使用ftp登录' '查看。\nftp地址:xxx:端口\nftp用户名：xxx\n', 'plain', 'utf-8') # 中文需参数‘utf-8'，单字节字符不需要 msg['Subject'] = Header(subject, 'utf-8') msg['From'] = 'lxftty@163.com' msg['To'] = "602497030@qq.com" smtp = smtplib.SMTP() smtp.connect('smtp.163.com') smtp.login(username, password) smtp.sendmail(sender, receiver, msg.as_string()) print('发送成功！') smtp.quit()

if name == "main":zabbix = ReportForm()zabbix.getinfo()zabbix.writeToXls()zabbix.sendmail()[root@zabbix-server ~]# vim liyonglv.py [root@zabbix-server ~]# cat liyonglv.py #!/usr/local/python/bin/python3.5import pymysqlimport time, datetimeimport smtplibimport osfrom email.mime.text import MIMETextfrom email.header import Header

zabbix数据库信息

zdbhost = '10.20.2.21'zdbuser = 'zabbix'zdbpass = 'zabbix'zdbport = 3306zdbname = 'zabbix'

d = datetime.datetime.now()day = datetime.date.today()keys = {'trends_uint': ['net.if.in[eth0]','net.if.out[eth0]','vfs.fs.size[/,used]','vm.memory.size[available]',],'trends': ['system.cpu.load[percpu,avg5]','system.cpu.util[,idle]',],}class ReportForm:

def __init__(self): self.conn = pymysql.connect(host=zdbhost, user=zdbuser, passwd=zdbpass, port=zdbport, db=zdbname) self.cursor = self.conn.cursor() self.groupname = 'K8s机器监控' #这里分组一定要修改不该查不到 self.IpInfoList = self.__getHostList() # return self.IpInfoList def __getHostList(self): sql = '''select groupid from groups where name = '%s' ''' % self.groupname self.cursor.execute(sql) groupid = self.cursor.fetchone()[0] print(groupid) sql = '''select hostid from hosts_groups where groupid = %s''' % groupid self.cursor.execute(sql) hostlist = self.cursor.fetchall() IpInfoList = {} for i in hostlist: hostid = i[0] sql = '''select host from hosts where status = 0 and hostid = %s''' % hostid ret = self.cursor.execute(sql) if ret: IpInfoList[self.cursor.fetchone()[0]] = {'hostid': hostid} return IpInfoList def __getItemid(self, hostid, itemname): sql = '''select itemid from items where hostid = %s and key_ = '%s' ''' % (hostid, itemname) if self.cursor.execute(sql): itemid = self.cursor.fetchone()[0] else: itemid = None return itemid def getTrendsValue(self, itemid, start_time, stop_time): resultlist = {} for type in ['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()[0] if result == None: result = 0 resultlist[type] = result return resultlist def getTrends_uintValue(self, itemid, start_time, stop_time): resultlist = {} for type in ['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()[0] if result: resultlist[type] = int(result) else: resultlist[type] = 0 return resultlist def get_week(self, d): dayscount = datetime.timedelta(days=d.isoweekday()) dayto = d - dayscount sixdays = datetime.timedelta(days=6) dayfrom = dayto - sixdays date_from = datetime.datetime(dayfrom.year, dayfrom.month, dayfrom.day, 0, 0, 0) date_to = datetime.datetime(dayto.year, dayto.month, dayto.day, 23, 59, 59) ts_first = int(time.mktime(datetime.datetime(dayfrom.year, dayfrom.month, dayfrom.day, 0, 0, 0).timetuple())) ts_last = int(time.mktime(datetime.datetime(dayto.year, dayto.month, dayto.day, 23, 59, 59).timetuple())) return ts_first, ts_last def getLastMonthData(self, hostid, table, itemname): ts_first = self.get_week(d)[0] ts_last = self.get_week(d)[1] itemid = self.__getItemid(hostid, itemname) # function = getattr(self, 'get %s Value' % table.capitalize()) function = getattr(self, 'get%sValue' % table.capitalize()) return function(itemid, ts_first, ts_last) def getinfo(self): for ip, resultdict in zabbix.IpInfoList.items(): print("正在查询 IP:%-15s hostid:%5d 的信息！" % (ip, resultdict['hostid'])) for table, keylists in keys.items(): for key in keylists: print("\t正在统计 key_:%s" % key) data = zabbix.getLastMonthData(resultdict['hostid'], table, key) zabbix.IpInfoList[ip][key] = data def writeToXls(self): dayscount = datetime.timedelta(days=d.isoweekday()) dayto = d - dayscount sixdays = datetime.timedelta(days=6) dayfrom = dayto - sixdays date_from = datetime.date(dayfrom.year, dayfrom.month, dayfrom.day) date_to = datetime.date(dayto.year, dayto.month, dayto.day) '''生成xls文件''' try: import xlsxwriter # 创建文件 workbook = xlsxwriter.Workbook('/usr/monitor/week/%s_%s巡检报告.xlsx' % (date_from, date_to)) # 创建工作薄 worksheet = workbook.add_worksheet() # 写入标题（第一行） i = 0 for value in ["主机", "CPU平均空闲值", "CPU最小空闲值", "可用平均内存(单位M)", "可用最小内存(单位M)", "CPU5分钟负载", "进入最大流量（单位Kbps）", "进入平均流量（单位Kbps）", "出去最大流量（单位Kbps）", "出去平均流量（单位Kbps）"]: worksheet.write(0, i, value) i = i + 1 # 写入内容： j = 1 for ip, value in self.IpInfoList.items(): worksheet.write(j, 0, ip) worksheet.write(j, 1, '%.2f' % value['system.cpu.util[,idle]']['avg']) worksheet.write(j, 2, '%.2f' % value['system.cpu.util[,idle]']['min']) worksheet.write(j, 3, '%dM' % int(value['vm.memory.size[available]']['avg'] / 1024 / 1024)) worksheet.write(j, 4, '%dM' % int(value['vm.memory.size[available]']['min'] / 1024 / 1024)) worksheet.write(j, 5, '%.2f' % value['system.cpu.load[percpu,avg5]']['avg']) worksheet.write(j, 6, value['net.if.in[eth0]']['max'] / 1000) worksheet.write(j, 7, value['net.if.in[eth0]']['avg'] / 1000) worksheet.write(j, 8, value['net.if.out[eth0]']['max'] / 1000) worksheet.write(j, 9, value['net.if.out[eth0]']['avg'] / 1000) j = j + 1 workbook.close() except Exception as e: print(e) def __del__(self): '''关闭数据库连接''' self.cursor.close() self.conn.close() def sendmail(self): sender = 'lxftty@163.com' receiver = ['lxftty@163.com', '602497030@qq.com'] subject = '上周巡检报告' smtpserver = 'smtp.163.com' username = 'lxftty@163.com' password = 'asdPP' msg = MIMEText('上周巡检报告已生成，请检查。\n报告目录:/usr/monitor\n由于邮件无法发送excel文档，请使用ftp登录' '查看。\n机器地址:10.20.2.21:端口\n用户名：xxx\n', 'plain', 'utf-8') # 中文需参数‘utf-8'，单字节字符不需要 msg['Subject'] = Header(subject, 'utf-8') msg['From'] = 'lxftty@163.com' msg['To'] = "602497030@qq.com" smtp = smtplib.SMTP() smtp.connect('smtp.163.com') smtp.login(username, password) smtp.sendmail(sender, receiver, msg.as_string()) print('发送成功！') smtp.quit()

if name == "main":zabbix = ReportForm()zabbix.getinfo()zabbix.writeToXls()zabbix.sendmail()

”“”

==============================================接下来开始对每周报警的级别统计

链接：提取码：1ptl

"""

encoding:UTF-8

import xlsxwriterimport datetimeimport pymysqlimport numpy as npimport pandas

author = 'jmmei'data = '2020/07/22'

averagesql = """select from_unixtime(clock,'%Y-%m-%d') as time,count(DISTINCT eventid) as idfrom alertsWHERE mediatypeid = 9 #这里需要修改，去数据库对应上AND message LIKE '%PROBLEM%'AND message LIKE '%Average%'AND SUBJECT LIKE '%PROBLEM%'AND clock BETWEEN UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY)) AND UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))group by timeorder by time asc"""

highsql = """select from_unixtime(clock,'%Y-%m-%d') as time,count(DISTINCT eventid) as idfrom alertsWHERE mediatypeid = 9AND message LIKE '%PROBLEM%'AND message LIKE '%High%'AND SUBJECT LIKE '%PROBLEM%'AND clock BETWEEN UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY)) AND UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))group by timeorder by time asc"""

warningsql = """select from_unixtime(clock,'%Y-%m-%d') as time,count(DISTINCT eventid) as idfrom alertsWHERE mediatypeid = 9AND message LIKE '%PROBLEM%'AND message LIKE '%Warning%'AND SUBJECT LIKE '%PROBLEM%'AND clock BETWEEN UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY)) AND UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))group by timeorder by time asc"""

informationsql = """select from_unixtime(clock,'%Y-%m-%d') as time,count(DISTINCT eventid) as idfrom alertsWHERE mediatypeid = 9AND message LIKE '%PROBLEM%'AND message LIKE '%Information%'AND SUBJECT LIKE '%PROBLEM%'AND clock BETWEEN UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY)) AND UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))group by timeorder by time asc"""

disastersql = """select from_unixtime(clock,'%Y-%m-%d') as time,count(DISTINCT eventid) as idfrom alertsWHERE mediatypeid = 9AND message LIKE '%PROBLEM%'AND message LIKE '%Diasater%'AND SUBJECT LIKE '%PROBLEM%'AND clock BETWEEN UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY)) AND UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))group by timeorder by time asc"""

#连接MySQL数据库def get_count(sql):'''#zabbix数据库信息：zdbhost = '10.20.2.21'zdbuser = 'zabbix'zdbpass = 'zabbix'zdbport = 3306zdbname = 'zabbix''''conn=pymysql.connect("10.20.2.21", "zabbix", "zabbix", "zabbix", charset='utf8')cursor = conn.cursor()#print("===============cursor:",cursor,type(cursor))cursor.execute(sql)count = cursor.fetchall()

将rows转化为数组

#print("=================count:",count) rows = np.array(count) #print("============rows:",rows) conn.close() return count

def coloum(data, weekendtime):#创建一个excel文件workbook = xlsxwriter.Workbook(weekendtime +".xlsx")#创建一个工作表，默认sheet1worksheet = workbook.add_worksheet()bold = workbook.add_format({'bold': 1})#表头

title = ['告警级别', '星期一','星期二','星期三','星期四','星期五','星期六','星期日']

#列名buname = ['information', 'warning','average', 'high', 'disaster']

定义数据formatter格式对象，设置边框加粗1像素

formatter = workbook.add_format() formatter.set_border(2)

#定义格式：# 定义标题栏格式对象：边框加粗1像素，背景色为灰色，单元格内容居中、加粗,标题字体颜色title_formatter = workbook.add_format()title_formatter.set_border(2)title_formatter.set_bg_color('#4682B4')title_formatter.set_align('center')title_formatter.set_bold()title_formatter.set_color("#F8F8FF")title_formatter.set_font_size(14)chart_col = workbook.add_chart({'type': 'column'})def chart_series(row):chart_col.add_series({'categories': '=Sheet1!$B$1:$H$1','values': '=Sheet1!$B${}:$H${}'.format(row, row),'line': {'color': 'black'},

'name': '=Sheet1!$A${}'.format(row)

'name': '=Sheet1!$A$' + row } )

下面分别以行和列的方式将标题栏、业务名称、流量数据写入单元格，并引用不同的格式对象

worksheet.write_row('A1',title,title_formatter) worksheet.write_column('A2',buname,formatter) for i in range (2,7): worksheet.write_row('B{}'.format(i),data[i-2],formatter) print (i) chart_series(str(i)) # 设置图表的title 和 x，y轴信息 chart_col.set_title({'name': '告警统计/周'}) chart_col.set_x_axis({'name': '告警级别'}) chart_col.set_y_axis({'name': '告警次数'}) # 设置图表的风格 #chart_col.set_style(9) # 把图表插入到worksheet以及偏移 worksheet.insert_chart('A10', chart_col, {'x_offset': 25, 'y_offset': 10}) workbook.close()

#判断二维元组是否为空，长度是否满足要求，不满足则补0.#输入为一个字典，判断是否为空，空则添加数据def covertdata(jsondata,weektime):

listkey = list(jsondata.keys()) for i in weektime: j = i.strftime("%Y-%m-%d") if listkey: if j not in listkey: jsondata[j] = "0" else: jsondata[j] = "0" #print ("jsondata:>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>",jsondata)

#按照时间对字典进行排序sort = sorted(jsondata.items(), key=lambda d: d[0])#将第二列取出来并转为列表array = np.array(sort)array2 = array[:, 1]list2 = array2.tolist()list3 = list(map(lambda x: float(x), list2))#print("list2:>>>>>>>>>>>>>>>", list2)#print("list3:>>>>>>>>>>>>>>>", list3)return list3

if name == 'main':yesterday = (datetime.date.today() + datetime.timedelta(days=-1)).strftime("%Y-%m-%d")print(yesterday)weeklist = pandas.date_range(end=yesterday, periods=7)

informationdata = get_count(informationsql) informationlist = covertdata(dict(informationdata),weeklist) warningdata = get_count(warningsql) warninglist = covertdata(dict(warningdata),weeklist) averagedata = get_count(averagesql) averagelist = covertdata(dict(averagedata),weeklist) highdata = get_count(highsql) highlist = covertdata(dict(highdata),weeklist) disasterdata = get_count(disastersql) disasterlist = covertdata(dict(disasterdata),weeklist) print (informationlist) print (warninglist) print (averagelist) print (highlist) print (disasterlist) data1 = [informationlist, warninglist, averagelist, highlist, disasterlist] coloum(data1,yesterday)

"""

看看执行结果

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。