IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Visualization_Library_PerfDataWithEveryNthReductionGet' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE SDK.Microsoft_SystemCenter_Visualization_Library_PerfDataWithEveryNthReductionGet AS RETURN 1')
END
GO
ALTER PROCEDURE [SDK].[Microsoft_SystemCenter_Visualization_Library_PerfDataWithEveryNthReductionGet]
@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_PerfDataWithEveryNthReductionGet'
,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_PerfDataWithEveryNthReductionGet'
,0
,N'Invalid AggregationType - only 0, 20, and 30 are supported'
)
END
/* ------------------------------ */
CREATE TABLE #EveryNthValuesTable(
dataPointTime DateTime,
value float
)
DECLARE @ActualDataPointCount int = 0
DECLARE @IxPerfData int = 0
DECLARE @DataPointTime DateTime
DECLARE @DataPointValue float
DECLARE @NumGaps int = 0
DECLARE @IntervalLengthInGaps float = 0
DECLARE @LastPickedIntervalIndex int = -1
DECLARE @CurrentIntervalIndex int = 0
-- Get the actual count of data points that we have.
IF (@AggregationTypeToQueryFrom = 0)
BEGIN
SELECT @ActualDataPointCount = COUNT(*)
FROM [Perf].[vPerfRaw]
WHERE ManagedEntityRowId = @ManagedEntityRowId
AND PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
AND DateTime >= @StartTime
AND DateTime <= @EndTime
IF (@ActualDataPointCount <= @NumberOfDataPoints)
BEGIN
SELECT DateTime, SampleValue
FROM [Perf].[vPerfRaw]
WHERE ManagedEntityRowId = @ManagedEntityRowId
AND PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
AND DateTime >= @StartTime
AND DateTime <= @EndTime
RETURN
END
DECLARE rawPerfData_cursor CURSOR
FOR SELECT DateTime, SampleValue
FROM [Perf].[vPerfRaw]
WHERE ManagedEntityRowId = @ManagedEntityRowId
AND PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
AND DateTime >= @StartTime
AND DateTime <= @EndTime
OPEN rawPerfData_cursor;
SET @IxPerfData = 0;
SET @NumGaps = @ActualDataPointCount - 1
SET @IntervalLengthInGaps = ( CONVERT(float, @NumGaps) / CONVERT(float, @NumberOfDataPoints))
SET @LastPickedIntervalIndex = -1
SET @CurrentIntervalIndex = 0
WHILE (@IxPerfData < @ActualDataPointCount)
BEGIN
FETCH NEXT FROM rawPerfData_cursor
INTO @DataPointTime, @DataPointValue;
SET @CurrentIntervalIndex = CONVERT(int, (@IxPerfData / @IntervalLengthInGaps))
IF (@LastPickedIntervalIndex != @CurrentIntervalIndex)
BEGIN
INSERT INTO #EveryNthValuesTable
VALUES (@DataPointTime, @DataPointValue)
SET @LastPickedIntervalIndex = @LastPickedIntervalIndex + 1
IF (@LastPickedIntervalIndex = (@NumberOfDataPoints - 1))
BEGIN
BREAK
END
END
SET @IxPerfData = @IxPerfData + 1
END
CLOSE rawPerfData_cursor;
DEALLOCATE rawPerfData_cursor;
END
ELSE IF (@AggregationTypeToQueryFrom = 20)
BEGIN
SELECT @ActualDataPointCount = COUNT(*)
FROM [Perf].[vPerfHourly]
WHERE ManagedEntityRowId = @ManagedEntityRowId
AND PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
AND DateTime >= @StartTime
AND DateTime <= @EndTime
IF (@ActualDataPointCount <= @NumberOfDataPoints)
BEGIN
SELECT DateTime, AverageValue
FROM [Perf].[vPerfHourly]
WHERE ManagedEntityRowId = @ManagedEntityRowId
AND PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
AND DateTime >= @StartTime
AND DateTime <= @EndTime
RETURN
END
DECLARE hourlyPerfData_cursor CURSOR
FOR SELECT DateTime, AverageValue
FROM [Perf].[vPerfHourly]
WHERE ManagedEntityRowId = @ManagedEntityRowId
AND PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
AND DateTime >= @StartTime
AND DateTime <= @EndTime
OPEN hourlyPerfData_cursor;
SET @IxPerfData = 0;
SET @NumGaps = @ActualDataPointCount - 1
SET @IntervalLengthInGaps = ( CONVERT(float, @NumGaps) / CONVERT(float, @NumberOfDataPoints))
SET @LastPickedIntervalIndex = -1
SET @CurrentIntervalIndex = 0
WHILE (@IxPerfData < @ActualDataPointCount)
BEGIN
FETCH NEXT FROM hourlyPerfData_cursor
INTO @DataPointTime, @DataPointValue;
SET @CurrentIntervalIndex = CONVERT(int, (@IxPerfData / @IntervalLengthInGaps))
IF (@LastPickedIntervalIndex != @CurrentIntervalIndex)
BEGIN
INSERT INTO #EveryNthValuesTable
VALUES (@DataPointTime, @DataPointValue)
SET @LastPickedIntervalIndex = @LastPickedIntervalIndex + 1
IF (@LastPickedIntervalIndex = (@NumberOfDataPoints - 1))
BEGIN
BREAK
END
END
SET @IxPerfData = @IxPerfData + 1
END
CLOSE hourlyPerfData_cursor;
DEALLOCATE hourlyPerfData_cursor;
END
ELSE IF (@AggregationTypeToQueryFrom = 30)
BEGIN
SELECT @ActualDataPointCount = COUNT(*)
FROM [Perf].[vPerfDaily]
WHERE ManagedEntityRowId = @ManagedEntityRowId
AND PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
AND DateTime >= @StartTime
AND DateTime <= @EndTime
IF (@ActualDataPointCount <= @NumberOfDataPoints)
BEGIN
SELECT DateTime, AverageValue
FROM [Perf].[vPerfDaily]
WHERE ManagedEntityRowId = @ManagedEntityRowId
AND PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
AND DateTime >= @StartTime
AND DateTime <= @EndTime
RETURN
END
DECLARE dailyPerfData_cursor CURSOR
FOR SELECT DateTime, AverageValue
FROM [Perf].[vPerfDaily]
WHERE ManagedEntityRowId = @ManagedEntityRowId
AND PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
AND DateTime >= @StartTime
AND DateTime <= @EndTime
OPEN dailyPerfData_cursor;
SET @IxPerfData = 0;
SET @NumGaps = @ActualDataPointCount - 1
SET @IntervalLengthInGaps = ( CONVERT(float, @NumGaps) / CONVERT(float, @NumberOfDataPoints))
SET @LastPickedIntervalIndex = -1
SET @CurrentIntervalIndex = 0
WHILE (@IxPerfData < @ActualDataPointCount)
BEGIN
FETCH NEXT FROM dailyPerfData_cursor
INTO @DataPointTime, @DataPointValue;
SET @CurrentIntervalIndex = CONVERT(int, (@IxPerfData / @IntervalLengthInGaps))
IF (@LastPickedIntervalIndex != @CurrentIntervalIndex)
BEGIN
INSERT INTO #EveryNthValuesTable
VALUES (@DataPointTime, @DataPointValue)
SET @LastPickedIntervalIndex = @LastPickedIntervalIndex + 1
IF (@LastPickedIntervalIndex = (@NumberOfDataPoints - 1))
BEGIN
BREAK
END
END
SET @IxPerfData = @IxPerfData + 1
END
CLOSE dailyPerfData_cursor;
DEALLOCATE dailyPerfData_cursor;
END
ELSE
BEGIN
SET @ActualDataPointCount = 0
RETURN
END
SELECT * FROM #EveryNthValuesTable
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_PerfDataWithEveryNthReductionGet] TO OpsMgrReader
GO
Home