This snippet of code extracts a list of device outages from a LibreNMS database that are longer than 20 minutes, over the previous 12 month period. The results display the downtime in hours and minutes.
The 20 minute duration was added to counter situations where polling failed or where a single outage resulted in multiple up/down events. You can adjust this to suit your needs on line 15 or omit it entirely if preferred.
SELECT
d.sysname,
FROM_UNIXTIME(o.going_down) AS down_time,
FROM_UNIXTIME(o.up_again) AS up_time,
CONCAT(
FLOOR(TIMESTAMPDIFF(SECOND, FROM_UNIXTIME(o.going_down), FROM_UNIXTIME(o.up_again)) / 3600), ' hours ',
FLOOR((TIMESTAMPDIFF(SECOND, FROM_UNIXTIME(o.going_down), FROM_UNIXTIME(o.up_again)) % 3600) / 60), ' minutes'
) AS outage_duration
FROM
device_outages o
JOIN
devices d ON o.device_id = d.device_id
WHERE
o.going_down >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR))
AND TIMESTAMPDIFF(SECOND, FROM_UNIXTIME(o.going_down), FROM_UNIXTIME(o.up_again)) > 1200
ORDER BY
o.going_down DESC;