IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Visualization_Library_GetAggregatedPerformanceSeries' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE SDK.Microsoft_SystemCenter_Visualization_Library_GetAggregatedPerformanceSeries AS RETURN 1')
END
GO
ALTER PROCEDURE [sdk].[Microsoft_SystemCenter_Visualization_Library_GetAggregatedPerformanceSeries]
@ManagementGroup uniqueidentifier
,@StartTime datetime
,@EndTime datetime
,@SeriesCollectionXml nvarchar(max)
,@NumberOfDataPoints int
,@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
BEGIN TRY
IF ((@AggregationType != 1) AND ((@AggregationType != 2)))
BEGIN
RAISERROR (777971002, 16, 1
,0
,0
,0
,N'SDK.Microsoft_SystemCenter_Visualization_Library_GetAggregatedPerformanceSeries'
,0
,N'Not supported aggregation type. Only 1 and 2 are supported.'
)
END
CREATE TABLE #SeriesTable(
MEId nvarchar(64),
ManagedEntityRowId int,
PerfRuleInstanceRowId int)
-- Parse the @SeriesCollectionXml to get the ME ID's and PerfRuleInstanceRowId's
DECLARE @xmlhandle int
IF NOT @ExecError = 0
RAISERROR(777971000, 16, 1
,'SeriesCollectionXml'
,@ExecError)
-- Get the series information and resolve the ME and MG GUIDs to RowIds
INSERT INTO #SeriesTable (MEId, ManagedEntityRowId, PerfRuleInstanceRowId)
SELECT MEId, ME.ManagedEntityRowId, PerfRuleInstanceRowId
FROM
OPENXML(@xmlhandle, '/SeriesCollection/Series', 2)
WITH
(MEId nvarchar(64),
PerfRuleInstanceRowId int)
JOIN vManagementGroup as MG
ON MG.ManagementGroupGuid = @ManagementGroup
JOIN vManagedEntity as ME
ON MEId = ME.ManagedEntityGuid AND ME.ManagementGroupRowId = MG.ManagementGroupRowId
-- Create ##SubIntervalsTable to contain the startTime, endTime and the average
-- value of the sub intervals.
SET @IntervalLength = (@DeltaSeconds / @NumberOfDataPoints)
WHILE (@IxSubInterval < @NumberOfDataPoints)
BEGIN
SET @endSubIntervalDatetime = DATEADD(second, @IntervalLength-1, @startSubIntervalDatetime)
INSERT INTO #SubIntervalsTable VALUES (@startSubIntervalDatetime, @endSubIntervalDatetime, null)
SET @startSubIntervalDatetime = DATEADD(second, @IntervalLength, @startSubIntervalDatetime)
SET @IxSubInterval = @IxSubInterval + 1
END
-- Horizontally aggregate each series down to the requested number of data points,
-- This is done by dividing the time period into @NumberOfDataPoints even time slices
-- and averaging the points in each time slice.
CREATE TABLE #DatapointsTable(
SampleTime DateTime,
SampleValue float)
IF (@IntervalLength < 7200)
BEGIN
-- Intervals are too small for hourly, use raw
INSERT INTO #DatapointsTable
SELECT S.startTime, AVG(P.SampleValue) FROM #SubIntervalsTable S
LEFT OUTER JOIN [Perf].[vPerfRaw] P
ON (P.DateTime >= S.startTime) AND (P.DateTime < S.endTime)
INNER JOIN #SeriesTable ST
ON (P.ManagedEntityRowId = ST.ManagedEntityRowId) AND (P.PerformanceRuleInstanceRowId = ST.PerfRuleInstanceRowId)
GROUP BY S.startTime, P.ManagedEntityRowId, P.PerformanceRuleInstanceRowId
ORDER BY S.startTime
END
ELSE IF (@IntervalLength < 172800)
BEGIN
-- Intervals are too small for daily, use hourly
INSERT INTO #DatapointsTable
SELECT S.startTime, AVG(P.AverageValue) FROM #SubIntervalsTable S
LEFT OUTER JOIN [Perf].[vPerfHourly] P
ON (P.DateTime >= S.startTime) AND (P.DateTime < S.endTime)
INNER JOIN #SeriesTable ST
ON (P.ManagedEntityRowId = ST.ManagedEntityRowId) AND (P.PerformanceRuleInstanceRowId = ST.PerfRuleInstanceRowId)
GROUP BY S.startTime, P.ManagedEntityRowId, P.PerformanceRuleInstanceRowId
ORDER BY S.startTime
END
ELSE
BEGIN
-- Intervals are longer than or equal to daily, so use them
INSERT INTO #DatapointsTable
SELECT S.startTime, AVG(P.AverageValue) FROM #SubIntervalsTable S
LEFT OUTER JOIN [Perf].[vPerfDaily] P
ON (P.DateTime >= S.startTime) AND (P.DateTime < S.endTime)
INNER JOIN #SeriesTable ST
ON (P.ManagedEntityRowId = ST.ManagedEntityRowId) AND (P.PerformanceRuleInstanceRowId = ST.PerfRuleInstanceRowId)
GROUP BY S.startTime, P.ManagedEntityRowId, P.PerformanceRuleInstanceRowId
ORDER BY S.startTime
END
-- Vertically aggregate for each sample time according to the aggregation type:
-- @@AggregationType values
-- 1- Average
-- 2- Sum
IF (@AggregationType = 1)
BEGIN
SELECT DP.SampleTime, AVG(DP.SampleValue) from #DatapointsTable DP
GROUP BY DP.SampleTime
END
ELSE IF (@AggregationType = 2)
BEGIN
SELECT DP.SampleTime, SUM(DP.SampleValue) from #DatapointsTable DP
GROUP BY DP.SampleTime
END
ELSE
BEGIN
RAISERROR (777971002, 16, 1
,0
,0
,0
,N'SDK.Microsoft_SystemCenter_Visualization_Library_GetAggregatedPerformanceSeries'
,0
,N'Invalid RequestedDataPointType - only 1 and 2 are supported'
)
END
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_GetAggregatedPerformanceSeries] TO OpsMgrReader
GO
Home