IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Visualization_Library_PerfDataWithMinMaxReductionGet' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE SDK.Microsoft_SystemCenter_Visualization_Library_PerfDataWithMinMaxReductionGet AS RETURN 1')
END
GO
ALTER PROCEDURE [SDK].[Microsoft_SystemCenter_Visualization_Library_PerfDataWithMinMaxReductionGet]
@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_PerfDataWithMinMaxReductionGet'
,0
,N'Non existent managed entity'
)
END
IF (@AggregationTypeToQueryFrom != 0 AND
@AggregationTypeToQueryFrom != 20 AND
@AggregationTypeToQueryFrom != 30)
BEGIN
RAISERROR (777971002, 16, 1
,0
,0
,0
,N'SDK.Microsoft_SystemCenter_Visualization_Library_PerfDataWithMinMaxReductionGet'
,0
,N'Invalid AggregationType - only 0, 20, and 30 are supported'
)
END
/* ------------------------------ */
-- Create ##SubIntervalsTable to contain the startTime, endTime of the sub intervals.
-- Dividing by 2 since for each interval we will have a min value and a max value.
SET @IntervalCount = (@NumberOfDataPoints / 2)
SET @IntervalLength = (@DeltaSeconds / @IntervalCount)
WHILE (@IxSubInterval < @IntervalCount)
BEGIN
SET @endSubIntervalDatetime = DATEADD(second, @IntervalLength-1, @startSubIntervalDatetime)
INSERT INTO #SubIntervalsTable VALUES (@startSubIntervalDatetime, @endSubIntervalDatetime)
SET @startSubIntervalDatetime = DATEADD(second, @IntervalLength, @startSubIntervalDatetime)
SET @IxSubInterval = @IxSubInterval + 1
END
DECLARE subinterval_cursor CURSOR
FOR SELECT startTime, endTime
FROM #SubIntervalsTable
OPEN subinterval_cursor;
SET @IxSubInterval = 0;
WHILE (@IxSubInterval < @IntervalCount)
BEGIN
SET @minValueTime = NULL
SET @maxValueTime = NULL
SET @minValueForSubInterval = NULL
SET @maxValueForSubInterval = NULL
SET @numberOfDistinctValues = NULL
FETCH NEXT FROM subinterval_cursor
INTO @startSubIntervalDatetime, @endSubIntervalDatetime;
IF (@AggregationTypeToQueryFrom = 0)
BEGIN
SELECT @minValueForSubInterval = MIN(SampleValue),
@maxValueForSubInterval = MAX(SampleValue),
@numberOfDistinctValues = COUNT(DISTINCT SampleValue)
FROM [Perf].[vPerfRaw]
WHERE ManagedEntityRowId = @ManagedEntityRowId
AND PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
AND DateTime >= @startSubIntervalDatetime
AND DateTime <= @endSubIntervalDatetime
IF (@numberOfDistinctValues >= 2)
BEGIN
SELECT @minValueTime = DateTime
FROM [Perf].[vPerfRaw]
WHERE ManagedEntityRowId = @ManagedEntityRowId
AND PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
AND DateTime >= @startSubIntervalDatetime
AND DateTime <= @endSubIntervalDatetime
AND SampleValue = @minValueForSubInterval
SELECT @maxValueTime = DateTime
FROM [Perf].[vPerfRaw]
WHERE ManagedEntityRowId = @ManagedEntityRowId
AND PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
AND DateTime >= @startSubIntervalDatetime
AND DateTime <= @endSubIntervalDatetime
AND SampleValue = @maxValueForSubInterval
END
END
ELSE IF (@AggregationTypeToQueryFrom = 20)
BEGIN
SELECT @minValueForSubInterval = MIN(AverageValue),
@maxValueForSubInterval = MAX(AverageValue),
@numberOfDistinctValues = COUNT(DISTINCT AverageValue)
FROM [Perf].[vPerfHourly]
WHERE ManagedEntityRowId = @ManagedEntityRowId
AND PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
AND DateTime >= @startSubIntervalDatetime
AND DateTime <= @endSubIntervalDatetime
IF (@numberOfDistinctValues >= 2)
BEGIN
SELECT @minValueTime = DateTime
FROM [Perf].[vPerfHourly]
WHERE ManagedEntityRowId = @ManagedEntityRowId
AND PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
AND DateTime >= @startSubIntervalDatetime
AND DateTime <= @endSubIntervalDatetime
AND AverageValue = @minValueForSubInterval
SELECT @maxValueTime = DateTime
FROM [Perf].[vPerfHourly]
WHERE ManagedEntityRowId = @ManagedEntityRowId
AND PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
AND DateTime >= @startSubIntervalDatetime
AND DateTime <= @endSubIntervalDatetime
AND AverageValue = @maxValueForSubInterval
END
END
ELSE IF (@AggregationTypeToQueryFrom = 30)
BEGIN
SELECT @minValueForSubInterval = MIN(AverageValue),
@maxValueForSubInterval = MAX(AverageValue),
@numberOfDistinctValues = COUNT(DISTINCT AverageValue)
FROM [Perf].[vPerfDaily]
WHERE ManagedEntityRowId = @ManagedEntityRowId
AND PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
AND DateTime >= @startSubIntervalDatetime
AND DateTime <= @endSubIntervalDatetime
IF (@numberOfDistinctValues >= 2)
BEGIN
SELECT @minValueTime = DateTime
FROM [Perf].[vPerfDaily]
WHERE ManagedEntityRowId = @ManagedEntityRowId
AND PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
AND DateTime >= @startSubIntervalDatetime
AND DateTime <= @endSubIntervalDatetime
AND AverageValue = @minValueForSubInterval
SELECT @maxValueTime = DateTime
FROM [Perf].[vPerfDaily]
WHERE ManagedEntityRowId = @ManagedEntityRowId
AND PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
AND DateTime >= @startSubIntervalDatetime
AND DateTime <= @endSubIntervalDatetime
AND AverageValue = @maxValueForSubInterval
END
END
ELSE
BEGIN
SET @minValueTime = NULL
SET @maxValueTime = NULL
SET @minValueForSubInterval = NULL
SET @maxValueForSubInterval = NULL
END
IF (@numberOfDistinctValues = 0)
BEGIN
INSERT INTO #MinMaxValuesTable VALUES(@startSubIntervalDatetime, NULL)
INSERT INTO #MinMaxValuesTable VALUES(@endSubIntervalDatetime, NULL)
END
ELSE IF (@numberOfDistinctValues = 1)
BEGIN
-- @minValueForSubInterval must be equal to @maxValueForSubInterval
INSERT INTO #MinMaxValuesTable VALUES(@startSubIntervalDatetime, @minValueForSubInterval)
INSERT INTO #MinMaxValuesTable VALUES(@endSubIntervalDatetime, @maxValueForSubInterval)
END
ELSE IF (@numberOfDistinctValues >= 2)
BEGIN
INSERT INTO #MinMaxValuesTable VALUES(@minValueTime, @minValueForSubInterval)
INSERT INTO #MinMaxValuesTable VALUES(@maxValueTime, @maxValueForSubInterval)
END
SET @IxSubInterval = @IxSubInterval + 1
END
CLOSE subinterval_cursor;
DEALLOCATE subinterval_cursor;
SELECT * FROM #MinMaxValuesTable
ORDER BY dataPointTime ASC
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_PerfDataWithMinMaxReductionGet] TO OpsMgrReader
GO
Home