[решено] Выбрать последнее значение в сутках за квартал

[Griboed0ff]

Доброго времени суток! У меня снова задача глобальных масштабов. А именно, мне из базы заббикса нужно выгрузить аптаймы машин за квартал, значения на каждый день для каждой машины. По графику забикса видно, что последнее большое значение в сутках отправленное агентом и есть примерный аптайм машины за этот день(ну или примерно так). В таблицах я разобрался и нашел нужные значения, но проблема в том, что по запросу, который я делаю выгружаются все значения за определенный период, а мне нужно только последнее большое значение в каждых сутках в периоде для каждой машины. Я бы мог выгрузить и так, а потом как-то через powershell обработать, но машин 27750 и данных за одни сутки получается ~100 мегабайт, запрос на квартал уходит в длительные раздумья с высокой утилизацией ресурсов сервера. Поэтому нужно выбрать нужные значения с помощью запроса.
SELECT tr.itemid, FROM_UNIXTIME(tr.clock) as date, tr.clock as unixtime,sec_to_time(tr.value_avg) 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
where it.key_ in ('system.uptime')
and gr.hostid=hs.hostid
and it.hostid=hs.hostid
and tr.itemid=it.itemid
and tr.clock BETWEEN '1659843196' and '1660275196';

В списке много машин, но для примера небольшой кусок, где только одна машинаitemid date unixtime uptime value_avg hostid host groupid
1307087 2022-08-10 16:00:00 1660136400 18:34:09.0000 66849.0000 86397 L847 237
1307087 2022-08-10 17:00:00 1660140000 19:34:10.0000 70450.0000 86397 L847 237
1307087 2022-08-10 18:00:00 1660143600 20:34:09.0000 74049.0000 86397 L847 237
1307087 2022-08-10 19:00:00 1660147200 21:34:09.0000 77649.0000 86397 L847 237
1307087 2022-08-10 20:00:00 1660150800 22:34:09.0000 81249.0000 86397 L847 237
1307087 2022-08-10 21:00:00 1660154400 23:34:09.0000 84849.0000 86397 L847 237
1307087 2022-08-10 22:00:00 1660158000 00:34:11.0000 2051.0000 86397 L847 237
1307087 2022-08-10 23:00:00 1660161600 01:34:10.0000 5650.0000 86397 L847 237
1307087 2022-08-11 00:00:00 1660165200 02:34:10.0000 9250.0000 86397 L847 237
1307087 2022-08-11 01:00:00 1660168800 03:34:11.0000 12851.0000 86397 L847 237

Буду рад любой подсказке!

[Sham]

видимо вместо zabbix.trends тоже нужен временный набор из неё с дополнительным полем даты DATE(FROM_UNIXTIME(clock)) по которому всё группировать и делать поле MAX(value_avg) в общем наборе.

[Griboed0ff]

Добавил часовые пояса и стало немного проще. То есть не нужно ловить самое большое значение в сутках, а просто последнее значение для каждой машины в сутках. Как-то нужно по хитрому сгруппировать сначала по компам и дням, а потом выбрать последние значения. МБ как-то: group by WEEKDAY(date_add_tmz), hostid; Совершенно не понимаю группировки, попробую разобраться.
SELECT tr.itemid,
FROM_UNIXTIME(tr.clock) as date,
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,
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 '1660275196';
itemid,date,date_add_tmz,timezone,unixtime,unix_add_tmz,uptime,value_avg,hostid,host,groupid
1307087,"2022-08-08 22:00:00","2022-08-09 01:00:00",6,1659985200,1659985560,00:34:11.0000,2051.0000,86397,F124,237
1307087,"2022-08-08 23:00:00","2022-08-09 02:00:00",6,1659988800,1659989160,01:34:11.0000,5651.0000,86397,F124,237
1307126,"2022-08-09 06:00:00","2022-08-09 10:00:00",7,1660014000,1660014420,09:34:43.0000,34483.0000,86439,R569,237
1307126,"2022-08-09 07:00:00","2022-08-09 11:00:00",7,1660017600,1660018020,10:34:43.0000,38083.0000,86439,R569,237
1307126,"2022-08-09 08:00:00","2022-08-09 12:00:00",7,1660021200,1660021620,11:34:43.0000,41683.0000,86439,R569,237
1307126,"2022-08-09 09:00:00","2022-08-09 13:00:00",7,1660024800,1660025220,12:34:43.0000,45283.0000,86439,R569,237
1307126,"2022-08-09 10:00:00","2022-08-09 14:00:00",7,1660028400,1660028820,13:34:44.0000,48884.0000,86439,R569,237

[Sham]

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

SELECT tr.itemid, tr.real_date, MAX(tr.value_avg) as max_avg, tr.date, tr.unixtime, tr.uptime, tr.value_avg, hs.hostid, hs.host, gr.groupid
FROM
(
SELECT
DATE(FROM_UNIXTIME(`clock`)) AS `real_date`,
`itemid`,
FROM_UNIXTIME(`clock`) as `date`,
`clock` AS `unixtime`,
sec_to_time(`value_avg`) AS `uptime`,
`value_avg`
FROM
zabbix.trends
WHERE
`clock` BETWEEN '1659843196' and '1660275196'
) 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
where it.key_ in ('system.uptime')
and gr.hostid=hs.hostid
and it.hostid=hs.hostid
and tr.itemid=it.itemid
GROUP BY tr.real_date;

группировать по вычисляемому полю вроде нельзя, по крайней мере в 5.7. Если было бы поле только даты в таблице, то другое дело.

[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;

[Griboed0ff]

Осталось перевернуть таблицу, что имена столбцов были даты, первый столбец хост, остальные аптаймы по датам.
host date uptime uptime_sec
T003-2 2022-04-07 0 дн. 23:4:14 83054.0000
I003-3 2022-04-07 0 дн. 12:39:57 45597.0000
C005-2 2022-04-07 0 дн. 20:47:11 74831.0000
M005-7 2022-04-07 0 дн. 23:6:9 83169.0000
K005-8 2022-04-07 0 дн. 23:6:45 83205.0000
H009-2 2022-04-07 0 дн. 22:49:3 82143.0000

[Griboed0ff]

Собрал все в один запрос, но если у вас база большая как у меня 500gb+, то ждите, что вылетят все алерты на время выполнения, начнут ложно срабатывать триггеры, так же сработают действия с триггеров. select up.PC as host,
up.date_add_tmz as date,
max(up.uptime_sec) as uptime_sec,
(select concat(cast(floor(max(up.uptime_sec)/60/60/24) as char(4)),' ', 'дн.', ' ',
cast(floor(mod(max(up.uptime_sec)/60/60/24,1)*24) as char(2)),':',
cast(floor(mod(mod(max(up.uptime_sec)/60/60/24,1)*24,1)*60) as char(2)),':',
cast(round(mod(mod(mod(max(up.uptime_sec)/60/60/24,1)*24,1)*60,1)*60) as char(2)))
from dual) as uptime
from (select hs.host as PC,
DATE(DATE_ADD(FROM_UNIXTIME(clock), INTERVAL (tm.value-3) HOUR)) AS date_add_tmz,
DATE_ADD(FROM_UNIXTIME(clock), INTERVAL (tm.value-3) HOUR) as datetime_add_tmz,
value_avg as uptime_sec
from zabbix.trends as tr,
(SELECT * FROM zabbix.hostmacro where macro='{$TIMEZONE}' and value <> '') as tm,
(SELECT distinct hostid, host FROM zabbix.hosts) as hs,
zabbix.items as it,
(select * from zabbix.hosts_groups where groupid in (225, 227, 229, 231, 233, 235, 237, 239)) as gr
where clock BETWEEN 1660251600 and 1660481705
and hs.hostid=tm.hostid
and tr.itemid=it.itemid
and gr.hostid=hs.hostid
and it.hostid=hs.hostid
and it.key_ in ('system.uptime')) as up where datetime_add_tmz NOT LIKE '%00:00:00' group by date, host order by host;
По перестройке таблицы пока решения не нашел.