Create tabular report for given IPs showing current & avg CPU Load.

I am working to create a tabular report for given IPs showing current CPU load & avg CPU Load for each day (for past 30 days).

So something like

Hostname

Current CPU Load

Avg CPU Load (current_day-1)

Avg CPU Load (current_day-2)

Avg CPU Load (current_day-3)

abc

1.2.3

def

4.5.6

Is there a solarwinds provided way to do this?

If not then has someone done this using custom SQL/SWQL?

I think may be I can use CPULoad_Detail table but the entries in there are little weird.

I see MinLoad, MaxLoad and AvgLoad columns generally has 1 in it for all nodes.

Is that expected and right?

Please share if someone has done this in past.

I am following this which should give me something similar but not what I want.

Find more posts tagged with

sam_reports

sam 6.8

Accepted answers

All comments

shuth

Someone who knows SQL/SWQL better than I may be able to help but initial thoughts are it would need a whole bunch of nested queries and I'm not sure of the syntax.

The following SWQL will give you the node names, IP, current usage, and average usage for last 30 days. I may have borked my dates with TOLOCAL but it looks right when I go through the data.

SELECT
 c.Node.Caption
,c.Node.IP_Address
,c.Node.CPULoad AS [Current CPU Load]
,AVG(c.AvgLoad) AS [Average Load (30d)]
FROM Orion.CPULoad c
WHERE DAYDIFF(TOLOCAL(c.DateTime),GETUTCDATE() ) <= 30
GROUP BY c.Node.Caption, c.Node.IP_Address, c.Node.CPULoad
ORDER BY c.Node.Caption ASC

The hard part is the extra columns for each day. The query below shows the average CPU load for yesterday (days diff = 1). You can repeat this query and change the 1 to a 2 for 2 days difference, etc.

SELECT
 c.Node.Caption
,c.Node.IP_Address
,AVG(c.AvgLoad) AS [Average Load (30d)]
FROM Orion.CPULoad c
WHERE DAYDIFF(TOLOCAL(c.DateTime),GETUTCDATE() ) = 1
GROUP BY c.Node.Caption, c.Node.IP_Address

So something like:

SELECT
 c.Node.Caption
,c.Node.IP_Address
,c.Node.CPULoad AS [Current CPU Load]
,AVG(c.AvgLoad) AS [Average Load (30d)]
,(SELECT <new query for -1 day> )
,(SELECT <new query for -2 days> )
,(SELECT <new query for -3 days> )
,(SELECT <new query for -4 days> )
etc

I'm not sure how to combine them into a single (and efficient) query though. Or I'm completely off and there is another method that is more elegant/clean...

shuth

There is another way to do it but it's not as clean. It's not dynamic - you'll need to update the source objects if you want to list more nodes.

1. Add a Custom Object Resource widget to a view

2. Click Configure this resource (or EDIT) . Since we want CPU data, leave the object type as Nodes and click Select Object. Find the nodes and click Submit.

3. Set the chart to Average CPU Load, change the time period to Last 30 Days, and the sample interval to One a Day

4. You should see a chart like below.

5. If you click on the chart itself, it will open up a new tab. Down the bottom of this new page, is a Display Chart Data section.

- Clicking RAW DATA will download an Excel spreadsheet of the data

- Clicking CHART DATA will load up the data in the browser

Theoretically you could load this data up via URL.

http://solarwindsserver/Orion/NetPerfMon/ChartData.aspx?SubsetColor=&RYSubsetColor=&ChartName=AvgAggrCPULoad&Title=Multiple+Object+Chart&SubTitle=Custom+object+resource&SubTitle2=&Width=640&Height=0&SampleSize=1D&Period=LAST 30 DAYS&FontSize=1&NetObject=&ResourceID=4200&NetObjectPrefix=&Rows=&CustomPollerID=&ShowTrend=True&PlotStyle=&ChartEntity=Orion.Nodes&ShowSum=NoSum&SelectedEntities=1470,1914,1915,1985&DataColumnName=&FilterEntities=False&TopXX=&timeStamp=637040665406518961&PeriodBegin=15/08/2019 12:00:00 AM&PeriodEnd=14/09/2019 2:03:21 PM

mesverrum

I think a much easier way to do this, with no custom code required, is just to use the date summarizing feature that is built in.

Add your caption, ip, current cpu, and the historical avg/min/max cpu and their timestamp. Then a bit below where you picked the columns is a box that defaults to something like "no summarization, show all values" where you want to change it to daily. It will take your data set and automatically add a row for each daily average/min/max.