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 MIMEText

from email.utils import COMMASPACE,formatdate
from email import encoders

import os

def 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', 'attachment; 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=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

00 14 * * 5 /usr/bin/python /data/software/zabbix_hour_avg_monitor.py

BTW：我公司现在提供IT与运维方面技术外包，主要提供物理机租赁、云主机租赁与管理、一站式运维外包支持（包含程序部署与维护、主机维护与管理、数据库管理与维护、CDN管理与维护、监控管理与维护等）；

有需要运维外包可以登录官网

另外也可以加群沟通交流256326024