Zabbix中查询未处理问题的SQL并通过钉钉告警发送

为了查询未关闭且未确认的问题，我们需要关注 problem 表中的 r_eventid（恢复事件 ID）和 acknowledged（确认状态）列。以下是修正后的 SQL 查询示例：

SELECT
  t.triggerid,
  t.description,
  h.host,
  e.eventid,
  e.clock,
  t.priority,
  pt.tag AS problem_name,
  pt.value AS tag_value
FROM
  events e
  JOIN triggers t ON e.objectid = t.triggerid
  JOIN functions f ON t.triggerid = f.triggerid
  JOIN items i ON f.itemid = i.itemid
  JOIN hosts h ON i.hostid = h.hostid
  LEFT JOIN problem p ON e.eventid = p.eventid
  LEFT JOIN event_recovery r ON p.eventid = r.eventid
  LEFT JOIN problem_tag pt ON pt.eventid = e.eventid
WHERE
  e.value = 1
  AND e.source = 0
  AND e.object = 0
  AND h.status = 0
  AND i.status = 0
  AND t.status = 0
  AND p.r_eventid IS NULL
  AND p.acknowledged = 0
ORDER BY
  e.clock DESC;

查询出来是这样的，那么我们再对SQL进行优化，以便于查出这样的效果

SELECT
  t.description,
  h.host
FROM
  events e
  JOIN triggers t ON e.objectid = t.triggerid
  JOIN functions f ON t.triggerid = f.triggerid
  JOIN items i ON f.itemid = i.itemid
  JOIN hosts h ON i.hostid = h.hostid
  LEFT JOIN problem p ON e.eventid = p.eventid
  LEFT JOIN event_recovery r ON p.eventid = r.eventid
WHERE
  e.value = 1
  AND e.source = 0
  AND e.object = 0
  AND h.status = 0
  AND i.status = 0
  AND t.status = 0
  AND p.r_eventid IS NULL
  AND p.acknowledged = 0
ORDER BY
  e.clock DESC;

显示结果：

最终去除加可见名称查询的SQL：最终版本：

SELECT DISTINCT
  t.description,
  h.name AS host_name
FROM
  events e
  JOIN triggers t ON e.objectid = t.triggerid
  JOIN functions f ON t.triggerid = f.triggerid
  JOIN items i ON f.itemid = i.itemid
  JOIN hosts h ON i.hostid = h.hostid
  LEFT JOIN problem p ON e.eventid = p.eventid
  LEFT JOIN event_recovery r ON p.eventid = r.eventid
WHERE
  e.value = 1
  AND e.source = 0
  AND e.object = 0
  AND h.status = 0
  AND i.status = 0
  AND t.status = 0
  AND p.r_eventid IS NULL
  AND p.acknowledged = 0
ORDER BY
  e.clock DESC;

接下去我们把这个查询出来的结果通过钉钉机器人的方式去发送出去

为了将查出的结果通过钉钉展示给别人，您需要执行以下步骤：

从 Zabbix 数据库中获取数据。
使用钉钉机器人 API 发送查询结果。

以下是一个 Python 示例，展示了如何执行上述步骤。首先确保已安装了 pymysql 和 requests 库：

pip install pymysql requests

如果报错

首先，您需要确保您的系统上已安装 pip 命令。如果您使用的是 CentOS 或 RHEL 等基于 Red Hat 的发行版，可以使用以下命令安装 pip：

sudo yum install epel-release sudo yum install python-pip

然后，创建一个 Python 脚本，如 zabbix_dingtalk.py：

import pymysql
import requests
import json

# Zabbix 数据库配置
zabbix_db_config = {
    'host': 'your_zabbix_db_host',
    'user': 'your_zabbix_db_user',
    'password': 'your_zabbix_db_password',
    'db': 'your_zabbix_db_name'
}

# 钉钉机器人 Webhook
dingtalk_webhook = "your_dingtalk_webhook_url"

# 从 Zabbix 数据库中获取数据
def get_unresolved_issues():
    connection = pymysql.connect(**zabbix_db_config)
    cursor = connection.cursor(pymysql.cursors.DictCursor)

    sql = """
    SELECT
      t.description,
      h.host
    FROM
      events e
      JOIN triggers t ON e.objectid = t.triggerid
      JOIN functions f ON t.triggerid = f.triggerid
      JOIN items i ON f.itemid = i.itemid
      JOIN hosts h ON i.hostid = h.hostid
      LEFT JOIN problem p ON e.eventid = p.eventid
      LEFT JOIN event_recovery r ON p.eventid = r.eventid
    WHERE
      e.value = 1
      AND e.source = 0
      AND e.object = 0
      AND h.status = 0
      AND i.status = 0
      AND t.status = 0
      AND p.r_eventid IS NULL
      AND p.acknowledged = 0
    ORDER BY
      e.clock DESC;
    """

    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()
    connection.close()

    return results

# 发送钉钉消息
def send_dingtalk_message(webhook, message):
    headers = {'Content-Type': 'application/json;charset=utf-8'}
    data = {
        'msgtype': 'text',
        'text': {
            'content': message
        }
    }

    response = requests.post(webhook, headers=headers, data=json.dumps(data))
    return response.json()

# 主函数
def main():
    unresolved_issues = get_unresolved_issues()
    message = '未解决且未确认的问题：\n\n'
    for issue in unresolved_issues:
        message += f"{issue['description']} - {issue['host']}\n"

    response = send_dingtalk_message(dingtalk_webhook, message)
    print(response)

if __name__ == '__main__':
    main()

替换 zabbix_db_config 和 dingtalk_webhook 变量中的占位符为您的实际 Zabbix 数据库配置和钉钉机器人 Webhook URL。

执行该脚本：

python zabbix_dingtalk.py

脚本将查询未解决且未确认的问题，并将结果以文本消息的形式发送到钉钉群。

您还可以将此脚本添加到计划任务中

在执行脚本之前，您需要确保已经设置了钉钉机器人。设置钉钉机器人的方法如下：

打开钉钉 App，进入要接收告警的群聊界面。
点击群聊右上角的群设置图标，进入群设置。
点击 “群机器人”，然后点击右下角的 “+” 按钮。
在 “添加机器人” 页面，选择 “自定义” 机器人。
设置机器人的名称，如 “Zabbix告警”，并选择一个头像。点击 “完成”。
在 “添加完成” 页面，点击 “复制” 按钮复制 “Webhook” 地址。这个地址将用于发送告警消息。

现在，您已经设置了钉钉机器人并获取了 Webhook 地址。将 dingtalk_webhook 变量的值替换为您刚刚复制的 Webhook 地址。

dingtalk_webhook = "your_dingtalk_webhook_url"

保存更改后，通过运行 python zabbix_dingtalk.py 命令，执行脚本。如果一切正常，您将看到钉钉群中接收到脚本发送的未解决且未确认的问题消息。

要定期发送告警，您可以将脚本添加到计划任务。例如，对于 Linux，可以使用 cron 来实现：

打开终端，运行 crontab -e 命令。
在编辑器中，添加以下行（将 /path/to/zabbix_dingtalk.py 替换为脚本的实际路径

*/5 * * * * /usr/bin/python /path/to/zabbix_dingtalk.py

这将每隔5分钟运行一次脚本。请根据您的需求调整执行频率。

现在，您已经成功设置了定期通过钉钉发送未解决且未确认的问题告警。

修改后的脚本代码：更改了如果没有问题就显示：目前没有未解决和未确认的问题

import pymysql
import requests
import json
 
# Zabbix 数据库配置
zabbix_db_config = {
    'host': '127.0.0.1',
    'user': 'root',
    'password': '密码',
    'db': 'zabbix'
}
 
# 钉钉机器人 Webhook
dingtalk_webhook = "https://oapi.dingtalk.com/robot/send?access_token=3d59ea5a0731a5dbf5665c6da030df71c050a0538e9729eb5a8ba205bccb353e"
 
# 从 Zabbix 数据库中获取数据
def get_unresolved_issues():
    connection = pymysql.connect(**zabbix_db_config)
    cursor = connection.cursor(pymysql.cursors.DictCursor)
 
    sql = """
    SELECT DISTINCT
  t.description,
  h.name AS host_name
FROM
  events e
  JOIN triggers t ON e.objectid = t.triggerid
  JOIN functions f ON t.triggerid = f.triggerid
  JOIN items i ON f.itemid = i.itemid
  JOIN hosts h ON i.hostid = h.hostid
  LEFT JOIN problem p ON e.eventid = p.eventid
  LEFT JOIN event_recovery r ON p.eventid = r.eventid
WHERE
  e.value = 1
  AND e.source = 0
  AND e.object = 0
  AND h.status = 0
  AND i.status = 0
  AND t.status = 0
  AND p.r_eventid IS NULL
  AND p.acknowledged = 0
ORDER BY
  e.clock DESC;
    """
 
    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()
    connection.close()
 
    return results
 
# 发送钉钉消息
def send_dingtalk_message(webhook, message):
    headers = {'Content-Type': 'application/json;charset=utf-8'}
    data = {
        'msgtype': 'text',
        'text': {
            'content': message
        }
    }
 
    response = requests.post(webhook, headers=headers, data=json.dumps(data))
    return response.json()
 
# 主函数
def main():
    unresolved_issues = get_unresolved_issues()
    if not unresolved_issues:
        message = '目前没有未解决和未确认的问题'
    else:
        message = '目前为止未解决且未确认的问题：\n\n'
        for issue in unresolved_issues:
            message += f"{issue['description']} - {issue['host_name']}\n"

    response = send_dingtalk_message(dingtalk_webhook, message)
    print(response)
 
if __name__ == '__main__':
    main()

还需要修改一些bug，并且在问题前面加序号

最终脚本：

import pymysql
import requests
import json
 
# Zabbix 数据库配置
zabbix_db_config = {
    'host': '127.0.0.1',
    'user': 'root',
    'password': 'QWER1234@',
    'db': 'zabbix'
}
 
# 钉钉机器人 Webhook
dingtalk_webhook = "https://oapi.dingtalk.com/robot/send?access_token=自己的token"
 
# 从 Zabbix 数据库中获取数据
def get_unresolved_issues():
    connection = pymysql.connect(**zabbix_db_config)
    cursor = connection.cursor(pymysql.cursors.DictCursor)
 
    sql = """
    SELECT DISTINCT
  t.description,
  h.name AS host_name
FROM
  events e
  JOIN triggers t ON e.objectid = t.triggerid
  JOIN functions f ON t.triggerid = f.triggerid
  JOIN items i ON f.itemid = i.itemid
  JOIN hosts h ON i.hostid = h.hostid
  LEFT JOIN problem p ON e.eventid = p.eventid
  LEFT JOIN event_recovery r ON p.eventid = r.eventid
WHERE
  e.value = 1
  AND e.source = 0
  AND e.object = 0
  AND h.status = 0
  AND i.status = 0
  AND t.status = 0
  AND p.r_eventid IS NULL
  AND p.acknowledged = 0
ORDER BY
  e.clock DESC;
    """
 
    cursor.execute(sql)
    results = cursor.fetchall()
    cursor.close()
    connection.close()
 
    return results
 
# 发送钉钉消息
def send_dingtalk_message(webhook, message):
    headers = {'Content-Type': 'application/json;charset=utf-8'}
    data = {
        'msgtype': 'text',
        'text': {
            'content': message
        }
    }
 
    response = requests.post(webhook, headers=headers, data=json.dumps(data))
    return response.json()
 
# 主函数
def main():
    unresolved_issues = get_unresolved_issues()
    if not unresolved_issues:
        message = '目前没有未解决和未确认的问题(每日定时播报)'
    else:
        message = '目前为止未解决且未确认的问题(每日定时播报)：\n\n'
        for i, issue in enumerate(unresolved_issues, start=1):
            issue_description = issue['description'].replace('{HOST.NAME}', issue['host_name'])
            message += f"{i}. {issue_description} - {issue['host_name']}\n"

    response = send_dingtalk_message(dingtalk_webhook, message)
    print(response)

 
if __name__ == '__main__':
    main()

最终效果展示：