3 Replies Latest reply on Jul 8, 2015 11:25 AM by dixter

    Uptime greater than 1 year report

    dixter

      I was hoping someone might be able to help me figure this one out.  I need to create a weekly report that contains all nodes which have an uptime (or last boot time) greater than one year.  Between the uptime tick conversion and the required greater than one year condition, I'm not sure where to even start the report for this.  SQL queries are not exactly my forte, but could probably figure it out if someone were to give me a little direction.

       

      Thanks in advance!!

        • Re: Uptime greater than 1 year report
          zackm

          If you only care about the last boot time, this will take care of your needs (with the understanding that the metric is not available for ICMP-only devices)

           

          SELECT *
          FROM dbo.Nodes
          WHERE LastBoot < DATEADD(year, -1, GetDate())
          
          

           

          If you need a report that shows all devices with 100% availability for the last 1 year, that's something different. Basically, since Orion only polls for availability every 120 seconds by default, it is theoretically possible to have a device restart in between polling cycles and still show 100% availability. Additionally, the default data retention for these metrics is only 1 year, so to have a report showing more than 1 year, you need to increase your daily statistics retention to > 365. (Settings > Polling Settings)

           

          For a report like that, you could use something more like this:

           

          SELECT
            n.Caption
            ,n.IP_Address
            ,MIN(r.[Availability]) as Minimum_Availability
          FROM ResponseTime r
          JOIN Nodes n on n.NodeID = r.NodeID
          GROUP BY n.Caption,n.IP_Address
          HAVING MIN(r.[Availability]) = 100
          
          

           

          Ideally, you would want both of these queries as separate tables in one web report so you have full visibility into all of your metrics.

           

          -ZackM

          Loop1 Systems: SolarWinds Training and Professional Services