PerfDataWithAverageReductionGet.SP.sql (Resource)

Element properties:

Source Code:

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

File Content: PerfDataWithAverageReductionGet.SP.sql

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


BEGIN


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


END


GO





ALTER PROCEDURE [SDK].[Microsoft_SystemCenter_Visualization_Library_PerfDataWithAverageReductionGet]


     @StartTime datetime


    ,@EndTime datetime


    ,@ManagedEntityRowId int


    ,@PerfRuleInstanceRowId int


    ,@NumberOfDataPoints int


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





    BEGIN TRY





        IF @ManagedEntityRowId IS NULL


        BEGIN


            RAISERROR (777971002, 16, 1


                ,0


                ,0


                ,0


                ,N'SDK.Microsoft_SystemCenter_Visualization_Library_PerfDataWithAverageReductionGet'


                ,0


                ,N'Non existent managed entity'


            )


        END





/* ------------------------------ */





        -- Create ##SubIntervalsTable to contain the startTime, endTime and the average


        -- value of the sub intervals.





		CREATE TABLE #SubIntervalsTable(


			startTime DateTime,


			endTime DateTime,


			averageValue float)





		DECLARE @IxSubInterval int = 0


		DECLARE @IntervalLength int


		DECLARE @DeltaSeconds int





		SELECT @DeltaSeconds = DATEDIFF(second, @StartTime, @EndTime)





		DECLARE @startSubIntervalDatetime datetime = @StartTime


		DECLARE @endSubIntervalDatetime datetime


		DECLARE @averageValue float





		SET @IntervalLength = (@DeltaSeconds / @NumberOfDataPoints)





		WHILE (@IxSubInterval < @NumberOfDataPoints)


		BEGIN





		   SET @endSubIntervalDatetime = DATEADD(second, @IntervalLength-1, @startSubIntervalDatetime)





		   INSERT INTO #SubIntervalsTable VALUES (@startSubIntervalDatetime, @endSubIntervalDatetime, null)





		   SET @startSubIntervalDatetime = DATEADD(second, @IntervalLength, @startSubIntervalDatetime)





		   SET @IxSubInterval = @IxSubInterval + 1


		END





		IF (@AggregationTypeToQueryFrom = 0)


		BEGIN


		  SELECT S.startTime, AVG(SampleValue) FROM #SubIntervalsTable S


		  LEFT OUTER JOIN  [Perf].[vPerfRaw] P


		  ON ((P.DateTime >= S.startTime) AND (P.DateTime < S.endTime) AND


		      (ManagedEntityRowId = @ManagedEntityRowId) AND


		      (PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId))


		  GROUP BY S.startTime


		  ORDER BY S.startTime


		END


		ELSE IF (@AggregationTypeToQueryFrom = 20)


		BEGIN


		  SELECT S.startTime, AVG(P.AverageValue) FROM #SubIntervalsTable S


		  LEFT OUTER JOIN [Perf].[vPerfHourly] P


		  ON ((P.DateTime >= S.startTime) AND (P.DateTime < S.endTime) AND


		      (ManagedEntityRowId = @ManagedEntityRowId) AND


		      (PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId))


		  GROUP BY S.startTime


		  ORDER BY S.startTime


		END


		ELSE IF (@AggregationTypeToQueryFrom = 30)


		BEGIN


		  SELECT S.startTime, AVG(P.AverageValue) FROM #SubIntervalsTable S


		  LEFT OUTER JOIN [Perf].[vPerfDaily] P


		  ON ((P.DateTime >= S.startTime) AND (P.DateTime < S.endTime) AND


 		      (ManagedEntityRowId = @ManagedEntityRowId) AND


		      (PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId))


		  GROUP BY S.startTime


		  ORDER BY S.startTime


		END


		ELSE


		BEGIN


		RAISERROR (777971002, 16, 1


			,0


			,0


			,0


			,N'SDK.Microsoft_SystemCenter_Visualization_Library_PerfDataWithAverageReductionGet'


			,0


			,N'Value of @AggregationTypeToQueryFrom is invalid. Only 0, 20 and 30 are supported.'


		)


		END








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


GO