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
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'
-- 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