3 Replies Latest reply on Nov 15, 2013 11:08 AM by oliver.grist

    Pulling availability stats

    oliver.grist

      Hello,

       

      I have downloaded the SWQL studio and managed to run a number of queries successfully, allowing us to use the NPM data across our other internal systems.

      * Apologies if this is not the correct place its a NPM API question

       

      But I have got stumped on the Availability table that appears on both nodes and applications.

       

      I have got as far as the Orion.NPM.EW.DeviceStats table/view but there seems to be a mixture of results (minute by minute, hourly and daily all combined some of which have an "Available" and "Available Percent" others are just blank).

       

      The query I have for now is:

      SELECT NodeID, Available, AvailablePercent, ObservationTimestamp

      FROM Orion.NPM.EW.DeviceStats

      WHERE NodeID = 1740

      ORDER BY ObservationTimestamp

       

      Essentially I just want to recreate the table, is there a better way to go about this:

       

      Availability Statistics

      PERIOD     AVAILABILITY

      Today    100.000 %

      Yesterday    100.000 %

      Last 7 Days    100.000 %

      Last 30 Days    100.000 %

      This Month    100.000 %

      Last Month    100.000 %

      This Year    100.000 %

       

      Am I missing something obvious?

       

      Many thanks,

      Oli

        • Re: Pulling availability stats
          Andy McBride

          Repost in in Product Forums -> Labs -> Orion SDK Lab for better traction.

          • Re: Pulling availability stats
            oliver.grist

            From hacking around and browsing the forums I made some progress - not the enitre table but I am able to pull some of it:

             

            What I have for now is:

             

            SELECT NodeID,

            (SELECT AVG(Availability) as Availability FROM Orion.ResponseTime WHERE Nodes.NodeId = ResponseTime.NodeID AND ObservationTimestamp > '15/11/2013 00:00:00') as AvailabilityToday,

            (SELECT AVG(Availability) as Availability FROM Orion.ResponseTime WHERE Nodes.NodeId = ResponseTime.NodeID AND ObservationTimestamp > '14/11/2013 00:00:00' AND ObservationTimestamp < '15/11/2013 00:00:00') as AvailabilityYesterday,

            (SELECT AVG(Availability) as Availability FROM Orion.ResponseTime WHERE Nodes.NodeId = ResponseTime.NodeID AND ObservationTimestamp > '07/11/2013 00:00:00' AND ObservationTimestamp < '15/11/2013 00:00:00') as AvailabilityLast7days

            FROM Orion.Nodes

             

            I took and combined some ideas from these 2 theads:

            http://thwack.solarwinds.com/thread/35627

            SWQL Help

              • Re: Pulling availability stats
                oliver.grist

                The final code I have ended up with is as follows, the dates being set in PHP.

                 

                This solves the issue of devices having different amounts of historic data, it is slightly slow when run across the entire dataset .

                 

                SELECT NodeID, NCP.MIST_OBJECT_ID, (SELECT SUM(Availability*Weight)/SUM(Weight) as Availability FROM Orion.ResponseTime WHERE Nodes.NodeId = ResponseTime.NodeID AND ObservationTimestamp >= '15/11/2013 00:00:00') as AvailabilityToday,

                (SELECT SUM(Availability*Weight)/SUM(Weight) as Availability FROM Orion.ResponseTime WHERE Nodes.NodeId = ResponseTime.NodeID AND ObservationTimestamp >= '14/11/2013 00:00:00' AND ObservationTimestamp <= '15/11/2013 00:00:00') as AvailabilityYesterday,

                (SELECT SUM(Availability*Weight)/SUM(Weight) as Availability FROM Orion.ResponseTime WHERE Nodes.NodeId = ResponseTime.NodeID AND ObservationTimestamp >= '08/11/2013 00:00:00') as AvailabilityLast7days,

                (SELECT SUM(Availability*Weight)/SUM(Weight) as Availability FROM Orion.ResponseTime WHERE Nodes.NodeId = ResponseTime.NodeID AND ObservationTimestamp >= '15/10/2013 00:00:00') as AvailabilityLast30days,

                (SELECT SUM(Availability*Weight)/SUM(Weight) as Availability FROM Orion.ResponseTime WHERE Nodes.NodeId = ResponseTime.NodeID AND ObservationTimestamp >= '15/08/2013 00:00:00') as AvailabilityLast90days,

                (SELECT SUM(Availability*Weight)/SUM(Weight) as Availability FROM Orion.ResponseTime WHERE Nodes.NodeId = ResponseTime.NodeID AND ObservationTimestamp >= '01/01/2013 00:00:00') as AvailabilityThisYear

                FROM Orion.Nodes

                INNER JOIN Orion.NodesCustomProperties NCP ON Nodes.NodeID = NCP.NodeID