Hello ,
I have report for downtime servers with SQL and need to add more column table that display custom property device type
SELECT * from (
SELECT
StartTime.EventTime AS Down_Event_time,
(SELECT TOP 1
EventTime
FROM Events AS Endtime
WHERE EndTime.EventTime >= StartTime.EventTime
AND EndTime.EventType = 5
AND EndTime.NetObjectType = 'N'
AND EndTime.NetObjectID = StartTime.NetObjectID
AND EventTime IS NOT NULL
ORDER BY EndTime.EventTime) AS Up_Event_Time, Nodes.Caption, Nodes.IP_Address
-- Downtime duration in Minutes. Based on either the current time (node is still down) or Up Event time (node is back up)
,CASE
WHEN
(
SELECT TOP 1 EventTime
FROM Events AS Endtime
WHERE EndTime.EventTime >= StartTime.EventTime
AND EndTime.EventType = 5
AND EndTime.NetObjectType = 'N'
AND EndTime.NetObjectID = StartTime.NetObjectID
AND EventTime IS NOT NULL
ORDER BY EndTime.EventTime
)
IS NULL
THEN
ROUND
(
(DATEDIFF(Minute, StartTime.EventTime, GETUTCDATE())),2
)
ELSE
ROUND
(
(DATEDIFF
(Minute, StartTime.EventTime,
(
SELECT TOP 1 EventTime
FROM Events AS EndTime
WHERE EndTime.EventTime > StartTime.EventTime
AND EndTime.EventType = 5
AND EndTime.NetObjectType = 'N'
AND EndTime.NetObjectID = StartTime.NetObjectID
ORDER BY EndTime.EventTime
)
)
),2
)
END AS Minutes
-- Downtime duration in Hours. Based on either the current time (node is still down) or Up Event time (node is back up)
,CASE
WHEN
(
SELECT TOP 1 EventTime
FROM Events AS Endtime
WHERE EndTime.EventTime >= StartTime.EventTime
AND EndTime.EventType = 5
AND EndTime.NetObjectType = 'N'
AND EndTime.NetObjectID = StartTime.NetObjectID
AND EventTime IS NOT NULL
ORDER BY EndTime.EventTime
)
IS NULL
THEN
ROUND
(
(DATEDIFF(Minute, StartTime.EventTime, GETUTCDATE())/60.0),2
)
ELSE
ROUND
(
(DATEDIFF
(Minute, StartTime.EventTime,
(SELECT TOP 1 EventTime
FROM Events AS EndTime
WHERE EndTime.EventTime > StartTime.EventTime
AND EndTime.EventType = 5
AND EndTime.NetObjectType = 'N'
AND EndTime.NetObjectID = StartTime.NetObjectID
ORDER BY EndTime.EventTime
)
)/60.0
),3
)
END AS Hours
FROM Events StartTime INNER JOIN Nodes ON StartTime.NetObjectID = Nodes.NodeID
WHERE (StartTime.EventType = 1)
) as uptimetable
WHERE Minutes IS NOT NULL
(
SELECT CONCAT(ROUND(AVG(Availability), 2), ' %' ) AS [Yes]
FROM Orion.ResponseTime AS [RT]
WHERE [RT].Node.CustomProperties.Servers_Availability Like '%Yes%'
)
ORDER BY Down_Event_time desc
