1 Reply Latest reply on Sep 11, 2012 2:38 AM by Víťa Tauer

    help with custom SQL query

    paulow19782

      Hi,

       

      I have a major issue at the moment in that our Solarwinds server was only configured to hold the last 30 days worth of data fully and then it seems to give a daily avergae before the 30 day period for all counters.

       

      I need to find out the CPU history of "Node A" between some dates that go back a fair way. I have the NPM SQL backup's so i want to restore them to a new database on our SQL server and then write a quick SQL query in the form of

       

      Select CPU % between date x and Date Y every 10 mins for Node A

       

      Does anyone know how I can accomplish this or if there is a reference guide from Solarwinds relating to the fields and tables that i can qery in order to get this information?

       

      I tried to do a SQL trace when I do it on the current site but we have so much going on there that I can't find the SQL query that is executed in the trace logs.

       

      Once I get the information I can then plot the graphs myself in excel so I am kind of hoping I will get a table with date/time in one column and CPU % in the other column.

       

      thanks,

       

      Paul

        • Re: help with custom SQL query
          Víťa Tauer

          The table (or view, to be precise) you are looking for is CPULoad.

           

          You can query it fairly easily:

           

          SELECT AvgLoad, DateTime FROM CPULoad WHERE NodeID={your node id} ORDER BY DateTime

           

          If you want the returned data to be sampled by 10 minutes, you can do following:

           

          SELECT

          Convert([DateTime],Floor(Cast([DateTime] as Float)/10*1440)*10/1440,0) as [DateTime],

          AVG([AvgLoad]) as Load

          FROM CPULoad

          WHERE [NodeID]={your node id}

          GROUP BY Convert([DateTime],Floor(Cast([DateTime] as Float)/10*1440)*10/1440,0)

          ORDER BY [DateTime]

           

          Hope this helps.