Downtime per MPLS and/or Metro-E circuit in minutes.

r0berth1

I am looking for help in writing a report that will look for the custom interface property of Metro-E and/or MPLS and reports every instance that the interface went down for longer than an hour for the last month. Also need it to display the duration of downtime in minutes. Can someone help me with this. I have seen a few reports out there but none really apply and i dont know enough SQL to modify them.

r0berth1

Here is what i finally came up with. I had to create a custom node property called Nodes.Branch = Branch, and 2 custom interfaces properties Interfaces.MPLS = MPLS and Interfaces.METRO_E = METRO-E. 

SELECT     StartTime.EventTime, dbo.Nodes.Branch, StartTime.Message, DATEDIFF(Mi, StartTime.EventTime,

                          (SELECT     TOP (1) EventTime

                            FROM          dbo.Events AS Endtime

                            WHERE      (EventTime > StartTime.EventTime) AND (EventType = 5) AND (NetObjectType = 'N') AND (NetworkNode = StartTime.NetworkNode)

                            ORDER BY EventTime)) AS OutageDurationInMinutes

FROM         dbo.Events AS StartTime INNER JOIN

                      dbo.Nodes ON StartTime.NetworkNode = dbo.Nodes.NodeID INNER JOIN

                      dbo.Interfaces ON dbo.Nodes.NodeID = dbo.Interfaces.NodeID

WHERE     (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND (StartTime.EventTime BETWEEN DATEADD(month, - 3, GETDATE()) AND GETDATE())

                       AND (dbo.Interfaces.MPLS = 'MPLS') OR

                      (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND (StartTime.EventTime BETWEEN DATEADD(month, - 3, GETDATE()) AND GETDATE())

                       AND (dbo.Interfaces.METRO_E = 'METRO-E') AND (dbo.Nodes.Branch = 'Branch')

ORDER BY dbo.Nodes.Branch