AggregatedPerfValuesForMultipleSeriesGet.SP.sql (Resource)

Element properties:

Source Code:

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

File Content: AggregatedPerfValuesForMultipleSeriesGet.SP.sql

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


BEGIN


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


END


GO





ALTER PROCEDURE [sdk].[Microsoft_SystemCenter_Visualization_Library_AggregatedPerfValuesForMultipleSeriesGet]


    @ManagementGroup uniqueidentifier,


    @StartTime datetime,


    @EndTime datetime,


    @AggregationType int,


    @TypeofReturnValues int,


    @NumEntities int,


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








        DECLARE


             @SqlStatement nvarchar(max)


            ,@SelectStatement nvarchar(max)


            ,@WhereTimeStatement nvarchar(max)


            ,@GroupByStatement nvarchar(max)


            ,@OrderByStatement nvarchar(max)


            ,@ViewName nvarchar(max)


            ,@ColumnName nvarchar(max)


            ,@FuntionName nvarchar(max)


            ,@CalculatedValueColumnName nvarchar(max)


            ,@AggregateExpression nvarchar(max)





        IF @AggregationType = 0


        BEGIN


            SET @ViewName = 'Perf.vPerfRaw'


            SET @ColumnName = 'vPerf.SampleValue'


            SET @WhereTimeStatement = 'WHERE vPerf.DateTime >= @StartTime AND vPerf.DateTime <= @EndTime '


            SET @AggregateExpression = ' AVG(vPerf.SampleValue) '


        END


        ELSE IF @AggregationType = 20


        BEGIN


            SET @ViewName = 'Perf.vPerfHourly'


            SET @ColumnName = 'vPerf.AverageValue'


            SET @WhereTimeStatement = 'WHERE (vPerf.DateTime >= DATEADD(hh, DATEPART(hh, @StartTime), convert(varchar(8), @StartTime, 112)))


                AND (vPerf.DateTime < DATEADD(hh, DATEPART(hh, @EndTime), convert(varchar(8), @EndTime, 112)))


                '


            SET @AggregateExpression = ' SUM(vPerf.AverageValue * SampleCount) / SUM(SampleCount) '


        END


        ELSE IF @AggregationType = 30


        BEGIN


            SET @ViewName = 'Perf.vPerfDaily'


            SET @ColumnName = 'vPerf.AverageValue'


            SET @WhereTimeStatement = '


                WHERE (vPerf.DateTime >= DATEADD(hh, DATEPART(hh, @StartTime), convert(varchar(8), @StartTime, 112)))


                AND (vPerf.DateTime < DATEADD(hh, DATEPART(hh, @EndTime), convert(varchar(8), @EndTime, 112)))


                '


            SET @AggregateExpression = ' SUM(vPerf.AverageValue * SampleCount) / SUM(SampleCount) '


        END


        ELSE


        BEGIN


            RAISERROR (777971002, 16, 1


                ,0


                ,0


                ,0


                ,N'SDK.Microsoft_SystemCenter_Visualization_Library_TopNEntitiesByPerfGet'


                ,0


                ,N'Invalid AggregationType - only 0, 20, and 30 are supported'


            )


        END





        SET @GroupByStatement = 'GROUP BY vPerf.ManagedEntityRowId, #RPI.ObjectName, #RPI.CounterName, #RPI.InstanceName'





        IF @TypeofReturnValues = 1


        BEGIN


            SET @FuntionName = 'AVG'


            SET @CalculatedValueColumnName = 'Average'


            SET @OrderByStatement = ' ORDER BY Average '





        END


        ELSE


        BEGIN


            RAISERROR (777971002, 16, 1


                ,0


                ,0


                ,0


                ,N'SDK.Microsoft_SystemCenter_Visualization_Library_TopNEntitiesByPerfGet'


                ,0


                ,N'Invalid TypeofReturnValues - only 1 is supported'


            )


        END





        IF @ReturnBottomEntities = 1


        BEGIN


            SET @OrderByStatement = @OrderByStatement + ' ASC '


        END


        ELSE


        BEGIN


            SET @OrderByStatement = @OrderByStatement + ' DESC '


        END








        SET @SqlStatement = '


            SELECT TOP(@NumEntities) vPerf.ManagedEntityRowId, #RPI.ObjectName, #RPI.CounterName, #RPI.InstanceName, ' + @AggregateExpression + ' AS ' +  @CalculatedValueColumnName +  


            ' FROM ' + @ViewName + ' AS vPerf ' + 


            'JOIN #ResolvedPerfInstancesTable #RPI ' +


            'ON (#RPI.ManagedEntityRowId = vPerf.ManagedEntityRowId AND #RPI.PerformanceRuleInstanceRowId  = vPerf.PerformanceRuleInstanceRowId) ' +


            @WhereTimeStatement +


            @GroupByStatement +


            @OrderByStatement





        EXEC sp_executesql @SqlStatement,


            @params = N'


                @StartTime datetime,


                @EndTime datetime,


                @NumEntities int',


            @StartTime = @StartTime,


            @EndTime = @EndTime,


            @NumEntities = @NumEntities








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


GO