3 Replies Latest reply on Oct 1, 2014 9:00 AM by patriot

    Need a Regional Average report

    patriot

      I'm not a SQL guru, so I would really appreciate the help of the community on this one.

       

      I have all of my nodes assigned to a Region and my manager wants to see a report showing the average availability of each node in a Region over the last 7 days, AND a single average availability value for each Region over the same period. What would the SQL look like for that?

       

      One other question. That same manager prefers to see charts rather than tables. Is is possible to display a chart showing the single average availability for a node or for a Region as a bar chart rather than a table?

       

      Thank you very much for your help.

        • Re: Need a Regional Average report
          zackm

          This should get you pointed in the right direction if you really want to use SQL (but I would HIGHLY recommend just making this in the web report writer). Note that it may not be 100% perfect, my test DB is down for maintenance right now.

           

          To answer you 2nd question, you should be able to chart the devices in a line chart using the web report writer.

           

          OVERALL AVG:

          SELECT
          AVG(r.Availability) as 'AVG Availability'
          FROM
          ResponseTime r
          JOIN
          Nodes n ON n.nodeid=r.nodeid
          WHERE
          DateTime > (GetDate()-7)
          AND
          --Change the line below to reflect your custom property name and region--
          n.CustomProperty = 'Region'
          

           

          AVG BY NODE:

          SELECT
          AVG(r.Availability) as 'AVG Availability'
          ,n.Caption as Device
          FROM
          ResponseTime r
          JOIN
          Nodes n ON n.nodeid=r.nodeid
          WHERE
          DateTime > (GetDate()-7)
          AND
          --Change the line below to reflect your custom property name and region--
          n.CustomProperty = 'Region'
          GROUP BY
          n.Caption
          

           

           

           

          -ZackM

          Loop1 Systems: SolarWinds Training and Professional Services