103 lines
3.3 KiB
SQL
103 lines
3.3 KiB
SQL
WITH
|
|
checks AS (
|
|
SELECT
|
|
h.host AS host_name,
|
|
COALESCE(h.name, h.host) AS visible_name,
|
|
COALESCE(hinv.name, 'unknown') AS equipment_type,
|
|
COALESCE(hinv.notes, '') AS object_number,
|
|
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 coordinates,
|
|
TO_TIMESTAMP(hx.clock) AT TIME ZONE 'Europe/Moscow' AS check_time,
|
|
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
|
|
INNER JOIN history_uint hx
|
|
ON hx.itemid = i.itemid
|
|
LEFT JOIN host_inventory hinv
|
|
ON hinv.hostid = h.hostid
|
|
WHERE
|
|
h.status = 0
|
|
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 UPPER(h.host) NOT LIKE 'IRZ%'
|
|
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')
|
|
),
|
|
|
|
daily_stats AS (
|
|
SELECT
|
|
host_name,
|
|
visible_name,
|
|
equipment_type,
|
|
object_number,
|
|
coordinates,
|
|
DATE(check_time) AS check_date,
|
|
ROUND((SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) * 60.0) / 3600.0, 2) AS available_hours,
|
|
ROUND((SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) * 60.0) / 3600.0, 2) AS unavailable_hours
|
|
FROM checks
|
|
GROUP BY
|
|
host_name,
|
|
visible_name,
|
|
equipment_type,
|
|
object_number,
|
|
coordinates,
|
|
DATE(check_time)
|
|
),
|
|
|
|
daily_graph AS (
|
|
SELECT
|
|
visible_name,
|
|
equipment_type,
|
|
object_number,
|
|
coordinates,
|
|
STRING_AGG(
|
|
TO_CHAR(check_date, 'DD.MM') || ': Доступно ' || available_hours || 'h, Недоступно ' || unavailable_hours || 'h',
|
|
'; ' ORDER BY check_date
|
|
) AS daily_schedule
|
|
FROM daily_stats
|
|
GROUP BY
|
|
visible_name,
|
|
equipment_type,
|
|
object_number,
|
|
coordinates
|
|
),
|
|
|
|
device_summary AS (
|
|
SELECT
|
|
visible_name,
|
|
equipment_type,
|
|
object_number,
|
|
coordinates,
|
|
SUM(CASE WHEN available_hours > 12 THEN 1 ELSE 0 END) AS days_available_gt_12h,
|
|
SUM(CASE WHEN available_hours <= 12 THEN 1 ELSE 0 END) AS days_unavailable_ge_12h
|
|
FROM daily_stats
|
|
GROUP BY
|
|
visible_name,
|
|
equipment_type,
|
|
object_number,
|
|
coordinates
|
|
)
|
|
|
|
SELECT
|
|
ROW_NUMBER() OVER (ORDER BY dg.equipment_type) AS row_number,
|
|
dg.equipment_type AS equipment_type,
|
|
dg.object_number AS object_number,
|
|
dg.coordinates AS coordinates,
|
|
dg.daily_schedule AS daily_schedule,
|
|
ds.days_available_gt_12h AS days_available_more_than_12h,
|
|
ds.days_unavailable_ge_12h AS days_unavailable_12h_or_more
|
|
FROM daily_graph dg
|
|
INNER JOIN device_summary ds
|
|
ON ds.visible_name = dg.visible_name
|
|
ORDER BY dg.object_number; |