我正在编写一个 API 来返回系统信息数据。我正在使用 get_status(( 当仪表板具有如下数据时,它会返回空数据。
系统信息
参数值详细信息 Zabbix服务器正在运行 是 127.0.0.1:10051 主机数量(启用/禁用/模板( 109 16/5/88 项目数(启用/禁用/不支持( 621 328/287/6 触发器数量(启用/禁用 [问题/正常]( 333 197/136 [36/161] 用户数量(在线( 17 1 所需的服务器性能,每秒新值 37.11
我使用 SQL 查询从数据库中选择数据。但是,项目数、触发器数、用户数和服务器性能的数据与仪表板不符。 我的代码如下: 项目
select count(itemid) as numberOfItems from items; => 2630
select count(itemid) as numberOfItems from items JOIN hosts on hosts.hostid = items.hostid where hosts.`status`=0; => 427
select count(itemid) as numberOfItemEnabled from items JOIN hosts on hosts.hostid = items.hostid where items.STATUS = 0 AND hosts.`status`=0; => 427
select count(itemid) as numberOfItemDisabled from items JOIN hosts on hosts.hostid = items.hostid where items.STATUS = 1 AND hosts.`status`=0; => 0
select count(itemid) as numberOfItemNotSupported from items JOIN hosts on hosts.hostid = items.hostid where items.state = 1 AND hosts.`status`=0; => 6
我的妖孽: 项目数(启用/禁用/不支持( 427 427/0/6
网页仪表板: 项目数(启用/禁用/不支持( 621 328/287/6
//触发
select count(triggerid) as numberOfTrigger from triggers; => 1329
select count(triggerid) as numberOfTriggerEnabled from triggers where status = 0; => 1328
select count(triggerid) as numberOfTriggerDisabled from triggers where status = 1; => 1
select count(triggerid) as numberOfTriggerOn from triggers where value = 0; => 1273
select count(triggerid) as numberOfTriggerOff from triggers where value = 1; => 56
我的妖孽: 触发器数量(启用/禁用 [问题/正常]( 1329 1328/1[1273/56 ]
网页仪表板: 触发器数量(启用/禁用 [问题/正常]( 333 197/136 [36/161]
// Number user online
//Number of users (online)
$numberOfUser = DBfetch(DBselect('select count(userid) as numberOfUser from users'));
$numberOfUserSessions = DBfetchArray(DBselect('select distinct(userid) from sessions'));
$numberOfUserOnline = 0;
foreach ($numberOfUserSessions as $userSession)
{
$sessionStatus = DBfetchArray(DBselect('select * from sessions where userid = '.$userSession['userid'].' order by lastaccess desc limit 2'));
if($sessionStatus[0]['status'] !== $sessionStatus[1]['status'])
{
$numberOfUserOnline = $numberOfUserOnline + 1;
}
}
我的妖孽: 用户数量(在线( 17 2
网页仪表板: 用户数量(在线( 17 1
// Server performance
$vps_total = DBfetch(DBselect(
'SELECT sum(vps) AS vps
FROM (
SELECT count(*),count(*)/i.delay as vps
FROM items i
JOIN hosts h ON i.hostid=h.hostid
WHERE i.status=0
AND h.status=0
AND i.type=0
GROUP BY i.type,i.delay
ORDER BY i.type, i.delay
) as data'
));
$server_performance = round($vps_total['vps'], 2);
我的妖孽: 所需的服务器性能,每秒新值 35.11
网页仪表板: 所需的服务器性能,每秒新值 37.11
请建议帮助我为与 Web 仪表板相同的结果进行正确的 SQL 查询。
谢谢和最好的问候, 比恩赫夫
有两种方法可以得到这个结果:
1:您可以通过此网址获取所有信息:
http://{zabbix server ip}/zabbix/zabbix.php?action=widget.systeminfo.view
您可以获取Zabbix系统信息:
{
"header": "System information",
"body": "<table class="list-table" id="t5e73161796549893369398"><thead><tr><th>Parameter</th><th>Value</th><th>Details</th></tr></thead><tbody><tr><th>Zabbix server is running</th><td><span class="green">Yes</span></td><td>localhost:10051</td></tr><tr><th>Number of hosts (enabled/disabled/templates)</th><td>173</td><td><span class="green">1</span> / <span class="red">1</span> / <span class="grey">171</span></td></tr><tr><th><span title="Only items assigned to enabled hosts are counted">Number of items (enabled/disabled/not supported)</span></th><td>285</td><td><span class="green">255</span> / <span class="red">0</span> / <span class="grey">30</span></td></tr><tr><th><span title="Only triggers assigned to enabled hosts and depending on enabled items are counted">Number of triggers (enabled/disabled [problem/ok])</span></th><td>54</td><td>53 / 1 [<span class="red">1</span> / <span class="green">52</span>]</td></tr><tr><th>Number of users (online)</th><td>2</td><td><span class="green">2</span></td></tr><tr><th>Required server performance, new values per second</th><td>2.1</td><td></td></tr></tbody></table>"
}
然后,您可以解析此信息以获取与Zabbix仪表板相同的Deatil系统信息。
2.by SQL 查询:
- 主机信息:
select status, count(status) hostNumber from hosts where flags = 0 group by status`
- items ifno(仅计算分配给已启用主机的项目(
select state, count(state) from items where hostid in
(select hostid from hosts where flags = 0 and status = 0)
and status = 0 and trends!= 0 group by state
- 触发器
- 信息(仅分配给已启用主机的触发器,具体取决于 对启用的项目进行计数(
SELECT DISTINCT t.triggerid
FROM triggers t,functions f,items i
WHERE i.hostid in (select hostid from hosts where flags = 0 and status = 0)
AND f.triggerid=t.triggerid
AND f.itemid=i.itemid
AND t.flags IN (0,4)
)
GROUP BY status
- 问题
select count(1) from problem where object = 0 and source = 0`
这不是对您的问题的直接回答,但添加到您的查询列表中可能会很有趣。要使用户当前联机,您可以使用以下查询:
SELECT
t2.userid,
t2.alias,
t2.name,
t2.surname,
t2.refresh,
CASE
WHEN t2.type = 1 THEN 'User'
WHEN t2.type = 2 THEN 'Admin'
WHEN t2.type = 3 THEN 'SuperAdmin'
ELSE t2.type
END AS UserType,
t2.theme,
t2.attempt_failed,
t2.attempt_ip,
t2.attempt_clock,
t2.rows_per_page,
FROM_UNIXTIME(t1.lastaccess) as lastaccess,
t1.status
FROM sessions as t1
LEFT JOIN users as t2 ON t1.userid = t2.userid
WHERE
t1.lastaccess IS NOT NULL
AND DATE(FROM_UNIXTIME(t1.lastaccess))=CURDATE()
AND ABS(TIMESTAMPDIFF(MINUTE, FROM_UNIXTIME(t1.lastaccess), NOW())) <= 5
GROUP BY t1.userid, t1.sessionid
ORDER BY FROM_UNIXTIME(t1.lastaccess) DESC;
如果您希望将它们放在一行逗号中,请使用以下查询:
SELECT
GROUP_CONCAT(FullName SEPARATOR ', ') AS FullName
FROM
(
SELECT
CONCAT(
t2.name,
" ",
t2.surname,
" (",
CASE
WHEN t2.type = 1 THEN 'User'
WHEN t2.type = 2 THEN 'Admin'
WHEN t2.type = 3 THEN 'SuperAdmin'
ELSE t2.type
END,
")"
) AS FullName
FROM sessions as t1
LEFT JOIN users as t2 ON t1.userid = t2.userid
WHERE
t1.lastaccess IS NOT NULL
AND DATE(FROM_UNIXTIME(t1.lastaccess))=CURDATE()
AND ABS(TIMESTAMPDIFF(MINUTE, FROM_UNIXTIME(t1.lastaccess), NOW())) <= 5
GROUP BY t1.userid, t1.sessionid
ORDER BY FROM_UNIXTIME(t1.lastaccess) DESC
) t;