Компьютерный форум OSzone.net  

Компьютерный форум OSzone.net (http://forum.oszone.net/index.php)
-   Программирование и базы данных (http://forum.oszone.net/forumdisplay.php?f=21)
-   -   [решено] Выбрать последнее значение в сутках за квартал (http://forum.oszone.net/showthread.php?t=351715)

Griboed0ff 12-08-2022 13:12 2990270

Выбрать последнее значение в сутках за квартал
 
Доброго времени суток! У меня снова задача глобальных масштабов. А именно, мне из базы заббикса нужно выгрузить аптаймы машин за квартал, значения на каждый день для каждой машины. По графику забикса видно, что последнее большое значение в сутках отправленное агентом и есть примерный аптайм машины за этот день(ну или примерно так). В таблицах я разобрался и нашел нужные значения, но проблема в том, что по запросу, который я делаю выгружаются все значения за определенный период, а мне нужно только последнее большое значение в каждых сутках в периоде для каждой машины. Я бы мог выгрузить и так, а потом как-то через 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 13-08-2022 03:13 2990320

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

Griboed0ff 13-08-2022 11:42 2990334

Добавил часовые пояса и стало немного проще. То есть не нужно ловить самое большое значение в сутках, а просто последнее значение для каждой машины в сутках. Как-то нужно по хитрому сгруппировать сначала по компам и дням, а потом выбрать последние значения. МБ как-то:
Код:

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 13-08-2022 16:37 2990359

не, идея примерно такая
Скрытый текст
Код:

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 13-08-2022 20:30 2990376

Цитата:

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

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

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 13-08-2022 23:54 2990383

Осталось перевернуть таблицу, что имена столбцов были даты, первый столбец хост, остальные аптаймы по датам.
Таблица
Код:

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 14-08-2022 16:16 2990415

Собрал все в один запрос, но если у вас база большая как у меня 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;


По перестройке таблицы пока решения не нашел.


Время: 01:12.

Время: 01:12.
© OSzone.net 2001-