zabbix企业应用之定时获取监控数据做报表 _ZABBIX

【zabbix企业应用之定时获取监控数据做报表】幽映每白日，清辉照衣裳。这篇文章主要讲述zabbix企业应用之定时获取监控数据做报表相关的知识，希望能为你提供帮助。
最近某项目突然提出一个新需求，需要每周五14点，获取他们监控项目每天20-24点监控平均数据，以小时为单位的，输出文件是excel的，要求以每天为单位单独一个sheet，汇总邮件转给业务。
他们主要是做业务使用量报表，每周周报使用，虽然需求困难，但作为运维也得解决，下面是邮件的效果图。

文章图片

可以看到邮件标题是带有项目名称与时间，收集人是业务与我。
下面是excel的格式

文章图片

每天一个sheet，获取这些项目自己每天20-24点的监控平均数据，以小时为单位。
主要是使用sql查看上面的监控数据，并通过python把数据汇总到excel里并使用crontab定时发送。
重要：我这里对默认的linux监控模板，添加了一个监控主机cpu数量的item，key名称是system.cpu.num，请大家也加入到模板里，否则导出的excel里是空的。

文章图片

下面是脚本内容，大家改改自己需要获取项目组与发送邮箱信息就行（我是使用163邮箱）

#!/usr/bin/env python #coding=utf-8 #Author: Denglei #Email: dl528888@gmail.com #QQ: 244979152 import mysqldb import datetime import xlwt import sys from email.mime.multipart import MIMEMultipart from email.mime.base import MIMEBase from email.mime.text import MIMETextfrom email.utils import COMMASPACE,formatdate from email import encodersimport osdef send_mail(server, fro, to, subject, text, files=[]): assert type(server) == dict assert type(to) == list assert type(files) == list msg = MIMEMultipart() msg[\'From\'] = fro msg[\'Subject\'] = subject msg[\'To\'] = COMMASPACE.join(to) #COMMASPACE==\', \' msg[\'Date\'] = formatdate(localtime=True) msg.attach(MIMEText(text)) for f in files: part = MIMEBase(\'application\', \'octet-stream\') #\'octet-stream\': binary data part.set_payload(open(f, \'rb\').read()) encoders.encode_base64(part) part.add_header(\'Content-Disposition\', \'p_w_upload; filename="%s"\' % os.path.basename(f)) msg.attach(part) import smtplib smtp = smtplib.SMTP(server[\'name\'], server[\'port\']) smtp.ehlo() smtp.starttls() smtp.ehlo() smtp.login(server[\'user\'], server[\'passwd\']) smtp.sendmail(fro, to, msg.as_string()) smtp.close()def get_mysql_data(sql): cur.execute(sql) results=cur.fetchall() return results def cover_excel(msg,start_time): #wb = xlwt.Workbook() ws = wb.add_sheet(start_time,cell_overwrite_ok=True) count=len(msg) x=msg title=[\'时间\'.encode(\'utf8\'),\'所属组\'.encode(\'utf8\'),\'主机IP\'.encode(\'utf8\'),\'CPU逻辑核数(单位:个)\'.encode(\'utf8\'),\'CPU空闲值(单位:%)\'.encode(\'utf8\'),\'可用内存值(单位:GB)\'.encode(\'utf8\'),\'总内存值(单位:GB)\'.encode(\'utf8\'),\'公网进入流量(单位:kbps)\'.encode(\'utf8\'),\'公网流出流量(单位:kbps)\'.encode(\'utf8\')] x.insert(0,title) for j in range(0,9): for i in range(0,count): if i == 0: #ws.write(i,j,title[j].decode(\'utf8\')) value=https://www.songbingjia.com/android/x[0] else: value=x[i] if isinstance(value[j],long) or isinstance(value[j],int) or isinstance(value[j],float): ws.write(i,j,value[j]) else: ws.write(i,j,value[j].decode(/'utf8\')) #wb.save(\'/tmp/zabbix_log/chance_zabbix_monitor_test.xls\') def run_select(start_time,end_time): get_cpu_idle_sql="select from_unixtime(hi.clock,\'%%Y-%%m-%%d %%T\') as Date,g.name as Group_Name,h.host as Host,round(avg(hi.value_avg),2) as Cpu_Idle from hosts_groups hg join groups g on g.groupid = hg.groupid join items i on hg.hostid = i.hostid join hosts h on h.hostid=i.hostid join trends hi on i.itemid = hi.itemid where i.key_=\'system.cpu.util[,idle]\' and hi.clock > = UNIX_TIMESTAMP(\'%s 20:00:00\') and hi.clock < UNIX_TIMESTAMP(\'%s 00:00:00\') and g.name like \'%%广告%%\' group by h.host; "%(start_time,end_time) cpu_idle_result=get_mysql_data(get_cpu_idle_sql) get_cpu_num_sql="select from_unixtime(hi.clock,\'%%Y-%%m-%%d %%T\') as Date,g.name as Group_Name,h.host as Host,avg(hi.value_avg) as Cpu_Number from hosts_groups hg join groups g on g.groupid = hg.groupid join items i on hg.hostid = i.hostid join hosts h on h.hostid=i.hostid join trends_uint hi on i.itemid = hi.itemid where i.key_=\'system.cpu.num\' and hi.clock > = UNIX_TIMESTAMP(\'%s 20:00:00\') and hi.clock < UNIX_TIMESTAMP(\'%s 00:00:00\') and g.name like \'%%广告%%\' group by h.host; "%(start_time,end_time) cpu_num_result=get_mysql_data(get_cpu_num_sql) get_mem_avai_sql="select from_unixtime(hi.clock,\'%%Y-%%m-%%d %%T\') as Date,g.name as Group_Name,h.host as Host,round(avg(hi.value_avg/1024/1024/1024),2) as Memory_Avaiable from hosts_groups hg join groups g on g.groupid = hg.groupid join items i on hg.hostid = i.hostid join hosts h on h.hostid=i.hostid join trends_uint hi on i.itemid = hi.itemid where i.key_=\'vm.memory.size[available]\' and hi.clock > = UNIX_TIMESTAMP(\'%s 20:00:00\') and hi.clock < UNIX_TIMESTAMP(\'%s 00:00:00\') and g.name like \'%%广告%%\' group by h.host; "%(start_time,end_time) mem_avai_result=get_mysql_data(get_mem_avai_sql) #get_mem_free_sql="select from_unixtime(hi.clock,\'%%Y-%%m-%%d %%T\') as Date,g.name as Group_Name,h.host as Host,hi.value_avg/1024/1024/1024 as Memory_Avaiable from hosts_groups hg join groups g on g.groupid = hg.groupid join items i on hg.hostid = i.hostid join hosts h on h.hostid=i.hostid join trends_uint hi on i.itemid = hi.itemid where i.key_=\'vm.memory.size[free]\' and hi.clock > = UNIX_TIMESTAMP(\'%s\') and hi.clock < UNIX_TIMESTAMP(\'%s\') and g.name like \'%%广告%%\'; "%(start_time,end_time) #mem_free_result=get_mysql_data(get_mem_free_sql) get_mem_total_sql="select from_unixtime(hi.clock,\'%%Y-%%m-%%d %%T\') as Date,g.name as Group_Name,h.host as Host,round(avg(hi.value_avg/1024/1024/1024),2) as Memory_Total from hosts_groups hg join groups g on g.groupid = hg.groupid join items i on hg.hostid = i.hostid join hosts h on h.hostid=i.hostid join trends_uint hi on i.itemid = hi.itemid where i.key_=\'vm.memory.size[total]\' and hi.clock > = UNIX_TIMESTAMP(\'%s 20:00:00\') and hi.clock < UNIX_TIMESTAMP(\'%s 00:00:00\') and g.name like \'%%广告%%\' group by h.host; "%(start_time,end_time) mem_total_result=get_mysql_data(get_mem_total_sql) get_em2_in_sql="select from_unixtime(hi.clock,\'%%Y-%%m-%%d %%T\') as Date,g.name as Group_Name,h.host as Host,round(avg(hi.value_avg/1000),2) as Network_Eth0_In from hosts_groups hg join groups g on g.groupid = hg.groupid join items i on hg.hostid = i.hostid join hosts h on h.hostid=i.hostid join trends_uint hi on i.itemid = hi.itemid where i.key_=\'net.if.in[em2]\' and hi.clock > = UNIX_TIMESTAMP(\'%s 20:00:00\') and hi.clock < UNIX_TIMESTAMP(\'%s 00:00:00\') and g.name like \'%%广告%%\' group by h.host; "%(start_time,end_time) em2_in_result=get_mysql_data(get_em2_in_sql) get_em2_out_sql="select from_unixtime(hi.clock,\'%%Y-%%m-%%d %%T\') as Date,g.name as Group_Name,h.host as Host,round(avg(hi.value_avg/1000),2) as Network_Eth0_In from hosts_groups hg join groups g on g.groupid = hg.groupid join items i on hg.hostid = i.hostid join hosts h on h.hostid=i.hostid join trends_uint hi on i.itemid = hi.itemid where i.key_=\'net.if.out[em2]\' and hi.clock > = UNIX_TIMESTAMP(\'%s 20:00:00\') and hi.clock < UNIX_TIMESTAMP(\'%s 00:00:00\') and g.name like \'%%广告%%\' group by h.host; "%(start_time,end_time) em2_out_result=get_mysql_data(get_em2_out_sql) msg=[list(i) for i in cpu_num_result] for i in msg: for ii in cpu_idle_result: if i[0] ==ii[0] and i[1] == ii[1] and i[2] == ii[2]: i[3]=int(i[3]) #msg.append([i[0],i[1],i[2],int(i[3]),ii[3]]) i.append(int(ii[3])) for iii in mem_avai_result: if i[0] ==iii[0] and i[1] == iii[1] and i[2] == iii[2]: i.append(round(float(iii[3]),2)) for iiii in mem_total_result: if i[0] ==iiii[0] and i[1] == iiii[1] and i[2] == iiii[2]: i.append(int(iiii[3])) for a in em2_in_result: if i[0] == a[0] and i[1] == a[1] and i[2] == a[2]: i.append(int(a[3])) if len(i) == 7: i.append(0) for b in em2_out_result: if i[0] == b[0] and i[1] == b[1] and i[2] == b[2]: i.append(int(b[3])) if len(i) == 8: i.append(0) cover_excel(msg,start_time) def main(): for i in range(7,0,-1): start_time=((datetime.datetime.now() - datetime.timedelta(days = i))).strftime("%Y-%m-%d") end_time=((datetime.datetime.now() - datetime.timedelta(days = i-1))).strftime("%Y-%m-%d") run_select(start_time,end_time) if __name__ == "__main__": default_encoding = \'utf-8\' if sys.getdefaultencoding() != default_encoding: reload(sys) sys.setdefaultencoding(default_encoding) if os.path.exists("/tmp/zabbix_log/") is False: os.mkdir("/tmp/zabbix_log/") conn=MySQLdb.connect(host=\'10.10.14.19\',user=\'zabbix\',passwd=\'zabbix\',port=3306,charset="utf8") cur=conn.cursor() conn.select_db(\'zabbix\') wb = xlwt.Workbook() main() wb.save(\'/tmp/zabbix_log/chance_zabbix_monitor_hour_avg.xls\') cur.close() conn.close() #follow is send mail server = {\'name\':\'smtp.163.com\', \'user\':\'ops_monitor\', \'passwd\':\'xxxx\', \'port\':25} fro = \'xxx@163.com\' to = [\'xx@xx.com\',\'244979152@qq.com\'] now_time=((datetime.datetime.now() - datetime.timedelta(days = 1))).strftime("%Y/%m/%d") last_time=((datetime.datetime.now() - datetime.timedelta(days = 7))).strftime("%Y/%m/%d") subject = \'xx平台监控数据【%s-%s】\'%(last_time,now_time) text = \'xx你好,附件是畅思平台最近一周每天20至24点平均值监控数据，请查收!\\n有问题请联系邓磊.\' files = [\'/tmp/zabbix_log/chance_zabbix_monitor_hour_avg.xls\'] send_mail(server, fro, to, subject, text, files=files)

想修改获取监控组的话，就把上面%%广告%%里广告改为你要求的组就行，其他的自动修改。
脚本我放入github里（博客里脚本格式难调整，大家直接去github吧），地址是https://github.com/dl528888/public_script/blob/master/zabbix_hour_avg_monitor.py
想定时发送就把这个脚本放入crontab里，设置好时间允许即可。
下面是我的crontab