Script to run in the SentryOne database to get CPU utilization min/max/avg/6σ for all targets

SentryOne_CPU_Utilization_Stats_for_all_Sql_Server_Targets.sql

USE SentryOne
GO
DECLARE
 @SiteName		NVARCHAR(1000)	= NULL
,@SqlServerName		NVARCHAR(1000)	= NULL
,@End_date		DATETIME	= NULL
,@Start_date		DATETIME	= NULL
,@CounterID		SMALLINT	= 1858 -- CPU %
,@DefaultDaysBack	INT		= 90

SET @End_date = ISNULL(@End_date, CONVERT(date, GETDATE()))
SET @Start_date = ISNULL(@Start_date, @End_date - @DefaultDaysBack)

DECLARE
	  @DeviceID smallint
	, @ConnectionID smallint
	, @StartTimestamp int = [SentryOne].[dbo].[fnConvertDateTimeToTimestamp](@Start_date)
	, @EndTimestamp int = [SentryOne].[dbo].[fnConvertDateTimeToTimestamp](@End_date)
	, @RangeSizeMinutes int = DATEDIFF(minute, @Start_date, @End_date)
		
DECLARE @AggPerf AS TABLE
(
	[AvgVal] FLOAT,
	[MinVal] FLOAT,
	[MaxVal] FLOAT,
	[STD] FLOAT
)

DECLARE @Result AS TABLE
(
	[SiteName] NVARCHAR(1000),
	[SqlServerName] NVARCHAR(1000),
	[AvgVal] FLOAT,
	[MinVal] FLOAT,
	[MaxVal] FLOAT,
	[STD] FLOAT
)

DECLARE SqlServers CURSOR
LOCAL FAST_FORWARD
FOR
SELECT d.ObjectName, s.Name
FROM Device AS d
INNER JOIN Site AS s ON d.SiteID = s.ID
WHERE d.ID IN (SELECT DeviceID FROM vwSqlServer)
AND (@SiteName IS NULL OR s.Name = @SiteName)
AND (@SqlServerName IS NULL OR d.ObjectName = @SqlServerName)

OPEN SqlServers

WHILE 1=1
BEGIN

	FETCH NEXT FROM SqlServers INTO @SqlServerName, @SiteName
	IF @@FETCH_STATUS <> 0 BREAK;

	SELECT @ConnectionID = c.ID, @DeviceID = d.ID
	FROM dbo.Device AS d
	INNER JOIN dbo.EventSourceConnection AS c
	ON d.ID = c.DeviceID
	INNER JOIN dbo.EventSourceConnectionType AS ect
	ON c.EventSourceConnectionTypeID = ect.ObjectID
	INNER JOIN dbo.[Site] AS s
	ON d.SiteID = s.ID
	WHERE
	    s.Name = @SiteName
	AND ect.Name = 'SQL Server'
	AND @SqlServerName IN (d.HostName, d.ObjectName, d.FriendlyName, c.ServerName, c.ObjectName, c.FriendlyName)

	-- Processor time:
	INSERT INTO @AggPerf
	EXEC [dbo].[GetCounterBaselineDataAggregateForDeviceByTimestamp] @DeviceID, @CounterID, @StartTimestamp, @EndTimestamp, @RangeSizeMinutes

	INSERT INTO @Result
	SELECT @SiteName AS SiteName, @SqlServerName AS SqlServerName, *
	FROM @AggPerf

	DELETE @AggPerf;

END

CLOSE SqlServers
DEALLOCATE SqlServers;

SELECT
  c.CounterName
, ag.*
, SixSigmaMax = (SELECT MIN(Val) FROM (VALUES(ag.AvgVal + ag.STD * 6),(ag.MaxVal)) AS a(Val))
FROM @Result AS ag
INNER JOIN dbo.PerformanceAnalysisCounter AS c ON c.ID = @CounterID