120 lines
5.3 KiB
SQL
120 lines
5.3 KiB
SQL
-- =============================================================================
|
|
-- Отчет повременного учета работоспособности периферийного оборудования
|
|
-- Период: май 2026
|
|
-- Исключены устройства, начинающиеся на IRZ
|
|
-- Тип оборудования берется из инвентаризации Zabbix (поле type)
|
|
-- Координаты: широта + долгота из полей location_lat и location_lon
|
|
-- =============================================================================
|
|
|
|
WITH
|
|
-- 1. Получаем все проверки ICMP за период
|
|
checks AS (
|
|
SELECT
|
|
h.host AS "Устройство",
|
|
COALESCE(h.name, h.host) AS "Видимое имя",
|
|
-- Тип оборудования из инвентаризации (поле type), если пусто - берем hardware
|
|
COALESCE(hinv."name", 'не указан') AS "Тип оборудования",
|
|
-- Номер объекта ИТС РО (храним в поле notes инвентаризации)
|
|
COALESCE(hinv.notes, '') AS "Номер_объекта_ИТС_РО",
|
|
-- Координаты: объединяем широту и долготу через пробел
|
|
CASE
|
|
WHEN hinv.location_lat IS NOT NULL AND hinv.location_lon IS NOT NULL
|
|
AND hinv.location_lat != '' AND hinv.location_lon != ''
|
|
THEN hinv.location_lat || ' ' || hinv.location_lon
|
|
WHEN hinv.location_lat IS NOT NULL AND hinv.location_lat != ''
|
|
THEN hinv.location_lat
|
|
WHEN hinv.location_lon IS NOT NULL AND hinv.location_lon != ''
|
|
THEN hinv.location_lon
|
|
ELSE ''
|
|
END AS "Координаты_расположения",
|
|
-- Время проверки с учетом часового пояса
|
|
TO_TIMESTAMP(hx.clock) AT TIME ZONE 'Europe/Moscow' AS "ts",
|
|
-- Статус: 1 = доступно, 0 = недоступно
|
|
hx.value AS "status"
|
|
FROM hosts h
|
|
INNER JOIN items i
|
|
ON i.hostid = h.hostid
|
|
AND i.key_ = 'icmpping'
|
|
AND i.status = 0
|
|
AND i.value_type = 3 -- unsigned numeric
|
|
INNER JOIN history_uint hx
|
|
ON hx.itemid = i.itemid
|
|
LEFT JOIN host_inventory hinv
|
|
ON hinv.hostid = h.hostid
|
|
WHERE
|
|
-- Только мониторимые хосты
|
|
h.status = 0
|
|
-- Исключаем оборудование, начинающееся на IRZ (любой регистр)
|
|
--AND UPPER(h.host) NOT LIKE 'IRZ%'
|
|
-- Период: май 2026 года (измените под свои даты)
|
|
--and hinv.notes = 'ОБЪЕКТ № 9'
|
|
and CAST(nullif(regexp_replace(hinv.notes, 'ОБЪЕКТ №\s*', '', 'g'),'') as INTEGER)
|
|
in (44,77,43,54,27,48,12,49,65,9,60,35,38,1,51,41,53,84,8)
|
|
AND hx.clock >= EXTRACT(EPOCH FROM TIMESTAMP '2026-05-25 00:00:00')
|
|
AND hx.clock < EXTRACT(EPOCH FROM TIMESTAMP '2026-06-01 00:00:00')
|
|
),
|
|
|
|
-- 2. Добавляем предыдущий статус для определения моментов смены
|
|
checks_with_prev AS (
|
|
SELECT
|
|
*,
|
|
LAG("status") OVER (PARTITION BY "Устройство" ORDER BY "ts") AS "prev_status"
|
|
FROM checks
|
|
),
|
|
|
|
-- 3. Определяем моменты смены статуса (начало нового интервала)
|
|
status_changes AS (
|
|
SELECT
|
|
*,
|
|
CASE
|
|
WHEN "prev_status" IS NULL OR "status" != "prev_status" THEN 1
|
|
ELSE 0
|
|
END AS "is_start"
|
|
FROM checks_with_prev
|
|
),
|
|
|
|
-- 4. Нумеруем интервалы для каждого устройства
|
|
intervals AS (
|
|
SELECT
|
|
*,
|
|
SUM("is_start") OVER (PARTITION BY "Устройство" ORDER BY "ts") AS "interval_id"
|
|
FROM status_changes
|
|
WHERE "status" IS NOT NULL
|
|
),
|
|
|
|
-- 5. Агрегируем каждый непрерывный интервал
|
|
grouped_intervals AS (
|
|
SELECT
|
|
"Устройство",
|
|
"Видимое имя",
|
|
"Тип оборудования",
|
|
"Номер_объекта_ИТС_РО",
|
|
"Координаты_расположения",
|
|
MIN("ts") AS "time_start",
|
|
MAX("ts") AS "time_end",
|
|
MAX("status") AS "status"
|
|
FROM intervals
|
|
GROUP BY
|
|
"Устройство",
|
|
"Видимое имя",
|
|
"Тип оборудования",
|
|
"Номер_объекта_ИТС_РО",
|
|
"Координаты_расположения",
|
|
"interval_id"
|
|
)
|
|
|
|
-- 6. Финальный вывод отчета
|
|
SELECT
|
|
ROW_NUMBER() OVER (ORDER BY "Устройство", "time_start") AS "№ п/п",
|
|
TO_CHAR(DATE("time_start"), 'DD.MM.YYYY') AS "Дата",
|
|
"Тип оборудования",
|
|
"Номер_объекта_ИТС_РО",
|
|
"Координаты_расположения",
|
|
TO_CHAR("time_start", 'HH24:MI:SS') AS "Время начала статуса",
|
|
TO_CHAR("time_end", 'HH24:MI:SS') AS "Время окончания статуса",
|
|
CASE
|
|
WHEN "status" = 1 THEN 'доступно'
|
|
ELSE 'недоступно'
|
|
END AS "Статус"
|
|
FROM grouped_intervals
|
|
ORDER BY "Номер_объекта_ИТС_РО"; |