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

Компьютерный форум OSzone.net (http://forum.oszone.net/index.php)

- Программирование и базы данных (http://forum.oszone.net/forumdisplay.php?f=21)

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

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

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

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

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

Код:

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

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

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

Код:

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. Если было бы поле только даты в таблице, то другое дело.

Цитата:

Цитата 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;

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

Таблица

Код:

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

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

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