cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Highlighted
Level 10

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

HostnameIPCurrent CPU LoadAvg CPU Load (current_day-1)
Avg CPU Load (current_day-2)
Avg CPU Load (current_day-3)
abc1.2.345651283
def4.5.687985899

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.

Tags (2)
0 Kudos
3 Replies
Highlighted
MVP
MVP

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

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...

Highlighted
MVP
MVP

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

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

     pastedImage_1.png

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.

     pastedImage_6.png

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

     pastedImage_11.png

4. You should see a chart like below.

     pastedImage_0.png

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.

     pastedImage_26.png

   

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

     pastedImage_19.png

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

          pastedImage_18.png

Theoretically you could load this data up via URL.

http://solarwindsserver/Orion/NetPerfMon/ChartData.aspx?SubsetColor=&RYSubsetColor=&ChartName=AvgAgg...

Highlighted

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

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.

- Marc Netterfield, Github