【数据可视化】基于DataEase制作Zabbix监控系统-告警监控大屏

最新推荐文章于 2024-07-25 14:47:04 发布

贝小贝貝

最新推荐文章于 2024-07-25 14:47:04 发布

阅读量3.7k

点赞数 3

分类专栏：数据可视化 DataEase 文章标签： zabbix mysql 数据库数据可视化 BI

本文链接：https://blog.csdn.net/xiebeixb/article/details/127084454

版权

DataEase 同时被 2 个专栏收录

26 篇文章 1 订阅

订阅专栏

数据可视化

17 篇文章 5 订阅

订阅专栏

制作Zabbix告警监控大屏的背景介绍：

1、希望能够更直观的看到当前告警主机、告警主机组、告警分布、主机被监控情况等跟告警相关的内容；

2、公司的Zabbix监控系统的数据库系统为MySQL；

3、公司部署了DataEase。

下图为成果图：

一、新建Zabbix的MySQL数据源，如下图所示：

二、添加对应的数据集

1、添加数据库数据集：选择hosts表、hstgrp表，创建定时同步的数据库数据集

2、添加MySQL数据集，如下图所示，SQL语句在图后，依次添加数据集

（1）创建问题告警级别分布数据集，MySQL语句如下：

SELECT
	COUNT(p.severity) count,
	p.severity,
	(
		CASE p.severity
		WHEN '0' THEN
			'未定义'
		WHEN '1' THEN
			'信息'
		WHEN '2' THEN
			'警告'
		WHEN '3' THEN
			'一般严重'
		WHEN '4' THEN
			'严重'
		WHEN p.severity = '5' THEN
			'灾难'
		ELSE
			'未知'
		END
	) severityName
FROM
	problem p
LEFT JOIN (
	SELECT
		s1.triggerid,
		(
			SELECT
				s2.itemid
			FROM
				functions s2
			WHERE
				s2.triggerid = s1.triggerid
			LIMIT 1
		) itemid
	FROM
		functions s1
	GROUP BY
		s1.triggerid
) f ON f.triggerid = p.objectid
LEFT JOIN `items` i ON i.itemid = f.itemid
LEFT JOIN `hosts` h ON h.hostid = i.hostid
LEFT JOIN `interface` inf ON inf.hostid = h.hostid
WHERE
	ISNULL(p.r_eventid)
AND h. STATUS = 0
AND i.`status` = 0
GROUP BY
	p.severity
ORDER BY
	p.severity ASC

（2）创建问题列表数据集，MySQL语句如下：

SELECT
	p.eventid,
	FROM_UNIXTIME(p.clock) time,
	p.clock,
	p. NAME pname,
	p.acknowledged,
	p.severity,
	(
		CASE p.severity
		WHEN '0' THEN
			'未定义'
		WHEN '1' THEN
			'信息'
		WHEN '2' THEN
			'警告'
		WHEN '3' THEN
			'一般严重'
		WHEN '4' THEN
			'严重'
		WHEN p.severity = '5' THEN
			'灾难'
		ELSE
			'未知'
		END
	) severityName,
	p.objectid,
	i.`status`,
	i.key_,
	f.itemid,
	h. HOST,
	h. NAME,
	concat(inf.ip, ":", inf. PORT) ip,
	inf. PORT
FROM
	problem p
LEFT JOIN (
	SELECT
		s1.triggerid,
		(
			SELECT
				s2.itemid
			FROM
				functions s2
			WHERE
				s2.triggerid = s1.triggerid
			LIMIT 1
		) itemid
	FROM
		functions s1
	GROUP BY
		s1.triggerid
) f ON f.triggerid = p.objectid
LEFT JOIN `items` i ON i.itemid = f.itemid
LEFT JOIN `hosts` h ON h.hostid = i.hostid
LEFT JOIN `interface` inf ON inf.hostid = h.hostid
WHERE
	ISNULL(p.r_eventid)
AND h. STATUS = 0
AND i.`status` = 0
ORDER BY
	clock DESC

（3）创建问题主机组数据集，MySQL语句如下：

SELECT
	count(DISTINCT h.hostid) problemNum,
	(
		SELECT
			COUNT(hg.hostid)
		FROM
			hosts_groups hg
		WHERE
			hg.groupid = hs.groupid
	) total,
	(
		SELECT
			COUNT(hg.hostid)
		FROM
			hosts_groups hg
		WHERE
			hg.groupid = hs.groupid
	) - count(DISTINCT h.hostid) normal,
	hs. NAME
FROM
	problem p
LEFT JOIN (
	SELECT
		s1.triggerid,
		(
			SELECT
				s2.itemid
			FROM
				functions s2
			WHERE
				s2.triggerid = s1.triggerid
			LIMIT 1
		) itemid
	FROM
		functions s1
	GROUP BY
		s1.triggerid
) f ON f.triggerid = p.objectid
LEFT JOIN `items` i ON i.itemid = f.itemid
LEFT JOIN `hosts` h ON h.hostid = i.hostid
LEFT JOIN hosts_groups hg ON hg.hostid = h.hostid
LEFT JOIN hstgrp hs ON hs.groupid = hg.groupid
WHERE
	ISNULL(p.r_eventid)
AND h. STATUS = 0
AND i.`status` = 0
GROUP BY
	hs. NAME,
	hs.groupid
ORDER BY
	NAME

（4）创建主机问题排行数据集，MySQL语句如下：

SELECT
	COUNT(h. NAME) count,
	h. NAME
FROM
	problem p
LEFT JOIN (
	SELECT
		s1.triggerid,
		(
			SELECT
				s2.itemid
			FROM
				functions s2
			WHERE
				s2.triggerid = s1.triggerid
			LIMIT 1
		) itemid
	FROM
		functions s1
	GROUP BY
		s1.triggerid
) f ON f.triggerid = p.objectid
LEFT JOIN `items` i ON i.itemid = f.itemid
LEFT JOIN `hosts` h ON h.hostid = i.hostid
WHERE
	ISNULL(p.r_eventid)
AND h. STATUS = 0
AND i.`status` = 0
GROUP BY
	h. NAME
ORDER BY
	count DESC

（5）创建主机状态分布数据集，MySQL语句如下：

SELECT
	available,
	(
		CASE available
		WHEN '0' THEN
			'可用'
		WHEN '1' THEN
			'不可用'
		WHEN '2' THEN
			'未知'
		ELSE
			'未知'
		END
	) 主机状态,
	count(available) 主机数量
FROM
	`hosts`
WHERE
	`status` = '0'
AND `flags` = '0'
GROUP BY
	`available`;

三、创建仪表板和视图

（1）创建空白仪表板