GetPerformanceSeries.SP.sql (Resource)

Element properties:

Source Code:

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

File Content: GetPerformanceSeries.SP.sql

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


BEGIN


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


END


GO





ALTER PROCEDURE [SDK].[Microsoft_SystemCenter_Visualization_Library_GetPerformanceSeries]


     @ManagementGroup uniqueidentifier


    ,@ManagedEntityGuid uniqueidentifier


    ,@StartTime datetime


    ,@EndTime datetime


    ,@ObjectName nvarchar(256)


    ,@CounterName nvarchar(256)


    ,@InstanceName nvarchar(256) = ''


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


         @ManagedEntityRowId int





    BEGIN TRY





        SELECT @ManagedEntityRowId = ME.ManagedEntityRowId FROM vManagedEntity as ME


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


        WHERE ME.ManagedEntityGuid = @ManagedEntityGuid


            AND MG.ManagementGroupGuid = @ManagementGroup





        IF @ManagedEntityRowId IS NULL


        BEGIN


            RAISERROR (777971002, 16, 1


                ,0


                ,0


                ,0


                ,N'SDK.GetPerformanceSeries'


                ,0


                ,N'Non existent managed entity'


            )


        END





        IF @ObjectName = '' OR @ObjectName IS NULL


        BEGIN


            RAISERROR (777971002, 16, 1


                ,0


                ,0


                ,0


                ,N'SDK.GetPerformanceSeries'


                ,0


                ,N'NULL or empty ObjectName'


            )


        END





        IF @CounterName = '' OR @CounterName IS NULL


        BEGIN


            RAISERROR (777971002, 16, 1


                ,0


                ,0


                ,0


                ,N'SDK.GetPerformanceSeries'


                ,0


                ,N'NULL or empty CounterName'


            )


        END





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





        DECLARE @SelectStatement nvarchar(max)


        DECLARE @WhereStatement nvarchar(max)


        DECLARE @TimeStatement nvarchar(max)


        DECLARE @SqlStatement nvarchar(max)


        DECLARE @ViewName nvarchar(max)


        DECLARE @ColumnName nvarchar(max)





        IF @AggregationType = 0


        BEGIN


            SET @ViewName = 'Perf.vPerfRaw'


            SET @ColumnName = 'vPerf.SampleValue'


            SET @TimeStatement = '


                AND vPerf.DateTime >= @StartTime


                AND vPerf.DateTime <= @EndTime


                '


        END


        ELSE IF @AggregationType = 20


        BEGIN


            SET @ViewName = 'Perf.vPerfHourly'


            SET @ColumnName = 'vPerf.AverageValue'


            SET @TimeStatement = '


                AND (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)))


                '


        END


        ELSE IF @AggregationType = 30


        BEGIN


            SET @ViewName = 'Perf.vPerfDaily'


            SET @ColumnName = 'vPerf.AverageValue'


            SET @TimeStatement = '


                AND (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)))


                '


        END


        ELSE


        BEGIN


            RAISERROR (777971002, 16, 1


                ,0


                ,0


                ,0


                ,N'SDK.GetPerformanceSeries'


                ,0


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


            )


        END








        SET @SelectStatement = '


            SELECT DateTime,' + @ColumnName + ' FROM ' + @ViewName + ' AS vPerf INNER JOIN


                vManagedEntity AS ME ON vPerf.ManagedEntityRowId = ME.ManagedEntityRowId INNER JOIN


                vPerformanceRuleInstance AS pRI ON pRI.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN


                vRule AS vR ON vR.RuleRowId = pRI.RuleRowId INNER JOIN


                vPerformanceRule as pR ON pR.RuleRowId = pRI.RuleRowId


                '





        IF @InstanceName = ''


            SET @WhereStatement = '


                WHERE vPerf.ManagedEntityRowId = @ManagedEntityRowId


                    AND pR.ObjectName = @ObjectName


                    AND pR.CounterName = @CounterName


                    '


        ELSE


            SET @WhereStatement = '


                WHERE vPerf.ManagedEntityRowId = @ManagedEntityRowId


                    AND pR.ObjectName = @ObjectName


                    AND pR.CounterName = @CounterName


                    AND pRI.InstanceName = @InstanceName


                    '


    


        SET @SqlStatement = @SelectStatement + @WhereStatement + @TimeStatement + 'ORDER BY vPerf.DateTime ASC'





        EXEC sp_executesql @SqlStatement,


            @params = N'


                @ManagedEntityRowId int,


                @StartTime datetime,


                @EndTime datetime,


                @ObjectName nvarchar(256),


                @CounterName nvarchar(256),


                @InstanceName nvarchar(256)',


            @ManagedEntityRowId = @ManagedEntityRowId,


            @StartTime = @StartTime,


            @EndTime = @EndTime,


            @ObjectName = @ObjectName,


            @CounterName = @CounterName,


            @InstanceName = @InstanceName


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


GO