Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Where to get Historical CPU and Mem stats from SW DB

My team is beging to work with a BI tool that will mine data from SW db. I would like to know where I can find historical data on CPU and memory, like last 7 days, last 24 hours and last 4 hours. It is of course possible to do this in the front end of the BI tool but it would seem that the data is already avail in the SW DB.

Find more posts tagged with

Accepted answers

mesverrum

the swql queries work pretty similarly to sql but there are a few small changes to commands that you can see if you read through the documentation, for the last 7 days average cpu you'd be doing something along the lines of

select avg(cpuload) from orion.cpuload where daydiff(datetime,getdate())<7

group by nodeid

All comments

mesverrum

To make your BI integration more future proofed you would probably want to take a look at the Solarwinds SDK documentation to make calls to the API. Going directly to the SQL means you are more likely to have things break when Solarwinds makes changes to the tables in future versions. Using the SDK the info you are looking for will be on this table Orion.CPULoad

About SWIS · solarwinds/OrionSDK Wiki · GitHub

-Marc Netterfield

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

brent.weaver

Thank you SO much for changing my direction here. I think my plan would be to use SWISS (which is the best part of a great product!!) to build a CSV file and then import that in each day to the tool... Thank you for this thought!

How would I get data such as:

Last 7 Days of Avg CPU

More granular 24 period of CPU

Last 4 hours of CPU

I would like to do the same as above for response time, and many other attributes. Any help is MUCH appreciated! I suppose if I could just get the data from all intervals I could have the BI tool do the timeframe computing. Any help is MUCH appreciated!

mesverrum

select avg(cpuload) from orion.cpuload where daydiff(datetime,getdate())<7

group by nodeid

brent.weaver

Thank you soon much!!! Will this give me the data from each interval? if not it may not even matter... Thank you soon much

brent.weaver

Having success thanks to your help here! Since I am doing this via the SWIS API, how do I group them by ID and correlate them to an actual node name (caption)?

THANK YOU!

bscherer

something like this

select n.caption, avg(cpu.cpuload) as avgCPULoad from orion.cpuload cpu

left outer join Orion.Nodes N on n.nodeid = cpu.nodeid

where daydiff(datetime,getdate())<7

group by n.caption

brent.weaver

A question along the lines of this post. It seems that [at least some of] the historical data get "rolled-over" into different tables where the name is based on date? Is that correct?

For example:

CPULoad_Detail_20160408

bscherer

Yes, but their are views so you do not need to worry about that.

When using SWQL it is not an issue either.

brent.weaver

Whew! It may be that I need to do direct connects as there is no way to call the API directly from the BI tool, which means I would need to write out several csv files and then pick them up with the BI tool and we want to avoid that if possible.

Can you give me an example of a view that would have CPU history for the last 7 days?

bscherer

You just need to query the CPULoad view.

Not much changes in the query. At least I think my sqlfu is correct here

select n.caption, avg(cpu.avgload) as avgCPULoad,min(MinLoad) as minCPULoad, max(MaxLoad) as maxCPULoad, avg(cpu.TotalMemory) as TotalMemory, min(MinMemoryUsed) as minMemUsed, max(MaxMemoryUsed) as MaxMemUsed, avg(AvgMemoryUsed) as avgMemUsed, avg(AvgPercentMemoryUsed) as avgPercentMemUsed

from Nodes N

left outer join cpuload cpu on n.nodeid = cpu.nodeid

where datetime >= dateadd(day,-7,getdate())

group by n.caption