Outage Duration Report - SQL query help needed

upinsmoke

Hi All,

 

I am running a 7 day report that gives me outages for the week for main sites and also gives the outage duration. Obviously if I have nodes that have been down for over 7 days, they do not show in this report.

 

Can anyone help me to get the nodes that are already down captured in this as well.

I'm running Orion 9.1 SP5 and SQL 2005 Enterprise

SQL query;

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') AND
eventtime between dateadd(week, -1, getdate()) and getdate()
AND Nodes.Bases='Base'
AND Nodes.Type='Router'
ORDER BY OutageDurationInMinutes DESC

 

Thanks in advance,

 

Ben