Имя пользователя:
Пароль:  
Помощь | Регистрация | Забыли пароль?  | Правила  

Компьютерный форум OSzone.net » Программирование, базы данных и автоматизация действий » Программирование и базы данных » MySQL - [решено] Выбрать последнее значение в сутках за квартал

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

Старожил


Сообщения: 211
Благодарности: 2

Профиль | Отправить PM | Цитировать


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

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

Отправлено: 13:12, 12-08-2022

 

Deadooshka


Сообщения: 2480
Благодарности: 667

Профиль | Отправить PM | Цитировать


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

Отправлено: 03:13, 13-08-2022 | #2



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

Если же вы забыли свой пароль на форуме, то воспользуйтесь данной ссылкой для восстановления пароля.


Старожил


Сообщения: 211
Благодарности: 2

Профиль | Отправить PM | Цитировать


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

Отправлено: 11:42, 13-08-2022 | #3


Deadooshka


Сообщения: 2480
Благодарности: 667

Профиль | Отправить PM | Цитировать


не, идея примерно такая
Скрытый текст
Код: Выделить весь код
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. Если было бы поле только даты в таблице, то другое дело.
Это сообщение посчитали полезным следующие участники:

Отправлено: 16:37, 13-08-2022 | #4


Старожил


Сообщения: 211
Благодарности: 2

Профиль | Отправить PM | Цитировать


Цитата 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 в 21:28.


Отправлено: 20:30, 13-08-2022 | #5


Старожил


Сообщения: 211
Благодарности: 2

Профиль | Отправить PM | Цитировать


Осталось перевернуть таблицу, что имена столбцов были даты, первый столбец хост, остальные аптаймы по датам.
Таблица
Код: Выделить весь код
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

Отправлено: 23:54, 13-08-2022 | #6


Старожил


Сообщения: 211
Благодарности: 2

Профиль | Отправить PM | Цитировать


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

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

Последний раз редактировалось Griboed0ff, 14-08-2022 в 19:40.


Отправлено: 16:16, 14-08-2022 | #7



Компьютерный форум OSzone.net » Программирование, базы данных и автоматизация действий » Программирование и базы данных » MySQL - [решено] Выбрать последнее значение в сутках за квартал

Участник сейчас на форуме Участник сейчас на форуме Участник вне форума Участник вне форума Автор темы Автор темы Шапка темы Сообщение прикреплено

Похожие темы
Название темы Автор Информация о форуме Ответов Последнее сообщение
PowerShell - [решено] Мониторы, выбрать нужное значение. Griboed0ff Скриптовые языки администрирования Windows 10 10-02-2021 12:34
Статистика планшетов за 4-й квартал 2016 OSZone News Новости железа 0 03-02-2017 09:30
Статистика видеокарт за 4-й квартал 2015 OSZone News Новости железа 0 02-03-2016 20:30
CMD/BAT - [решено] Последнее значение в строке ncmps Скриптовые языки администрирования Windows 5 30-06-2015 12:06
Что за производитель модема и где скачать последнее драйвера для него? Михайло Поиск драйверов, прошивок и руководств 7 15-04-2013 16:39




 
Переход