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

            • Re: Need a Regional Average report
              patriot

              Thanks for the help zackm.

               

              I would prefer to get this done using Manage Reports, but I could not see how to get the tool to calculate the average for each Region based on the values for the nodes in the Region. Can you advise?

              • Re: Need a Regional Average report
                patriot

                Also, if I want to use the Report Writer, how would I combine the two separate reports into one? Ideally, I would like to have a report that shows the nodes grouped by Region with the average availability for each node and the average for the region as well. Thanks again for your help.