IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Visualization_Library_GetTopEntitiesByPerformance' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE SDK.Microsoft_SystemCenter_Visualization_Library_GetTopEntitiesByPerformance AS RETURN 1')
END
GO
ALTER PROCEDURE [SDK].[Microsoft_SystemCenter_Visualization_Library_GetTopEntitiesByPerformance]
@ManagementGroup uniqueidentifier,
@ClassXml nvarchar(max),
@StartTime datetime,
@EndTime datetime,
@ObjectName nvarchar(256),
@CounterName nvarchar(256),
@InstanceName nvarchar(256) = '',
@NumEntities int,
@AggregationType int,
@OrderBy int
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
/* ------------------------------ */
BEGIN TRY
DECLARE @ExecError int
-- Create #ClassTable to contain the GUIDs for all of the class types that we care about
CREATE TABLE #ClassTable(
Id uniqueidentifier)
-- Parse the ClassXml to get the GUIDs into the #ClassTable
DECLARE @xmlhandle int
INSERT INTO #ManagedEntityTable
SELECT DISTINCT ME.ManagedEntityRowId FROM vManagedEntity AS ME
INNER JOIN vManagedEntityType AS MET ON MET.ManagedEntityTypeRowId = ME.ManagedEntityTypeRowId
INNER JOIN #ClassTable AS CT ON CT.Id = MET.ManagedEntityTypeGuid
INNER JOIN vManagementGroup AS MG ON MG.ManagementGroupRowId = ME.ManagementGroupRowId
INNER JOIN vManagedEntityManagementGroup AS MEMG ON MEMG.ManagedEntityRowId = ME.ManagedEntityRowId
WHERE MG.ManagementGroupGuid = @ManagementGroup
AND MEMG.ToDateTime IS null
IF @AggregationType = 0
BEGIN
SET @ViewName = 'Perf.vPerfRaw'
SET @ColumnName = 'vPerf.SampleValue'
SET @TimeStatement = '
AND vPerf.DateTime >= @StartTime
AND vPerf.DateTime <= @EndTime
'
END
ELSE IF @AggregationType = 20
BEGIN
SET @ViewName = 'Perf.vPerfHourly'
SET @ColumnName = 'vPerf.AverageValue'
SET @TimeStatement = '
AND (vPerf.DateTime >= DATEADD(hh, DATEPART(hh, @StartTime), convert(varchar(8), @StartTime, 112)))
AND (vPerf.DateTime < DATEADD(hh, DATEPART(hh, @EndTime), convert(varchar(8), @EndTime, 112)))
'
END
ELSE IF @AggregationType = 30
BEGIN
SET @ViewName = 'Perf.vPerfDaily'
SET @ColumnName = 'vPerf.AverageValue'
SET @TimeStatement = '
AND (vPerf.DateTime >= DATEADD(hh, DATEPART(hh, @StartTime), convert(varchar(8), @StartTime, 112)))
AND (vPerf.DateTime < DATEADD(hh, DATEPART(hh, @EndTime), convert(varchar(8), @EndTime, 112)))
'
END
ELSE
BEGIN
RAISERROR (777971002, 16, 1
,0
,0
,0
,N'SDK.GetTopEntitiesByPerformance'
,0
,N'Invalid AggregationType - only 0, 20, and 30 are supported'
)
END
IF @InstanceName = '' OR @InstanceName IS NULL
SET @WhereStatement = '
WHERE PRule.ObjectName = @ObjectName
AND PRule.CounterName = @CounterName
'
ELSE
SET @WhereStatement = '
WHERE PRule.ObjectName = @ObjectName
AND PRule.CounterName = @CounterName
AND PRI.InstanceName = @InstanceName
'
IF @OrderBy = 0
SET @OrderByStatement = ' ORDER BY AVERAGE ASC '
ELSE
SET @OrderByStatement = ' ORDER BY AVERAGE DESC '
SET @SqlStatement = '
INSERT INTO #TopNTable (EntityRowId, SampleValue)
SELECT TOP (@NumEntities) MET.ManagedEntityRowId, AVG(' + @ColumnName + ') AS Average
FROM ' + @ViewName + ' AS vPerf
INNER JOIN vPerformanceRuleInstance AS PRI ON PRI.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId
INNER JOIN vPerformanceRule AS PRule ON PRule.RuleRowId = PRI.RuleRowId
INNER JOIN #ManagedEntityTable AS MET ON MET.ManagedEntityRowId = vPerf.ManagedEntityRowId
' + @WhereStatement + @TimeStatement + '
GROUP BY MET.ManagedEntityRowId ' +
@OrderByStatement
WHILE EXISTS(SELECT * FROM #TopNTable as TNT where TNT.EntityRowId > @RowId)
BEGIN
SELECT TOP 1 @RowId = TNT.EntityRowId
FROM #TopNTable as TNT
WHERE TNT.EntityRowId > @RowId
ORDER BY TNT.EntityRowId ASC
UPDATE #TopNTable SET
DisplayName = ME.DisplayName
,EntityGuid = ME.ManagedEntityGuid
,EntityState = @HealthState
FROM vManagedEntity as ME
INNER JOIN #TopNTable as TNT on TNT.EntityRowId = ME.ManagedEntityRowId
WHERE ME.ManagedEntityRowId = @RowId
END
IF (@OrderBy = 0)
SELECT TNT.EntityGuid as Id, TNT.DisplayName, TNT.EntityState, TNT.SampleValue FROM #TopNTable AS TNT
ORDER BY SampleValue ASC
ELSE
SELECT TNT.EntityGuid as Id, TNT.DisplayName, TNT.EntityState, TNT.SampleValue FROM #TopNTable AS TNT
ORDER BY SampleValue DESC
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON [SDK].[Microsoft_SystemCenter_Visualization_Library_GetTopEntitiesByPerformance] TO OpsMgrReader
GO
Home