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

Yossarian

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.