GetPerformanceSeriesStatistics.SP.sql (Resource)

Element properties:

Source Code:

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

File Content: GetPerformanceSeriesStatistics.SP.sql

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


BEGIN


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


END


GO





ALTER PROCEDURE [SDK].[Microsoft_SystemCenter_Visualization_Library_GetPerformanceSeriesStatistics]


	@ManagementGroup uniqueidentifier,


    @ManagedEntityXml nvarchar(max),


    @StartTime datetime,


    @EndTime datetime,


    @ObjectName nvarchar(max),


    @CounterName nvarchar(max),


    @InstanceName nvarchar(max) = '',


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





    DECLARE @Error int


    DECLARE @ExecError int


    DECLARE @return_value int





    BEGIN TRY





        -- Create #ManagedEntityTable to contain the data for all of the managed entities that we care about





        CREATE TABLE #ManagedEntityTable(


            ManagedEntityGuid uniqueidentifier,


            ManagedEntityRowId int,


            AverageValue float,


            MinimumValue float,


            MaximumValue float)





        -- Parse the ManagedEntityXml to get the GUIDs into the #ManagedEntityTable





        DECLARE @xmlhandle int





        EXEC @return_value = sp_xml_preparedocument @xmlhandle OUTPUT, @ManagedEntityXml





        if NOT @return_value = 0


            RAISERROR(777971000, 16, 1


                ,'ManagedEntityXml'


                ,@return_value)





        INSERT INTO #ManagedEntityTable (ManagedEntityGuid, AverageValue, MinimumValue, MaximumValue)


        SELECT ID,0,0,0


            FROM


                OPENXML(@xmlhandle, '/ManagedEntities/ManagedEntity', 2)


            WITH


                (ID nvarchar(max))





	    UPDATE #ManagedEntityTable set ManagedEntityRowId = ME.ManagedEntityRowId


	    FROM vManagedEntity as ME


		    INNER JOIN vManagementGroup as MG on ME.ManagementGroupRowId = MG.ManagementGroupRowId


		    INNER JOIN #ManagedEntityTable as MET ON MET.ManagedEntityGuid = ME.ManagedEntityGuid


	    WHERE MET.ManagedEntityGuid = ME.ManagedEntityGuid


		    AND MG.ManagementGroupGuid = @ManagementGroup





        -- #ManagedEntityTable is now set up





        DECLARE


             @AverageValue float


            ,@MinimumValue float


            ,@MaximumValue float


            ,@ManagedEntityRowId int


    


        SET @ManagedEntityRowId = 0


    


        WHILE EXISTS (SELECT * FROM #ManagedEntityTable AS MET WHERE MET.ManagedEntityRowId > @ManagedEntityRowId)


        BEGIN


		    SELECT TOP 1 @ManagedEntityRowId = MET.ManagedEntityRowId


            FROM #ManagedEntityTable AS MET


            WHERE MET.ManagedEntityRowId > @ManagedEntityRowId


            ORDER BY MET.ManagedEntityRowId ASC





		    EXEC [SDK].[Microsoft_SystemCenter_Visualization_Library_GetPerformanceSeriesStatisticsAsParameters]


                @ManagedEntityRowId = @ManagedEntityRowId,


                @StartTime = @StartTime,


                @EndTime = @EndTime,


                @ObjectName = @ObjectName,


                @CounterName = @CounterName,


                @InstanceName = @InstanceName,


                @AggregationType = @AggregationType,


                @AverageValue = @AverageValue OUTPUT,


                @MinimumValue = @MinimumValue OUTPUT,


                @MaximumValue = @MaximumValue OUTPUT


		    UPDATE #ManagedEntityTable set AverageValue = @AverageValue, MinimumValue = @MinimumValue, MaximumValue = @MaximumValue


		    WHERE ManagedEntityRowId = @ManagedEntityRowId


        END








        SELECT ManagedEntityGuid as ManagedEntityGuid, AverageValue as AverageValue, MinimumValue as MinimumValue,MaximumValue as MaximumValue


        FROM #ManagedEntityTable


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


GO