3 Replies Latest reply on Mar 19, 2009 4:01 PM by r0berth1

    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.

        • Re: Downtime per MPLS and/or Metro-E circuit in minutes.
          r0berth1

          There has to be a SQL guru out there that has eigher already done this and can point me to the correct location or help me with this.

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

            I have a similiar need for this too... and unfortunately I not an expert with SQL..

            • Re: Downtime per MPLS and/or Metro-E circuit in minutes.
              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