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

    help with custom SQL query




      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.





        • 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:



          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.