8 Replies Latest reply on Mar 10, 2015 10:58 AM by luludev

    Add Minutes to the outage...

    luludev

      Hi....Team...


      I was  trying to add outage duration in minutes to my report for last 30 days outage...however went unsuccessful....appreciate if you can help me :)....

       

      Below is my SQL for the report....please advise what i need to add...

       

      Nodes.OfficeName AS OfficeName,

      Nodes.City AS City,

      Nodes.IP_Address AS IP_Address,

      Events_EventTypes.Name AS Event_Type_Name

       

      FROM

      Nodes INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)

       

       

      WHERE

      ( EventTime BETWEEN 42034 AND 42061 )

      AND 

      (

        (Nodes.NetworkPlatform = 'British Telecom') AND

        (

        (Events_EventTypes.Name = 'Node Down') OR

        (Events_EventTypes.Name = 'Node Up'))

      )

       

       

       

       

      ) As r ORDER BY 2 ASC, 3 DESC

        • Re: Add Minutes to the outage...
          jeremyxmentzell

          My SQL is pretty bad - but I'm trying... Here's what I'm using that sounds similar to what you're trying to accomplish though our properties might be skewed a bit.

           

          "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

          (Nodes.Admin_Owner = 'NOC')

          AND

          (StartTime.EventType = 1)

          AND

          (StartTime.NetObjectType = 'N')

          AND

          eventtime between dateadd(month, -1, getdate()) and getdate()

          ORDER BY Nodes.Caption ASC"

           

          I could probably export and put on content exchange if that's easier? Lemme know - hope this helps!

          • Re: Add Minutes to the outage...
            luludev

            Thanks Jeremy...

             

            But this generates reports for all the nodes ,i need to filter only the nodes under British Telecom....how could i do that

            • Re: Add Minutes to the outage...
              luludev

              "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

              (Nodes.Admin_Owner = 'NOC')

              AND

              (StartTime.EventType = 1)

              AND

              (StartTime.NetObjectType = 'N')

              AND

              eventtime between dateadd(month, -1, getdate()) and getdate()

              WHERE

              ( EventTime BETWEEN 42034 AND 42061 )

              AND

              (

                (Nodes.NetworkPlatform = 'British Telecom') AND

                (

                (Events_EventTypes.Name = 'Node Down') OR

                (Events_EventTypes.Name = 'Node Up'))

              )

               

               

               

               

              )

              ORDER BY Nodes.Caption ASC"

               

              Hi Jeremy....u mean like the above.....i get a syntax error though

              • Re: Add Minutes to the outage...
                LadaVarga

                Hello,

                 

                I recommended look here:

                Node Downtime Reports

                 

                There is downtime in minutes.

                 

                Thanks Lada

                • Re: Add Minutes to the outage...
                  luludev

                  Thanks Lada...

                  The provided seems to greek for me I am not an SQL expert....

                  • Re: Add Minutes to the outage...
                    luludev

                    Thanks Jeremy....appreciate your help...