从mysql中获取数据发送html邮件_Zabbix监控告警每周邮件发送自动统计分析的报表...

说明：应部门领导要求，每周自动对zabbix监控告警进行统计分析，并有图表展示，将结果通过邮件的方式自动发出来。

思路：可以通过Zabbix的api获取值，然后通过Zabbix的Mysql数据库的alters表来获取具体值。

最终的结果展示：

直接上脚本：

注意脚本路径：

/usr/lib/zabbix/alertscripts

# encoding:UTF-8import sysreload(sys)sys.setdefaultencoding('utf-8')import xlsxwriterimport datetimeimport pymysqlimport numpy as npimport pandasimport smtplib, time, os__author__ = 'ALOM'__data__ = '2020/12/27'from email.mime.text import MIMETextfrom email.header import Headerdef send_mail_html(file):'''发送html内容邮件'''# 发送邮箱#sender = '1352209734x@163.com'sender = ("%s<1352209734x@163.com>")%(Header('Zabbix监控系统告警!','utf-8'),)# 接收邮箱receiver = '1058609199@qq.com'# 发送邮件主题t = time.strftime("%Y年%m月%d日", time.localtime())subject = t +'zabbix监控报表！'# 发送邮箱服务器smtpserver = 'smtp.163.com'# 发送邮箱用户/密码username = 'l1352209734x'password = '你的授权码'# 读取html文件内容f = open(file, 'rb')mail_body = f.read()f.close()# 组装邮件内容和标题，中文需参数'utf-8'，单字节字符不需要msg = MIMEText(mail_body, _subtype='html', _charset='utf-8')msg['Subject'] = Header(subject, 'utf-8')msg['From'] = sendermsg['To'] = receiver# 登录并发送邮件try:smtp = smtplib.SMTP()smtp.connect(smtpserver)smtp.login(username, password)smtp.sendmail(sender, receiver, msg.as_string())except:print("邮件发送失败！")else:print("邮件发送成功！")finally:smtp.quit()def find_new_file(dir):'''查找目录下最新的文件'''file_lists = os.listdir(dir)file_lists.sort(key=lambda fn: os.path.getmtime(dir + "/" + fn)if not os.path.isdir(dir + "/" + fn)else 0)# print('最新的文件为： ' + file_lists[-1])file = os.path.join(dir, file_lists[-1])print('/usr/lib/zabbix/alertscripts/：', file)return filedir = '/usr/lib/zabbix/alertscripts/' # 指定文件目录file = find_new_file(dir) # 查找最新的html文件send_mail_html(file) # 发送html内容邮件Averagesql = """select from_unixtime(clock,'%Y-%m-%d') as time,count(DISTINCT mediatypeid) as idfrom alertsWHERE mediatypeid = 5AND 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 = 5AND 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 = 5AND 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 = 5AND 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 = 5AND 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.100.22.29'zdbuser = 'zabbix'zdbpass = 'zabbix'zdbport = 3306zdbname = 'zabbix''''conn=pymysql.connect("10.100.22.29", "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 countdef 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 list3if __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)

本人在实施过程中遇到了各种坑，但是都一一解决了，关于是哪些坑，这里就不多做描述了，大家如果实施中遇到了问题，可以在下方评论处或私信沟通哦，感谢关注，每日分享！