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

Recreate Top 10s for Periods Longer Than Last 24 Hours

I'm interested in recreating the queries that create the Top 10s, but want them to reflect, for instance, Top 10 Nodes by Average CPU Load for the last 30 days. My SQL game isn't the strongest, as I am still relatively inexperienced. I have the code that runs the canned Top 10s, but the database is so huge and complicated I'm having trouble determining what I need to pull from to look at the last 30 days of data. Has anyone else attempted this?

Find more posts tagged with

Accepted answers

All comments

orioncrack

You are asking for something that has been asked for years from SolarWinds.

In the blog for PerfStack, this is the front and central point made.

You will have to build out custom views with charts to do what you want, which are very busy and terrible. Is PerfStack the answer? Not really, we want a report, like the Views, why they cannot do date selection is still on my Sad List, LOL.

Drag & Drop Answers to Your Toughest IT Questions

mprobus

What are you trying to build by using the Top 10s? If you are just wanting a report or resource, you can build one by using a custom table. It will not be in the same format as the current top 10 resources, but it will get the job done. You can configure the table to look like the following.

The SWQL code behind it is as follows. If you want SQL instead, I can work on doing the conversion, but this will get you want you want inside of SAM.

SELECT TOP 10 [data].[Caption] AS [Caption],SUM((CASE WHEN [data].[CPULoadHistory].[AvgLoad] < 0 THEN NULL ELSE [data].[CPULoadHistory].[AvgLoad] END)*(data.CPULoadHistory.Weight))/SUM(case when ((CASE WHEN [data].[CPULoadHistory].[AvgLoad] < 0 THEN NULL ELSE [data].[CPULoadHistory].[AvgLoad] END) is null) then 0 else (data.CPULoadHistory.Weight) end) AS [AvgLoad]
FROM orion.nodes AS data
WHERE
((([data].[CPULoadHistory].[ObservationTimestamp]) >= p0)) AND (([data].[CPULoadHistory].[ObservationTimestamp]) < p1)))
GROUP BY
[data].[Caption]
ORDER BY
[AvgLoad] DESC
WITH NOLOCK RETURN XML RAW (p0=3/15/2017 4:00:00 AM, p1=4/15/2017 4:00:00 AM)