Sample DW Queries dashboard

Dashboard Server
,
1

Hi

Add context to your dashboards from SQL data sources | SquaredUp speaks of a sample DW dashboard (Teams > Monitoring > Sample DW SQL Queries ) . Is it only available in 4.8 ? I don’t see it in my 5.1 install.
If so, does anybody have the source of the CPU trend query ?

Thanks
Nick

2

Hello Nick, it looks like the dashboard name in the article is incorrect. The dashboard name is DW Analytics (first in the list under Teams > Monitoring. Do you see it?

1 Like
3

Hi

Yes, that is it. Saves me a lot of typing from the screenshots :slight_smile:

Thanks!
Nick

1 Like
4

Pro-tip (one I use often)!

OneNote has a “Copy text from picture” option. Simply paste an image into a OneNote and then right-click> copy text.

Glad you have the dashboard you need though! Highly recommend checking out the Analytics perspective on Alerts and also the Alert History perspective on objects - Both of these have SQL queries into the DW.

And of course, the fountain of all knowledge, Kevin Holman:

5

Hi @vinbab,

My production instance doesn’t have this dashboard but my test instance did. Unfortunately I don’t see the CPU trend Query in there.
Care to share the SQL Query? Just looking at the screenshots, I can see that there are parts missing… (I’m handy with SQL, just not THAT good)…

Merci

6

Here you go:

  -- Just in case we need a smaller sample size, create a variable for our StartDate
DECLARE @StartDate datetime
SET @StartDate = ISNULL(@StartDate, cast(cast(cast(DATEADD(DAY,-30,GetDate()) as int) as float) as datetime));

-- Create a temporary table to hold our Performance Data
DECLARE @Performance TABLE(
  ID                INT IDENTITY(1,1) PRIMARY KEY
, Server            nVarChar(255)
, DateTime          datetime
, AverageValue      DECIMAL(38, 10)
, MinValue          DECIMAL(38, 10)
, MaxValue          DECIMAL(38, 10)
, StandardDeviation DECIMAL(38, 10)
, Trend             DECIMAL(38, 10)
)

--Find the names all of our Management Servers
DECLARE @Server TABLE(Path nVarChar(255))
INSERT INTO @Server
SELECT vme.Path 
FROM  vManagedEntity vme
 JOIN vRelationship vr on vr.TargetManagedEntityRowId = vme.ManagedEntityRowId
 JOIN vManagedEntity vme2 on vme2.ManagedEntityRowId = vr.SourceManagedEntityRowId
WHERE vme2.DisplayName = 'Operations Manager Management Servers'

-- Fetch our performance metric
INSERT INTO @Performance
SELECT me.Path
     , p.DateTime
     , p.AverageValue
     , p.MinValue
     , p.MaxValue
     , p.StandardDeviation
     , CONVERT(DECIMAL(38, 10),NULL) AS Trend
FROM Perf.vPerfDaily AS p
     INNER JOIN vPerformanceRuleInstance AS pri ON p.PerformanceRuleInstanceRowId = pri.PerformanceRuleInstanceRowId 
     INNER JOIN vPerformanceRule AS pr ON pri.RuleRowId = pr.RuleRowId 
     INNER JOIN vRule ON pri.RuleRowId = vRule.RuleRowId 
     INNER JOIN vManagedEntity AS me ON p.ManagedEntityRowId = me.ManagedEntityRowId
	 INNER JOIN @Server as s ON me.Path = s.Path
WHERE DateTime >= @StartDate
  AND ObjectName = 'Health Service' 
  AND pr.CounterName = 'Agent Processor Utilization'
ORDER BY me.path, ObjectName, CounterName

--Create a second performance table
DECLARE @Performance2 TABLE(
  ID                INT IDENTITY(1,1) PRIMARY KEY
, Server            nVarChar(255)
, DateTime          datetime
, Value             DECIMAL(38, 10)
, Trend             DECIMAL(38, 10)
)

--Insert the MaxValue for each datetime sample so that we have only one record per sample time
INSERT INTO @Performance2
SELECT 'Server' as Server
     , DateTime as DateTime
     , Max(MaxValue) as Value
		 , CONVERT(DECIMAL(38, 10),NULL) AS Trend
FROM @Performance
GROUP BY DateTime

-- Declare the variables needed to calculate our trendline
DECLARE @sample_size INT; 
DECLARE @intercept   DECIMAL(38, 10);
DECLARE @slope       DECIMAL(38, 10);
DECLARE @sumX        DECIMAL(38, 10);
DECLARE @sumY        DECIMAL(38, 10);
DECLARE @sumXX       DECIMAL(38, 10);
DECLARE @sumYY       DECIMAL(38, 10);
DECLARE @sumXY       DECIMAL(38, 10);

-- Calculate the Sample Size and the different Sums for our target metric
-- In this case, we're using MaxValue, but any of the values returned can be used
SELECT
  @sample_size = COUNT(*)
, @sumX        = SUM(ID)
, @sumY        = SUM(Value)
, @sumXX       = SUM(ID*ID)
, @sumYY       = SUM(Value*Value)
, @sumXY       = SUM(ID*Value)
FROM @Performance2;

-- Calculate the Slope and Intercept
SET @slope = CASE WHEN @sample_size = 1
    THEN 0 -- avoid divide by zero error
    ELSE (@sample_size * @sumXY - @sumX * @sumY) / (@sample_size * @sumXX - POWER(@sumX,2))
    END;
SET @intercept = (@sumY - (@slope*@sumX)) / @sample_size;

-- Now add the trend calculation back to our original table
UPDATE @Performance2 SET Trend = (@slope*ID) + @intercept;

-- Output the results
SELECT 
  '' as Server
, DateTime
, Trend
, Value 
FROM @Performance2;
1 Like
7

Thanks @Jelly, just noted your reply there. However, that SQL Query is for the MS servers or for any Windows Computer? I thought this was for CPU trend for a single windows computer?