IF NOT EXISTS (SELECT * FROM sys.types WHERE name = 'Microsoft_SystemCenter_Visualization_Library_RawValuesTableType' AND schema_id = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE TYPE SDK.Microsoft_SystemCenter_Visualization_Library_RawValuesTableType AS TABLE ( DataPointTime datetime, Value float )')
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Visualization_Library_SinglePerformanceDataSeriesGet' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE SDK.Microsoft_SystemCenter_Visualization_Library_SinglePerformanceDataSeriesGet AS RETURN 1')
END
GO
ALTER PROCEDURE [SDK].[Microsoft_SystemCenter_Visualization_Library_SinglePerformanceDataSeriesGet]
@ManagementGroup uniqueidentifier
,@StartTime datetime
,@EndTime datetime
,@ManagedEntityGuid uniqueidentifier
,@PerfRuleInstanceRowId int
,@NumberOfDataPoints int
,@RequestedDataPointType 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
DECLARE @RowCount 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.SinglePerformanceDataSeriesGet'
,0
,N'Non existent managed entity'
)
END
-- @RequestedDataPointType values
-- 1- Raw
-- 2- Hourly
-- 3- Daily
-- 4- Average
-- 5- MinMax
-- 6- EveryNth
IF (@RequestedDataPointType = 1)
BEGIN
EXEC [SDK].[Microsoft_SystemCenter_Visualization_Library_PerfDataWithNoReductionGet]
@StartTime,
@EndTime,
@ManagedEntityRowId,
@PerfRuleInstanceRowId,
0,
@RowCount OUTPUT
END
ELSE IF (@RequestedDataPointType = 2)
BEGIN
END
ELSE IF ((@RequestedDataPointType = 4) OR
(@RequestedDataPointType = 5) OR
(@RequestedDataPointType = 6))
BEGIN
DECLARE @NumDataPointsInDaily int
DECLARE @NumDataPointsInHourly int
DECLARE @NumDataPointsInRaw int
DECLARE @FudgeFactorInPercent float = 5.0
DECLARE @Fudge int
/* Number of data points we can find in the daily table for this interval*/
SELECT @NumDataPointsInDaily = (DATEDIFF(day, @StartTime , @EndTime) - 1);
/* Number of data points we can find in the hourly table for this interval*/
SELECT @NumDataPointsInHourly = (DATEDIFF(hour, @StartTime , @EndTime) - 1);
SET @Fudge = (@NumberOfDataPoints * (@FudgeFactorInPercent / 100));
/* @Fudge is moved to the other side of the equation to prevent overflows*/
IF ((@NumDataPointsInDaily - @Fudge)> (@NumberOfDataPoints))
BEGIN
EXEC [SDK].[Microsoft_SystemCenter_Visualization_Library_PerfDataWithReductionGet]
@StartTime,
@EndTime,
@ManagedEntityRowId,
@PerfRuleInstanceRowId,
@NumberOfDataPoints,
30,
@RequestedDataPointType
END
ELSE IF (( (@NumDataPointsInDaily - @Fudge) <= (@NumberOfDataPoints) ) AND
( @NumDataPointsInDaily >= (@NumberOfDataPoints - @Fudge) ))
BEGIN
/* Return the data from the daily table with no data point reduction. */
EXEC [SDK].[Microsoft_SystemCenter_Visualization_Library_PerfDataWithNoReductionGet]
@StartTime,
@EndTime,
@ManagedEntityRowId,
@PerfRuleInstanceRowId,
30,
@RowCount OUTPUT
END
ELSE IF ((@NumDataPointsInHourly - @Fudge)> (@NumberOfDataPoints))
BEGIN
EXEC [SDK].[Microsoft_SystemCenter_Visualization_Library_PerfDataWithReductionGet]
@StartTime,
@EndTime,
@ManagedEntityRowId,
@PerfRuleInstanceRowId,
@NumberOfDataPoints,
20,
@RequestedDataPointType
END
ELSE IF (( (@NumDataPointsInHourly - @Fudge)<= (@NumberOfDataPoints) ) AND
( @NumDataPointsInHourly >= (@NumberOfDataPoints - @Fudge) ))
BEGIN
/* Return the data from the daily table with no data point reduction. */
EXEC [SDK].[Microsoft_SystemCenter_Visualization_Library_PerfDataWithNoReductionGet]
@StartTime,
@EndTime,
@ManagedEntityRowId,
@PerfRuleInstanceRowId,
20,
@RowCount OUTPUT
END
ELSE
BEGIN
-- Get the number of data points in the raw table.
/* Declare a variable that references the type. */
DECLARE @RawValuesTVP
AS SDK.Microsoft_SystemCenter_Visualization_Library_RawValuesTableType;
/* Return the data from the raw table with no data point reduction. */
/* Store it in a temp table. */
INSERT INTO @RawValuesTVP ( DataPointTime , Value )
EXEC [SDK].[Microsoft_SystemCenter_Visualization_Library_PerfDataWithNoReductionGet]
@StartTime,
@EndTime,
@ManagedEntityRowId,
@PerfRuleInstanceRowId,
0,
@RowCount OUTPUT
IF ((@RowCount - @Fudge)> (@NumberOfDataPoints))
BEGIN
/* Return the data from the temporary table of data points */
SELECT DataPointTime, Value
FROM @RawValuesTVP
END
END
END
ELSE
BEGIN
RAISERROR (777971002, 16, 1
,0
,0
,0
,N'SDK.SinglePerformanceDataSeriesGet'
,0
,N'Invalid RequestedDataPointType - only 1, 2, 3, 4, 5, and 6 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_SinglePerformanceDataSeriesGet] TO OpsMgrReader
GRANT EXECUTE ON TYPE::[SDK].[Microsoft_SystemCenter_Visualization_Library_RawValuesTableType] TO OpsMgrReader
GO
Home