zabbix 报表统计邮件发送

疯飙的蜗牛

已于 2023-01-11 17:59:37 修改

阅读量2.7k

点赞数 3

分类专栏： zabbix 文章标签： zabbix

于 2019-07-23 16:26:01 首次发布

本文链接：https://blog.csdn.net/zhangxueleishamo/article/details/97002220

版权

zabbix 专栏收录该内容

8 篇文章 5 订阅

订阅专栏

1、思路：根据zabbix 4.2.3 自己的报表系统中触发器Top 100 中查询数据库信息，生成自己需要的信息

2、zabbix 数据库说明：Zabbix 数据库表结构说明文档_疯飙的蜗牛的博客-CSDN博客_item_preproc

3、问题：每天查询数据库，生成数据会对zabbix库造成压力。

4、解决办法：每天增量查询每天数据，将信息插入到其它数据库，在新库中查询，生成需要数据

注：本例中为了解决需求而做，具体优化请个人完成。

脚本：

1、1 0 * * * /usr/bin/sh /data/image/report/report.sh >/dev/null 2>&1 ；

每天执行导出zabbix库中需要数据，并插入到新库表中

#!/bin/bash

MYSQL="/usr/bin/mysql -uzabbix -pzabbix -S /var/lib/mysql/mysql.sock --default-character-set=utf8 -A -N"

clock_time=`date +%s`
#clock_time=1563465600
clock_time_one_day_ago=`expr $clock_time - 86400`

dump_data=/data/image/report/data/data.$clock_time.txt

#sql="SELECT zabbix.`hosts`.`host`,zabbix.hstgrp.groupid,zabbix.hstgrp.`name`,zabbix.`triggers`.triggerid,zabbix.`triggers`.description,zabbix.`triggers`.priority,zabbix.`events`.`value`,zabbix.`events`.clock FROM zabbix.`hosts`,zabbix.`triggers`,zabbix.`events`,zabbix.items,zabbix.functions,zabbix.hstgrp,zabbix.hosts_groups  WHERE  zabbix.`hosts`.hostid=zabbix.hosts_groups.hostid  AND zabbix.hosts_groups.groupid=zabbix.hstgrp.groupid  AND zabbix.`triggers`.triggerid = zabbix.`events`.objectid  AND zabbix.`hosts`.hostid = zabbix.items.hostid  AND zabbix.items.itemid=zabbix.functions.itemid  AND zabbix.functions.triggerid=zabbix.`triggers`.triggerid  AND zabbix.`triggers`.priority>2 AND zabbix.`events`.clock>1562144400;"

sql="SELECT
        zabbix.hosts.host,';',
        zabbix.hstgrp.groupid,';',
		zabbix.hstgrp.name,';',
        zabbix.triggers.triggerid,';',
        zabbix.triggers.description,';',
        zabbix.triggers.priority,';',
	zabbix.alerts.mediatypeid,';',
	zabbix.alerts.sendto,';',
	zabbix.alerts.status,';',
        zabbix.events.clock
    FROM
        zabbix.hosts,
        zabbix.triggers,
        zabbix.events,
        zabbix.items,
        zabbix.functions,
        zabbix.hstgrp,
	zabbix.alerts,
        zabbix.hosts_groups
    WHERE
        zabbix.hosts.hostid = zabbix.hosts_groups.hostid
        AND zabbix.hosts_groups.groupid = zabbix.hstgrp.groupid
        AND zabbix.triggers.triggerid = zabbix.events.objectid
        AND zabbix.hosts.hostid = zabbix.items.hostid
        AND zabbix.items.itemid = zabbix.functions.itemid
	AND zabbix.alerts.eventid = zabbix.events.eventid
        AND zabbix.functions.triggerid = zabbix.triggers.triggerid
        AND zabbix.triggers.priority >2
	AND zabbix.events.clock >= $clock_time_one_day_ago
	AND zabbix.events.clock < $clock_time"

result="$($MYSQL -e "$sql")"
####将结果输出到文件中
echo -e "$result" > $dump_data

####查看文件，导入到新库，新表中
cat $dump_data | while IFS=";" read host groupid groupname triggerid description priority mediatypeid sendto status clock
do
	$MYSQL -e "INSERT INTO report.newevent values ( null,'$host','$groupid','$groupname','$triggerid','$description','$priority','$mediatypeid','$sendto','$status','$clock')"
done

####去除指定表列中信息字符串 前后的空格或Tab，根据自己需要操作
$MYSQL -e "update report.newevent  set  groupname= replace(replace(replace(groupname,char(9),''),char(10),''),char(13),'')"

数据库表结构sql

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for newevent
-- ----------------------------
DROP TABLE IF EXISTS `newevent`;
CREATE TABLE `newevent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `host` varchar(128) NOT NULL,
  `groupid` bigint(20) NOT NULL,
  `groupname` varchar(255) NOT NULL,
  `triggerid` bigint(20) NOT NULL,
  `description` varchar(255) NOT NULL,
  `priority` int(11) NOT NULL,
  `mediatypeid` bigint(20) NOT NULL,
  `sendto` varchar(1024) NOT NULL,
  `status` int(11) NOT NULL,
  `clock` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2、根据需求统计信息，采用php生成7天内的数据查询

<html>
<head>
	<meta charset="UTF-8">
	<meta name="Generator" content="EditPlus®">
	<meta name="Author" content="">
	<meta name="Keywords" content="">
	<meta name="Description" content="">
	<title>zabbix监控告警</title>
	<style type="text/css">td {text-align:center; background-color:#FFD700}</style>
</head>

<th><font size="4" color="BlueViolet">报表数据统计为:<?php echo date("Y-m-d H:i:s",strtotime("-7 day"))."至".date("Y-m-d H:i:s");  ?></font></th>

<body>
	<table width="820" border="0">
	<tr><td><TABLE BORDER=1>
	<tr><td colspan="5" style="background-color:#FFA500;">zabbix按主机组告警统计</td></tr> 
	<tr><th>主机组</th><th>所属部门</th><th colspan="3">告警次数</th></tr>

<?php
header("Content-Type: text/html;charset=utf-8");
require_once("/data/image/report/info/con_mysql.php");
require_once("/data/image/report/info/group.php");

$db_name="report";
$table_name="newevent";
$clock=strtotime('now');
$clock_1=$clock-86400;
$clock_7=$clock-604800;

$sql = "select SUM(B.mun) as Bnum,B.groupname,B.groupid from (select A.mun,A.groupname,A.description,A.sendto,A.triggerid,A.groupid from (select COUNT(*)as mun,groupname,description,sendto,triggerid,groupid from newevent where clock>$clock_7 group by groupname,triggerid,sendto ) as A GROUP BY A.description,A.triggerid ) as B GROUP BY B.groupname ORDER BY Bnum DESC";
mysql_query("SET NAMES 'utf8'");

$result = mysql_query($sql,$con);

while($mysql_array = mysql_fetch_row($result))
{
	echo "<tr><td>".$mysql_array[1]."</td><td>"; group($mysql_array[2]); echo"</td>" ?> <td colspan="3"> <?php echo $mysql_array[0]."</td>";
}
?>
<tr><td colspan="5" style="background-color:#FFA500;">zabbix按触发器告警统计(告警级别: 1 表示:信息 2 表示:告警 3 表示:一般严重 4 表示:严重 5 表示:灾难)</td></tr>
<TH>主机</TH><TH>告警次数</TH><TH>主机组</TH><TH>触发器</TH><TH>告警级别</TH></TR>

<?php
$sql = "select * from (SELECT COUNT(*) as num ,host,groupname,triggerid,description,priority from $table_name where clock>$clock_7  GROUP BY host,groupname,triggerid,description,sendto ) as A GROUP BY A.triggerid ORDER BY A.num DESC ";

mysql_query("SET NAMES 'utf8'");
$result = mysql_query($sql,$con);

while($mysql_array = mysql_fetch_row($result))
{
        echo "<tr><td>".$mysql_array[1]."</td><td>".$mysql_array[0]."</td><td>".$mysql_array[2]."</td><td>".$mysql_array[4]."</td><td>".$mysql_array[5]."</td></tr>";
}

mysql_close($con);

?>
</table>
</body>
</html>

3、数据库连接文件

<?php
$con = mysql_connect("localhost","zabbix","zabbix");
if (!$con){
  die('Could not connect: ' . mysql_error());
}else{
	mysql_select_db("report",$con);
}

?>

4、根据zabbix组id，匹配公司部门名称（1个部门对应多个组id）

<?php
function group($groupid){
	switch ($groupid) {
	case 49:
		echo "算法";
        	break;
        case 19:
        	echo "前端";
        	break;
	case 32:
                echo "前端";
                break;
        case 38:
        	echo "平台";
        	break;
	case 40:
                echo "平台";
                break;
	case 23:
                echo "数据";
                break;
	case 25:
                echo "数据";
                break;
	case 46:
                echo "数据";
                break;
	case 48:
                echo "数据";
                break;
	case 15:
                echo "引擎";
                break;
	case 24:
                echo "引擎";
                break;
	case 26:
                echo "引擎";
                break;
	case 27:
                echo "引擎";
                break;
	case 28:
                echo "引擎";
                break;
	case 29:
                echo "引擎";
                break;
	case 30:
                echo "引擎";
                break;
	case 31:
                echo "引擎";
                break;
	case 41:
                echo "引擎";
                break;
	default:
		echo "运维";
	}
}

?>

5、python 发送邮件通知，根据需求发送，本例中为每周五一次

1 0 * * 5 /usr/bin/sh /data/image/report/weekly.sh >/dev/null 2>&1

#!/usr/bin/python  
#coding:utf-8
import smtplib, time, os
from email.mime.text import MIMEText
from email.header import Header
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
 
def send_mail_html(file):
    '''发送html内容邮件'''
    # 发送邮箱
    sender = ("%s<monit@mail.com.cn>")%(Header('Zabbix监控系统告警周报!','utf-8'),) 
    # 接收邮箱
    receiver = ["龙 <long@mail.com.cn>","xiao <xiao@mail.com.cn>","test <test@mail.com.cn>"]
    # 发送邮件主题
    t = time.strftime("%Y年%m月%d日", time.localtime())
    subject = t +'zabbix监控周报！'
    # 发送邮箱服务器
    smtpserver = 'mail.com.cn'
    # 发送邮箱用户/密码
    username = 'monit@mail.com.cn'
    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'] = sender
    msg['To'] = ','.join(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('/data/image/report/html/：', file)
    return file
 
dir = '/data/image/report/html/'  # 指定文件目录
file = find_new_file(dir)  # 查找最新的html文件
send_mail_html(file)  # 发送html内容邮件

效果：