根据zabbix触发器报警内容整理excel-CSDN博客

公司板卡经常ping不同，想到zabbix会报警，于是去数据库取出相关报警内容，
查询zabbix数据库
select from_unixtime(clock) as DateTime,subject from alerts WHERE sendto = 'xiaofeng@sunspeedy.com' and subject like '%ping down%' \G;

每个子元组里面的格式是前面是时间(teTime):，后面是ubject的内容

import xlsxwriter
import os
import smtplib
import datetime
from email.mime.text import MIMEText
from email.header import Header
from email.mime.multipart import MIMEMultipart
now_time = datetime.datetime.now()
now = now_time.strftime('%Y%m%d')
yes_time = now_time + datetime.timedelta(days=-7)
yes_time_nyr1 = yes_time.strftime('%Y-%m-%d')
now_time_nyr1 = now_time.strftime('%Y-%m-%d')
fname = "%sbanka.xlsx" % now
def SendMail():
    '''    mail_host = "smtp.exmail.qq.com"  # 设置服务器
    mail_user = "zhouqianhong@sunspeedy.com"  # 用户名
    mail_pass = "xxxx"  # 口令
    sender = 'zhouqianhong@sunspeedy.com'
    '''
    # 第三方 SMTP 服务
    mail_host = "smtp.exmail.qq.com"  # 设置服务器
    mail_user = "sy_noc@sunspeedy.com"  # 用户名
    mail_pass = "xxx"  # 口令
    sender = 'sy_noc@sunspeedy.com'
    receivers = ['970171262@qq.com']  # 接收邮件，可设置为你的QQ邮箱或者其他邮箱

    # 创建一个带附件的实例
    message = MIMEMultipart()
    message['From'] = Header("板卡掉线情况", 'utf-8')
    message['To'] = Header("赛云数据", 'utf-8')
    subject = '板卡掉线周报'
    message['Subject'] = Header(subject, 'utf-8')

    # 邮件正文内容
    message.attach(MIMEText('板卡情况', 'plain', 'utf-8'))

    # 构造附件1，传送当前目录下的 test.txt 文件
    att1 = MIMEText(open(fname, 'rb').read(), 'base64', 'utf-8')
    att1["Content-Type"] = 'application/octet-stream'
    # 这里的filename可以任意写，写什么名字，邮件中显示什么名字
    att1["Content-Disposition"] = 'attachment; filename="banka.xlsx"'
    message.attach(att1)
    try:
        smtpObj = smtplib.SMTP()
        smtpObj.connect(mail_host, 25)  # 25 为 SMTP 端口号
        smtpObj.login(mail_user, mail_pass)
        smtpObj.sendmail(sender, receivers, message.as_string())
        print("邮件发送成功")
    except smtplib.SMTPException:
        print("Error: 无法发送邮件")

import time,datetime
import pymysql
conn = pymysql.connect(host="localhost", user="zabbix", password="zabbix", database="zabbix", charset="utf8")
cursor = conn.cursor()
#sql = """select * from hosts limit 2"""
#sql = """select from_unixtime(clock) as DateTime,subject from alerts WHERE sendto = 'xiaofeng@sunspeedy.com' and subject like '%ping down%' limit 10"""
#sqll = """select clock,subject from alerts WHERE sendto = 'xiaofeng@sunspeedy.com' and subject like '%ping down%' limit 10"""
sqll = """select from_unixtime(clock)as DateTime,subject from alerts WHERE  subject like '%ping down%' and from_unixtime(clock)>=%s and from_unixtime(clock)<=%s limmit 10""" % (yes_time_nyr1, now_time_nyr1)
cursor.execute(sqll)
sql = cursor.fetchall()
cursor.close()
conn.close()
#print(sql)

#sql = ((1514274911, u'PROBLEM: Response ip ping down 10.10.2.15'), (1514274911, u'PROBLEM: Response ip ping down 10.10.2.17'), (1514274911, u'PROBLEM: Response ip ping down 10.10.2.19'), (1514274911, u'PROBLEM: Response ip ping down 10.10.2.20'), (1514281340, u'OK: Response ip ping down 10.10.2.17'), (1514284769, u'PROBLEM: Response ip ping down 10.10.2.18'), (1514289191, u'PROBLEM: Response ip ping down 10.13.2.84'), (1514289191, u'PROBLEM: Response ip ping down 10.10.2.17'), (1514310874, u'OK: Response ip ping down 10.13.2.84'), (1514336446, u'OK: Response ip ping down 10.10.2.17'))
num_list = [[] for i in range(len(sql))]
for i in range(len(sql)):
    for j in sql[i]:
        if isinstance(j, int):
            time_local = time.localtime(j)
            dt = time.strftime("%Y-%m-%d %H:%M:%S", time_local)
            num_list[i].append(dt)
        else:
            num_list[i].append(j)
#print(num_list)
# a = 1514274911
import time
timestamp = 1462451334
#转换成localtime
time_local = time.localtime(timestamp)
dt = time.strftime("%Y-%m-%d-%H:%M:%S",time_local)
#print(dt)   #转换成二维列表格式了，可以增删改了
r = [[] for i in range(len(sql))]
for p in range(len(sql)):
    for i in range(len(num_list) - 1, -1, -1):
        if num_list[i - 1][-1][-9:] == num_list[-1][-1][-9:]:
            r[p].append(num_list[i - 1])
            num_list.pop(i - 1)
        else:
            # print(num_list[i-1])
            pass
for z in range(len(r) - 1, -1, -1):
    if len(r[z]):
        pass  # this list is not None
    else:
        r.remove(r[z])
#print(r)   #转换成三维列表了，所有ip相同的列表放在一起
import re
pattern = re.compile(r"((?:(?:25[0-5]|2[0-4]\d|((1\d{2})|([1-9]?\d)))\.){3}(?:25[0-5]|2[0-4]\d|((1\d{2})|([1-9]?\d))))")
# y = "fjlsfjla10.10.10.10jkfsjf"
for m in range(len(r)):
    bbb = pattern.findall(r[m][0][1])
    ip = [g[0] for g in bbb]
    if len(ip):
        r[m] = ip + r[m]
    else:
        pass
#print(r)   #在相同ip列表的前面加上该相同ip
pat='ok'
newpat='1'
for i in r:
    for ii in i:
        if isinstance(ii, list):
            if ii[-1].count('ing') == 0:
                #print(i[-1].count('ing'))
                pass
            #re.sub(pat,newpat,i[-1])
            else:
                if ii[-1].find('OK') != -1:
                    ii[-1]= '通'
                else:
                    ii[-1]= '断'
        else:
            pass
#print('rrrrr', r)  # 输出格式可以操作了，写到excel[['10.10.2.17', ['2017-12-26 19:53:11', 0], ['2017-12-26 17:42:20', 1], ['2017-12-26 15:55:11', 0], ['2017-12-27 09:00:46', 1]], ['10.13.2.84', ['2017-12-26 19:53:11', 0], ['2017-12-27 01:54:34', 1]], ['10.10.2.18', ['2017-12-26 18:39:29', 0]], ['10.10.2.20', ['2017-12-26 15:55:11', 0]], ['10.10.2.19', ['2017-12-26 15:55:11', 0]], ['10.10.2.15', ['2017-12-26 15:55:11', 0]]]
# a = ['2017-12-26 19:53:11', '1']
# b = " ".join(a)
for i in range(len(r)):
    for j in range(len(r[i])):
        #print(j,type(j))
        if isinstance(r[i][j], list):
            r[i][j] = "-".join(r[i][j])
            #print('jjjj',r[i][j])
        else:
            pass
print(r)   #[['10.10.2.17', '2017-12-26 19:53:11-断', '2017-12-26 17:42:20-通', '2017-12-26 15:55:11-断', '2017-12-27 09:00:46-通'], ['10.13.2.84', '2017-12-26 19:53:11-断', '2017-12-27 01:54:34-通'], ['10.10.2.18', '2017-12-26 18:39:29-断'], ['10.10.2.20', '2017-12-26 15:55:11-断'], ['10.10.2.19', '2017-12-26 15:55:11-断'], ['10.10.2.15', '2017-12-26 15:55:11-断']]

def Excel():
    data = r
    workbook = xlsxwriter.Workbook(fname)
    worksheet = workbook.add_worksheet()
    chart = workbook.add_chart({'type': 'column'})
    # title = [u'OWNER', u'AW_NUMBER', u'AWNAME', u'AW_VERSION', u'PAGESPACE', u'GENEACTION', u'FROZEN']
    format = workbook.add_format()
    format.set_border(1)
    format_title = workbook.add_format()
    format_title.set_border(1)
    format_title.set_bg_color('#cccccc')
    format_title.set_align('center')
    format_title.set_bold()
    format_ave = workbook.add_format()
    format_ave.set_border(1)
    format_ave.set_num_format('0.00')
    # worksheet.write_row('A1', title, format_title)

    for i in range(len(data)):
        worksheet.write_row('A%s' % i+1, data[i], format)

    workbook.close()
Excel()

SendMail()