GetTopEntitiesByAlerts.SP.sql (Resource)

Element properties:

Source Code:

<Resource ID="GetTopEntitiesByAlerts.SP.sql" Accessibility="Internal" FileName="GetTopEntitiesByAlerts.SP.sql"/>

File Content: GetTopEntitiesByAlerts.SP.sql

IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Visualization_Library_GetTopEntitiesByAlerts' AND UID = SCHEMA_ID('SDK'))


BEGIN


    EXECUTE ('CREATE PROCEDURE SDK.Microsoft_SystemCenter_Visualization_Library_GetTopEntitiesByAlerts AS RETURN 1')


END


GO





ALTER PROCEDURE [SDK].[Microsoft_SystemCenter_Visualization_Library_GetTopEntitiesByAlerts]


    @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





        EXEC @ExecError = sp_xml_preparedocument @xmlhandle OUTPUT, @ClassXml





        IF NOT @ExecError = 0


            RAISERROR(777971000, 16, 1


                ,'ClassXml'


                ,@ExecError)





        INSERT INTO #ClassTable (Id)


        SELECT ID


        FROM


            OPENXML(@xmlhandle, '/Classes/Class', 2)


        WITH


            (ID nvarchar(max))





        -- #ClassTable is now set up


          


        CREATE TABLE #ManagedEntityTable(


            ManagedEntityRowId 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





        -- #ManagedEntityTable is now set up





        CREATE TABLE #TopNTable (


            EntityGuid uniqueidentifier,


            DisplayName nvarchar(max),


            EntityRowId int,


            EntityState int,


            NumAlerts int)





        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


             @HealthState int


            ,@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





            EXEC [SDK].[Microsoft_SystemCenter_Visualization_Library_GetCurrentManagedEntityState]


                @HealthState = @HealthState OUTPUT,


                @ManagedEntityRowId = @RowId





            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





        SELECT EntityGuid AS Id, DisplayName, EntityState AS State,NumAlerts FROM #TopNTable ORDER BY NumAlerts DESC





    END TRY


    BEGIN CATCH


        IF (@@TRANCOUNT > 0)


            ROLLBACK TRAN





        SELECT


             @ErrorNumber = ERROR_NUMBER()


            ,@ErrorSeverity = ERROR_SEVERITY()


            ,@ErrorState = ERROR_STATE()


            ,@ErrorLine = ERROR_LINE()


            ,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')


            ,@ErrorMessageText = ERROR_MESSAGE()





        SET @ErrorInd = 1


    END CATCH





    -- 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_GetTopEntitiesByAlerts] TO OpsMgrReader


GO