最近某项目突然提出一个新需求，需要每周五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: 244979152import MySQLdbimport datetimeimport xlwtimport sysfrom email.mime.multipart import MIMEMultipartfrom email.mime.base import MIMEBasefrom email.mime.text import MIMETextfrom email.utils import COMMASPACE,formatdatefrom 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', '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 resultsdef 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