8 Replies Latest reply on Jun 9, 2014 8:23 AM by rstoney00

    HELP with the sql Query on report writer

    aimy.k

      I'm not an expert in SQL ,i need some help with the sql query at Solarwinds Report Writer ... Actually , i want to generate a report with this exact informations ( IP_Address, CPULoad, Last Boot , and Availibility )

      The problem is that , th CPU Load and last boot are in the same sql query , but when i want to add the AVAILIBILITY chart , i can't because it's in another TYPE of report ...

      PLEASe i Need some HELP . if someone could show me how to create an sql query to preview all this informations in only one chart .

        • Re: HELP with the sql Query on report writer
          stuartwhyte

          Hi,

           

          For CPU and availability - over what time frame are you looking for?  I expect you are looking for the average over this timeframe?

           

          Stuart

          • Re: HELP with the sql Query on report writer
            zackm

            stuartwhyte is correct; this will be a very expensive report in regards to cycles on your SQL box.

             

            In your other question about this (Re: How to display polling Engine as well as Other node information?) you mentioned you wanted PEAK CPU, This will get you the PEAK CPU load from any one polling cycle out of the last 7 days, along with the AVG Availability from all polling cycles over the same time period:

             

            select
              n.nodeid
              ,n.caption as 'DEVICE'
              ,n.ip_address as 'IP ADDRESS'
              ,n.lastboot as 'LAST BOOT'
              ,max(c.maxload) as 'PEAK CPU'
              ,avg(r.availability) as 'AVG AVAILABILITY'
            from nodes n
            join cpuload c on n.nodeid=c.nodeid
            join responsetime r on n.nodeid=r.nodeid
            where c.datetime > (getdate() -7)
            and r.datetime > (getdate() -7)
            and n.ip_address in ('1.1.1.1','1.1.1.2','1.1.1.3')
            group by n.nodeid, n.caption, n.ip_address, n.lastboot
            

             

            I would warn you though, looking at only 8 devices took almost 30 seconds on my lab machine that is a fairly standard SQL build. I wouldn't push this one too hard with more than 15-20 devices if possible. However, you can always cross your fingers and try

             

            If you want the AVG CPU load from all polling cycles and the AVG Availability from all polling cycles over the last 7 days, use this one (which surprisingly took only 1 second more to complete):

             

            select
              n.nodeid
              ,n.caption as 'DEVICE'
              ,n.ip_address as 'IP ADDRESS'
              ,n.lastboot as 'LAST BOOT'
              ,avg(c.avgload) as 'AVG CPU'
              ,avg(r.availability) as 'AVG AVAILABILITY'
            from nodes n
            join cpuload c on n.nodeid=c.nodeid
            join responsetime r on n.nodeid=r.nodeid
            where c.datetime > (getdate() -7)
            and r.datetime > (getdate() -7)
            and n.ip_address in ('1.1.1.1','1.1.1.2','1.1.1.3')
            group by n.nodeid, n.caption, n.ip_address, n.lastboot
            

             

            Obviously, for your environment, you would replace the IP Addresses from the line below to the ones you are interested in. The syntax is ('ip address','ip address','etc','etc')

             

            and n.ip_address in ('1.1.1.1','1.1.1.2','1.1.1.3')
            

             

             

            Hope this helps, I would be interested to hear how many IP Addresses you run against and how long the report takes to generate.

             

            -ZackM

            Loop1 Systems: SolarWinds Training and Professional Services