Is there any way to create a report to show outage time?
Node Downtime
Core_1 15:34
Is there any way to change this code to get data only for the Down nodes ?
SELECT StartTime.EventTime, Nodes.Caption, Nodes.Location,StartTime.Message, DATEDIFF(Mi, 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.NetworkNode = StartTime.NetworkNode ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes
FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N')ORDER BY StartTime.EventTime DESC
Just but there join to nodes table along NetworkNode column and NodeID and condition Status = 2
SELECT * FROM Events e
INNER JOIN Nodes n ON e.NetworkNode = n.NodeId
WHERE n.Status = 2
Please help me to create this report, I´m not a SQL expert.
Just add condition for node Status
FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND Nodes.Status = 2ORDER BY StartTime.EventTime DESC
that's all , but if you create this via Orion Report Writer, you need to add Filter Result Condition for [Network Nodes]->[Node Status]->[Status]. And let it equal to Down.
