[Griboed0ff]

идея примерно такая »

К сожалению вывод информации только одного хоста. Я для упрощения создал отдельную табличку из своего запроса.

Скрытый текст

Код:

SELECT tr.itemid, 
DATE(FROM_UNIXTIME(tr.clock)) AS real_date,
FROM_UNIXTIME(tr.clock) as datetime, 
DATE_ADD(FROM_UNIXTIME(tr.clock), INTERVAL (tm.value-3) HOUR) as date_add_tmz, 
tm.value as timezone, 
tr.clock as unixtime, 
tm.value*60 + tr.clock as unix_add_tmz, 
sec_to_time(tr.value_avg) as uptime,
(select concat(cast(floor(tr.value_avg/60/60/24) as char(3)),' ', 'дн.', ' ',
cast(floor(mod(tr.value_avg/60/60/24,1)*24) as char(2)),':',
cast(floor(mod(mod(tr.value_avg/60/60/24,1)*24,1)*60) as char(2)),':',
cast(round(mod(mod(mod(tr.value_avg/60/60/24,1)*24,1)*60,1)*60) as char(2)))
from dual) as uptime, 
tr.value_avg, 
hs.hostid, 
hs.host, 
gr.groupid
FROM 
zabbix.trends as tr,
zabbix.items as it,
(SELECT distinct hostid, host FROM zabbix.hosts) as hs,
(select * from zabbix.hosts_groups where groupid in ('225', '227', '229', '231', '233', '235', '237', '239')) as gr,
(SELECT * FROM zabbix.hostmacro where macro in  ('{$TIMEZONE}') and value <> '') as tm
where it.key_ in ('system.uptime') 
and gr.hostid=hs.hostid 
and it.hostid=hs.hostid
and tr.itemid=it.itemid
and hs.hostid=tm.hostid
and tr.clock BETWEEN '1659843196' and (SELECT unix_timestamp(now()));

Теперь по запросу

Код:

SELECT * FROM zabbix.uptime_tt;

получуаю

Код:

itemid	date	datetime	date_add_tmz	datetime_add_tmz	timezone	unixtime	unix_add_tmz	uptime	value_avg	hostid	host	groupid
1308405	2022-08-07	2022-08-07 07:00:00	2022-08-07	2022-08-07 09:00:00	5	1659844800	1659845100	0 дн. 0:50:19	3019.0000	87973	P975	235
1308405	2022-08-07	2022-08-07 08:00:00	2022-08-07	2022-08-07 10:00:00	5	1659848400	1659848700	0 дн. 1:50:19	6619.0000	87973	P975	235
1308405	2022-08-07	2022-08-07 09:00:00	2022-08-07	2022-08-07 11:00:00	5	1659852000	1659852300	0 дн. 2:50:19	10219.0000	87973	P975	235
1308405	2022-08-07	2022-08-07 10:00:00	2022-08-07	2022-08-07 12:00:00	5	1659855600	1659855900	0 дн. 3:50:19	13819.0000	87973	P975	235
1308405	2022-08-07	2022-08-07 11:00:00	2022-08-07	2022-08-07 13:00:00	5	1659859200	1659859500	0 дн. 4:50:19	17419.0000	87973	P975	235
1308405	2022-08-07	2022-08-07 12:00:00	2022-08-07	2022-08-07 14:00:00	5	1659862800	1659863100	0 дн. 5:50:19	21019.0000	87973	P975	235
1308405	2022-08-07	2022-08-07 13:00:00	2022-08-07	2022-08-07 15:00:00	5	1659866400	1659866700	0 дн. 6:50:19	24619.0000	87973	P975	235
1308405	2022-08-07	2022-08-07 14:00:00	2022-08-07	2022-08-07 16:00:00	5	1659870000	1659870300	0 дн. 7:50:18	28218.0000	87973	P975	235
1308405	2022-08-07	2022-08-07 15:00:00	2022-08-07	2022-08-07 17:00:00	5	1659873600	1659873900	0 дн. 8:50:19	31819.0000	87973	P975	235

потом таким запросом вытянул нужное. В 00 часов пришлось убрать так как там число уже меняется, а аптайм все еще за прошедшие сутки.

Скрытый текст

Код:

SELECT host, 
date_add_tmz as date, 
(select concat(cast(floor(max(value_avg)/60/60/24) as char(4)),' ', 'дн.', ' ',
cast(floor(mod(max(value_avg)/60/60/24,1)*24) as char(2)),':',
cast(floor(mod(mod(max(value_avg)/60/60/24,1)*24,1)*60) as char(2)),':',
cast(round(mod(mod(mod(max(value_avg)/60/60/24,1)*24,1)*60,1)*60) as char(2)))
from dual) as uptime,  
max(value_avg) as uptime_sec
FROM zabbix.uptime_tt where datetime_add_tmz NOT LIKE '%00:00:00' group by date, host order by host;