365 lines
13 KiB
Plaintext
365 lines
13 KiB
Plaintext
# 一、数据表结构（monitor_rule 复合主键，支持规则+实例精细化管控）
|
||
## 建表SQL（先执行备份再运行）
|
||
```sql
|
||
USE alert_mail_stat;
|
||
DROP TABLE IF EXISTS monitor_rule;
|
||
CREATE TABLE IF NOT EXISTS monitor_rule (
|
||
rule_name VARCHAR(128) NOT NULL COMMENT '监控规则名称，对应alert_name',
|
||
instance VARCHAR(128) NOT NULL COMMENT '主机实例地址',
|
||
status TINYINT NOT NULL DEFAULT 1 COMMENT '1=启用展示 0=下线隐藏',
|
||
create_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
update_at DATETIME NULL COMMENT '更新时间',
|
||
PRIMARY KEY (rule_name, instance)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='规则+实例存活注册表';
|
||
```
|
||
|
||
# 二、完整最终版 api/index.php（包含全部新增4个接口）
|
||
```php
|
||
<?php
|
||
session_start();
|
||
// 未登录直接拒绝所有接口访问
|
||
if(empty($_SESSION['user_id'])){
|
||
header("Content-Type:application/json;charset=utf-8");
|
||
echo json_encode(["code"=>401,"msg"=>"未登录，请前往登录页面"]);
|
||
exit;
|
||
}
|
||
// 统一数据库配置
|
||
$dbHost = "10.150.117.190";
|
||
$dbUser = "root";
|
||
$dbPass = "hp93000";
|
||
$dbName = "alert_mail_stat";
|
||
|
||
// 统一数据库连接函数
|
||
function getDbConn() {
|
||
global $dbHost, $dbUser, $dbPass, $dbName;
|
||
$conn = mysqli_connect($dbHost, $dbUser, $dbPass, $dbName);
|
||
mysqli_set_charset($conn, "utf8mb4");
|
||
return $conn;
|
||
}
|
||
|
||
$act = $_GET['act'] ?? '';
|
||
|
||
// CSV导出接口（独立流式下载，不输出JSON头）
|
||
if ($act === "export_csv") {
|
||
$conn = getDbConn();
|
||
if (!$conn) {
|
||
header("Content-Type:text/html;charset=utf-8");
|
||
echo "数据库连接失败：" . mysqli_connect_error();
|
||
exit;
|
||
}
|
||
$today = date("Y-m-d");
|
||
$sql = "SELECT alert_type,alert_name,instance,severity,starts_at,receive_time
|
||
FROM alert_log WHERE DATE(receive_time) = '$today' ORDER BY receive_time DESC";
|
||
$res = mysqli_query($conn, $sql);
|
||
|
||
ob_clean();
|
||
header("Content-Type: text/csv; charset=utf-8");
|
||
header("Content-Disposition: attachment; filename=告警报表_" . $today . ".csv");
|
||
echo "\xEF\xBB\xBF";
|
||
$header = ["告警状态", "告警名称", "实例", "级别", "故障开始时间", "接收时间"];
|
||
echo implode(",", $header) . "\r\n";
|
||
|
||
while ($row = mysqli_fetch_assoc($res)) {
|
||
$status = $row['alert_type'] == 1 ? "触发" : "恢复";
|
||
$line = [
|
||
$status,
|
||
$row['alert_name'],
|
||
$row['instance'],
|
||
$row['severity'],
|
||
$row['starts_at'],
|
||
$row['receive_time']
|
||
];
|
||
foreach ($line as &$v) {
|
||
$v = '"' . str_replace('"', '""', $v) . '"';
|
||
}
|
||
echo implode(",", $line) . "\r\n";
|
||
}
|
||
mysqli_close($conn);
|
||
exit;
|
||
}
|
||
|
||
// 所有JSON接口统一返回头
|
||
header("Content-Type:application/json;charset=utf-8");
|
||
$conn = getDbConn();
|
||
if (!$conn) {
|
||
echo json_encode(["code" => 500, "msg" => "数据库连接失败：" . mysqli_connect_error()], JSON_UNESCAPED_UNICODE);
|
||
exit;
|
||
}
|
||
$today = date("Y-m-d");
|
||
|
||
// 接口1：下线整条监控规则（该规则下所有实例全部清理）
|
||
if($act === "clear_offline_rule"){
|
||
$rule = $_GET['rule_name'] ?? '';
|
||
if(empty($rule)){
|
||
echo json_encode(["code"=>400,"msg"=>"缺少rule_name参数"],JSON_UNESCAPED_UNICODE);
|
||
exit;
|
||
}
|
||
$ruleEsc = mysqli_real_escape_string($conn, $rule);
|
||
// 标记该规则下全部实例为下线
|
||
$updateSql = "UPDATE monitor_rule SET status=0, update_at=NOW() WHERE rule_name='$ruleEsc'";
|
||
mysqli_query($conn, $updateSql);
|
||
// 批量插入恢复记录，消除页面残留告警
|
||
$insertSql = "
|
||
INSERT INTO alert_log (alert_type,alert_name,instance,severity,starts_at,receive_time)
|
||
SELECT DISTINCT 2,alert_name,instance,severity,NOW(),NOW()
|
||
FROM alert_log
|
||
WHERE alert_name='$ruleEsc'
|
||
AND (alert_name,instance) NOT IN (
|
||
SELECT alert_name,instance FROM alert_log WHERE alert_type=2
|
||
)
|
||
";
|
||
mysqli_query($conn, $insertSql);
|
||
echo json_encode(["code"=>0,"msg"=>"整条规则下线完成，所有实例告警已清除"],JSON_UNESCAPED_UNICODE);
|
||
mysqli_close($conn);
|
||
exit;
|
||
}
|
||
|
||
// 接口2：单独下线单台实例（仅隐藏指定机器，同规则其他主机不受影响）
|
||
if($act === "clear_single_instance"){
|
||
$rule = $_GET['rule_name'] ?? '';
|
||
$inst = $_GET['instance'] ?? '';
|
||
if(empty($rule) || empty($inst)){
|
||
echo json_encode(["code"=>400,"msg"=>"缺少rule_name或instance参数"],JSON_UNESCAPED_UNICODE);
|
||
exit;
|
||
}
|
||
$ruleEsc = mysqli_real_escape_string($conn, $rule);
|
||
$instEsc = mysqli_real_escape_string($conn, $inst);
|
||
// 仅给当前机器插入恢复日志
|
||
$insertSql = "
|
||
INSERT INTO alert_log (alert_type,alert_name,instance,severity,starts_at,receive_time)
|
||
SELECT DISTINCT 2,alert_name,instance,severity,NOW(),NOW()
|
||
FROM alert_log
|
||
WHERE alert_name='$ruleEsc' AND instance='$instEsc'
|
||
AND (alert_name,instance) NOT IN (
|
||
SELECT alert_name,instance FROM alert_log WHERE alert_type=2
|
||
)
|
||
";
|
||
mysqli_query($conn, $insertSql);
|
||
echo json_encode(["code"=>0,"msg"=>"单实例告警清理完成"],JSON_UNESCAPED_UNICODE);
|
||
mysqli_close($conn);
|
||
exit;
|
||
}
|
||
|
||
// 接口3：恢复整条已下线规则（status改为1，重新展示所有实例告警）
|
||
if($act === "restore_rule"){
|
||
$rule = $_GET['rule_name'] ?? '';
|
||
if(empty($rule)){
|
||
echo json_encode(["code"=>400,"msg"=>"缺少rule_name参数"],JSON_UNESCAPED_UNICODE);
|
||
exit;
|
||
}
|
||
$ruleEsc = mysqli_real_escape_string($conn, $rule);
|
||
$updateSql = "UPDATE monitor_rule SET status=1, update_at=NOW() WHERE rule_name='$ruleEsc'";
|
||
mysqli_query($conn, $updateSql);
|
||
echo json_encode(["code"=>0,"msg"=>"监控规则已恢复，新故障会正常展示"],JSON_UNESCAPED_UNICODE);
|
||
mysqli_close($conn);
|
||
exit;
|
||
}
|
||
|
||
// 1. 今日统计卡片
|
||
if ($act === "day_total") {
|
||
$sql = "SELECT
|
||
SUM(IF(alert_type=1,1,0)) as fire_count,
|
||
SUM(IF(alert_type=2,1,0)) as resolve_count,
|
||
COUNT(DISTINCT instance) as instance_num
|
||
FROM alert_log WHERE DATE(receive_time) = '$today'";
|
||
$data = mysqli_fetch_assoc(mysqli_query($conn, $sql));
|
||
echo json_encode($data, JSON_UNESCAPED_UNICODE);
|
||
mysqli_close($conn);
|
||
exit;
|
||
}
|
||
|
||
// 2. 今日告警TOP10图表
|
||
if ($act === "top_alert") {
|
||
$sql = "SELECT alert_name,COUNT(*) cnt FROM alert_log WHERE DATE(receive_time)='$today' GROUP BY alert_name ORDER BY cnt DESC LIMIT 10";
|
||
$list = [];
|
||
$q = mysqli_query($conn, $sql);
|
||
while ($r = mysqli_fetch_assoc($q)) $list[] = $r;
|
||
echo json_encode($list, JSON_UNESCAPED_UNICODE);
|
||
mysqli_close($conn);
|
||
exit;
|
||
}
|
||
|
||
// 3. 今日全量告警明细表格
|
||
if ($act === "log_list") {
|
||
$sql = "SELECT alert_type,alert_name,instance,severity,starts_at,ends_at,receive_time
|
||
FROM alert_log WHERE DATE(receive_time)='$today' ORDER BY receive_time DESC";
|
||
$list = [];
|
||
$q = mysqli_query($conn, $sql);
|
||
while ($r = mysqli_fetch_assoc($q)) $list[] = $r;
|
||
echo json_encode($list, JSON_UNESCAPED_UNICODE);
|
||
mysqli_close($conn);
|
||
exit;
|
||
}
|
||
|
||
// 4. 全局跨天未恢复告警（过滤整条下线规则，单实例仅靠恢复日志隐藏）
|
||
if ($act === "active_firing") {
|
||
$maxSql = "SELECT alert_name,instance,MAX(receive_time) max_rt FROM alert_log GROUP BY alert_name,instance";
|
||
$maxRes = mysqli_query($conn, $maxSql);
|
||
$activeList = [];
|
||
while ($maxRow = mysqli_fetch_assoc($maxRes)) {
|
||
$an = $conn->real_escape_string($maxRow['alert_name']);
|
||
$ins = $conn->real_escape_string($maxRow['instance']);
|
||
$mrt = $conn->real_escape_string($maxRow['max_rt']);
|
||
|
||
// 判断该规则是否整条下线，下线直接跳过
|
||
$ruleCheck = mysqli_query($conn, "SELECT status FROM monitor_rule WHERE rule_name='$an' LIMIT 1");
|
||
$ruleStatus = 1;
|
||
if($ruleCheck && $ruleRow = mysqli_fetch_assoc($ruleCheck)){
|
||
$ruleStatus = intval($ruleRow['status']);
|
||
}
|
||
if($ruleStatus === 0){
|
||
continue;
|
||
}
|
||
|
||
$rowSql = "SELECT alert_name,instance,severity,starts_at,receive_time,alert_type
|
||
FROM alert_log
|
||
WHERE alert_name='$an' AND instance='$ins' AND receive_time='$mrt'";
|
||
$rowRes = mysqli_query($conn, $rowSql);
|
||
$data = mysqli_fetch_assoc($rowRes);
|
||
if ($data && (int)$data['alert_type'] === 1) {
|
||
$activeList[] = $data;
|
||
}
|
||
}
|
||
echo json_encode($activeList, JSON_UNESCAPED_UNICODE);
|
||
mysqli_close($conn);
|
||
exit;
|
||
}
|
||
|
||
// 非法请求兜底
|
||
echo json_encode(["code" => 400, "msg" => "无效请求参数act"], JSON_UNESCAPED_UNICODE);
|
||
mysqli_close($conn);
|
||
?>
|
||
```
|
||
|
||
# 三、4个管控接口完整调用地址（示例）
|
||
## 1、下线整条规则（全部机器隐藏）
|
||
```
|
||
http://10.150.117.190:8080/api/index.php?act=clear_offline_rule&rule_name=Host_Ping_Offline
|
||
```
|
||
|
||
## 2、下线单台实例（仅隐藏 10.150.10.118，同规则其他机器正常）
|
||
```
|
||
http://10.150.117.190:8080/api/index.php?act=clear_single_instance&rule_name=Host_Ping_Offline&instance=10.150.10.118
|
||
```
|
||
|
||
## 3、恢复整条下线规则（重新展示该规则所有机器）
|
||
```
|
||
http://10.150.117.190:8080/api/index.php?act=restore_rule&rule_name=Host_Ping_Offline
|
||
```
|
||
|
||
## 4、兜底自动恢复机制（无需手动调用）
|
||
只要该规则下任意机器再次上报告警，webhook会自动将对应 rule_name+instance 状态置为1，自动恢复展示。
|
||
|
||
# 四、配套修改后的 webhook.py（同步规则+实例到monitor_rule）
|
||
```python
|
||
from flask import Flask, request, jsonify
|
||
import pymysql
|
||
|
||
app = Flask(__name__)
|
||
|
||
def get_db():
|
||
return pymysql.connect(
|
||
host="127.0.0.1",
|
||
user="root",
|
||
password="hp93000",
|
||
database="alert_mail_stat",
|
||
charset="utf8mb4"
|
||
)
|
||
|
||
@app.route("/alert", methods=["POST"])
|
||
def alert_receive():
|
||
try:
|
||
data = request.get_json()
|
||
alerts = data.get("alerts", [])
|
||
insert_num = 0
|
||
update_num = 0
|
||
db = get_db()
|
||
cur = db.cursor()
|
||
|
||
for alert in alerts:
|
||
fp = alert["fingerprint"]
|
||
alert_name = alert["labels"].get("alertname", "")
|
||
instance = alert["labels"].get("instance", "")
|
||
severity = alert["labels"].get("severity", "warning")
|
||
status = alert["status"]
|
||
start_at = alert.get("startsAt", "")
|
||
end_at = alert.get("endsAt")
|
||
alert_type = 2 if status == "resolved" else 1
|
||
content_str = str(alert)
|
||
|
||
# 告警入库逻辑不变
|
||
sql = """
|
||
INSERT INTO alert_log(mail_uid,alert_type,alert_name,instance,severity,starts_at,ends_at,content,receive_time)
|
||
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,NOW())
|
||
"""
|
||
cur.execute(sql, (fp, alert_type, alert_name, instance, severity, start_at, end_at, content_str))
|
||
insert_num += 1
|
||
|
||
# 同步 规则+实例 联合记录，存在自动置为启用1
|
||
if alert_name and instance:
|
||
sync_sql = """
|
||
INSERT INTO monitor_rule (rule_name, instance, status)
|
||
VALUES (%s, %s, 1)
|
||
ON DUPLICATE KEY UPDATE status = 1
|
||
"""
|
||
cur.execute(sync_sql, [alert_name, instance])
|
||
|
||
db.commit()
|
||
cur.close()
|
||
db.close()
|
||
return jsonify({"code": 200, "insert": insert_num, "update": update_num})
|
||
|
||
except Exception as e:
|
||
if "db" in locals():
|
||
db.rollback()
|
||
db.close()
|
||
return jsonify({"code": 500, "msg": str(e)}), 200
|
||
|
||
if __name__ == "__main__":
|
||
app.run(host="0.0.0.0", port=909, debug=False)
|
||
```
|
||
|
||
# 五、定时兜底清理脚本 clear_offline_rule_cron.py
|
||
```python
|
||
import pymysql
|
||
DB_CFG = {
|
||
"host": "127.0.0.1",
|
||
"user": "root",
|
||
"password": "hp93000",
|
||
"database": "alert_mail_stat",
|
||
"charset": "utf8mb4"
|
||
}
|
||
|
||
def run_clear():
|
||
conn = pymysql.connect(**DB_CFG)
|
||
cur = conn.cursor()
|
||
cur.execute("SELECT DISTINCT rule_name FROM monitor_rule WHERE status=0")
|
||
offline_rules = [row[0] for row in cur.fetchall()]
|
||
for rule in offline_rules:
|
||
insert_sql = """
|
||
INSERT INTO alert_log (alert_type,alert_name,instance,severity,starts_at,receive_time)
|
||
SELECT DISTINCT 2,alert_name,instance,severity,NOW(),NOW()
|
||
FROM alert_log
|
||
WHERE alert_name=%s
|
||
AND (alert_name,instance) NOT IN (
|
||
SELECT alert_name,instance FROM alert_log WHERE alert_type=2
|
||
)
|
||
"""
|
||
cur.execute(insert_sql, [rule])
|
||
conn.commit()
|
||
cur.close()
|
||
conn.close()
|
||
|
||
if __name__ == "__main__":
|
||
run_clear()
|
||
```
|
||
定时任务配置：
|
||
```cron
|
||
0 * * * * /usr/bin/python3 /opt/alert_dashboard/clear_offline_rule_cron.py >> /opt/alert_dashboard/clear_rule.log 2>&1
|
||
```
|
||
|
||
# 六、整体逻辑说明
|
||
1. 单台机器下线：仅插入恢复日志，不修改monitor_rule；机器重新告警自动恢复
|
||
2. 整条规则下线：monitor_rule全实例status=0，批量补恢复日志；需调用restore_rule恢复
|
||
3. 兜底保障：每小时定时脚本自动清理下线规则残留告警
|
||
4. 自动激活：任意机器上报告警，webhook强制将该rule+instance置为启用 |