IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Visualization_Library_GetPerformanceSeriesStatistics' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE SDK.Microsoft_SystemCenter_Visualization_Library_GetPerformanceSeriesStatistics AS RETURN 1')
END
GO
ALTER PROCEDURE [SDK].[Microsoft_SystemCenter_Visualization_Library_GetPerformanceSeriesStatistics]
@ManagementGroup uniqueidentifier,
@ManagedEntityXml nvarchar(max),
@StartTime datetime,
@EndTime datetime,
@ObjectName nvarchar(max),
@CounterName nvarchar(max),
@InstanceName nvarchar(max) = '',
@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 @return_value int
BEGIN TRY
-- Create #ManagedEntityTable to contain the data for all of the managed entities that we care about
if NOT @return_value = 0
RAISERROR(777971000, 16, 1
,'ManagedEntityXml'
,@return_value)
INSERT INTO #ManagedEntityTable (ManagedEntityGuid, AverageValue, MinimumValue, MaximumValue)
SELECT ID,0,0,0
FROM
OPENXML(@xmlhandle, '/ManagedEntities/ManagedEntity', 2)
WITH
(ID nvarchar(max))
UPDATE #ManagedEntityTable set ManagedEntityRowId = ME.ManagedEntityRowId
FROM vManagedEntity as ME
INNER JOIN vManagementGroup as MG on ME.ManagementGroupRowId = MG.ManagementGroupRowId
INNER JOIN #ManagedEntityTable as MET ON MET.ManagedEntityGuid = ME.ManagedEntityGuid
WHERE MET.ManagedEntityGuid = ME.ManagedEntityGuid
AND MG.ManagementGroupGuid = @ManagementGroup
-- #ManagedEntityTable is now set up
DECLARE
@AverageValue float
,@MinimumValue float
,@MaximumValue float
,@ManagedEntityRowId int
SET @ManagedEntityRowId = 0
WHILE EXISTS (SELECT * FROM #ManagedEntityTable AS MET WHERE MET.ManagedEntityRowId > @ManagedEntityRowId)
BEGIN
SELECT TOP 1 @ManagedEntityRowId = MET.ManagedEntityRowId
FROM #ManagedEntityTable AS MET
WHERE MET.ManagedEntityRowId > @ManagedEntityRowId
ORDER BY MET.ManagedEntityRowId ASC
SELECT ManagedEntityGuid as ManagedEntityGuid, AverageValue as AverageValue, MinimumValue as MinimumValue,MaximumValue as MaximumValue
FROM #ManagedEntityTable
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- 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_GetPerformanceSeriesStatistics] TO OpsMgrReader
GO