Microsoft.SystemCenter.Omonline.OutsideIn.VSWebTest.DataProviders.Script.SP.sql (Resource)

Element properties:

Source Code:

<Resource ID="Microsoft.SystemCenter.Omonline.OutsideIn.VSWebTest.DataProviders.Script.SP.sql" Accessibility="Internal" FileName="LastPerformanceSampleValueAndTimeGet.SP.sql"/>

File Content: LastPerformanceSampleValueAndTimeGet.SP.sql

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


BEGIN


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


END


GO





ALTER PROCEDURE [sdk].[Microsoft_SystemCenter_Omonline_OutsideIn_VSWebTest_LastPerformanceSampleValueAndTimeGet]


    @ManagementGroup uniqueidentifier,


    @CountersXml nvarchar(max),


    @RuleId uniqueidentifier,


    @InstanceNamePattern nvarchar(256) = '%'


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 #CounterTable to contain the GUIDs for all of the managed entities that we care about


        CREATE TABLE #CounterTable(


            MEId uniqueidentifier,


            RuleId uniqueidentifier,


            InstanceNamePattern nvarchar(256))


 


        -- This table will be used to store the result set that's going to be returned to the client.


        CREATE TABLE #ReturnValuesTable(


                Id int IDENTITY(1,1),


		        MEId uniqueidentifier,


		        MERowId int,


                ObjectName nvarchar(256),


                CounterName nvarchar(256),


                InstanceName nvarchar(256),


                PerformanceRuleInstanceRowId int,


                LastSampleValue float,


                LastSampleTime datetime


                )


 


        -- Parse the CountersXml to get the GUIDs into the #CounterTable


        DECLARE @xmlhandle int


 


        EXEC @ExecError = sp_xml_preparedocument @xmlhandle OUTPUT, @CountersXml


 


        IF NOT @ExecError = 0


            RAISERROR(777971000, 16, 1


                ,'CountersXml'


                ,@ExecError)


 


        INSERT INTO #CounterTable (MEId, RuleId, InstanceNamePattern)


        SELECT *


        FROM


            OPENXML(@xmlhandle, '/Counters/Counter', 2)


            WITH (MEID uniqueidentifier 'MeId',


			      RuleId uniqueidentifier 'RuleId',


			      InstanceNamePattern nvarchar(256) 'InstanceNamePattern')





		UPDATE #CounterTable


		SET #CounterTable.RuleId = @RuleId


		WHERE #CounterTable.RuleId is null





		UPDATE #CounterTable


		SET #CounterTable.InstanceNamePattern = @InstanceNamePattern


		WHERE #CounterTable.InstanceNamePattern is null





        -- Populate the results including all matches with mixed (could have extras that need to be removed.


        INSERT INTO #ReturnValuesTable


		SELECT Result.ManagedEntityGuid, Result.ManagedEntityRowId,


			   Result.ObjectName, Result.CounterName, 


               Result.InstanceName, Result.PerformanceRuleInstanceRowId,


               Result.SampleValue, Result.DateTime		


		FROM (


			SELECT ME.ManagedEntityGuid, ME.ManagedEntityRowId,


				   PR.ObjectName, PR.CounterName, 


				   PRI.InstanceName, PRI.PerformanceRuleInstanceRowId,


				   PPR.SampleValue, PPR.DateTime,


				   (RANK() OVER (


						PARTITION BY ME.ManagedEntityRowId, PRI.PerformanceRuleInstanceRowId 


						ORDER BY PPR.DateTime DESC)) as Rank


				FROM vPerformanceRule PR


				JOIN vRule VR ON VR.RuleRowId = PR.RuleRowId


				JOIN vPerformanceRuleInstance PRI ON PR.RuleRowId = PRI.RuleRowId


				JOIN Perf.vPerfRaw PPR ON (PPR.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId)


				JOIN vManagedEntity ME ON PPR.ManagedEntityRowId = ME.ManagedEntityRowId


				JOIN vManagementGroup AS VMG ON ME.ManagementGroupRowId = VMG.ManagementGroupRowId


				JOIN #CounterTable CT ON (CT.RuleId = VR.RuleGuid) AND 


										 (CT.MEId = ME.ManagedEntityGuid) AND 


										 (PRI.InstanceName like CT.InstanceNamePattern COLLATE database_default)


				WHERE (VMG.ManagementGroupGuid = @ManagementGroup)) as Result


		WHERE Result.Rank = 1





		SELECT *


		FROM #ReturnValuesTable


		WHERE LastSampleValue is not null


 


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


GO