IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Visualization_Library_GetTopEntitiesByAlerts2' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE SDK.Microsoft_SystemCenter_Visualization_Library_GetTopEntitiesByAlerts2 AS RETURN 1')
END
GO
ALTER PROCEDURE [SDK].[Microsoft_SystemCenter_Visualization_Library_GetTopEntitiesByAlerts2]
@ManagementGroup uniqueidentifier,
@ClassXml nvarchar(max),
@NumEntities 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
INSERT INTO #TopNTable(EntityRowId, NumAlerts)
SELECT w.RowId,w.NumAlerts FROM
(SELECT TOP (@NumEntities) RowId, COUNT(RowId) AS NumAlerts FROM
(SELECT ME.ManagedEntityRowId AS RowId FROM vManagedEntity AS ME
INNER JOIN #ManagedEntityTable AS MET ON MET.ManagedEntityRowId = ME.ManagedEntityRowId
INNER JOIN Alert.vAlert AS A ON A.ManagedEntityRowId = MET.ManagedEntityRowId
WHERE NOT EXISTS
(SELECT * FROM Alert.vAlertResolutionState as ARS
WHERE ARS.AlertGuid = A.AlertGuid
AND ARS.ResolutionState = 255)) AS t
GROUP BY RowId
ORDER BY NumAlerts DESC) AS w
DECLARE
@RowId int
SET @RowId = 0
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
FROM vManagedEntity AS ME
INNER JOIN #TopNTable AS TNT ON TNT.EntityRowId = ME.ManagedEntityRowId
WHERE ME.ManagedEntityRowId = @RowId
END
SELECT EntityGuid AS Id, DisplayName, NumAlerts FROM #TopNTable ORDER BY NumAlerts 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_GetTopEntitiesByAlerts2] TO OpsMgrReader
GO