IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Visualization_Library_GetManagedEntityAvailability' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE SDK.Microsoft_SystemCenter_Visualization_Library_GetManagedEntityAvailability AS RETURN 1')
END
GO
ALTER PROCEDURE [SDK].[Microsoft_SystemCenter_Visualization_Library_GetManagedEntityAvailability]
@ManagementGroup uniqueidentifier
,@ManagedEntityGuid uniqueidentifier
,@StartTime datetime
,@EndTime datetime
,@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)
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.GetManagedEntityAvailability'
,0
,N'Non existent managed entity'
)
END
IF @AggregationType = 30
BEGIN
SET @StateView = N'vStateDailyFull'
SET @WhereStatement = '
WHERE ME.ManagedEntityRowId = @ManagedEntityRowId
AND M.MonitorSystemName = ''System.Health.AvailabilityState''
AND (S.DateTime >= DATEADD(hh, DATEPART(hh, @StartTime) + 1, convert(varchar(8), @StartTime, 112)))
AND (S.DateTime < DATEADD(hh, DATEPART(hh, @EndTime) + 1, convert(varchar(8), @EndTime, 112)))
AND (S.Date BETWEEN DATEADD(day, - 1, @StartTime) AND DATEADD(day, 1, @EndTime))
'
END
ELSE IF @AggregationType = 20
BEGIN
SET @StateView = N'vStateHourlyFull'
SET @WhereStatement = '
WHERE ME.ManagedEntityRowId = @ManagedEntityRowId
AND M.MonitorSystemName = ''System.Health.AvailabilityState''
AND (S.DateTime >= DATEADD(hh, DATEPART(hh, @StartTime), convert(varchar(8), @StartTime, 112)))
AND (S.DateTime < DATEADD(hh, DATEPART(hh, @EndTime), convert(varchar(8), @EndTime, 112)))
AND (S.Date BETWEEN DATEADD(day, - 1, @StartTime) AND DATEADD(day, 1, @EndTime))
'
END
ELSE
BEGIN
RAISERROR (777971002, 16, 1
,0
,0
,0
,N'SDK.GetManagedEntityAvailability'
,0
,N'Invalid AggregationType - only 20, and 30 are supported'
)
END
SET @SelectStatement = '
SELECT SUM(CAST(S.IntervalDurationMilliseconds AS BIGINT)) AS IntervalDurationMilliseconds,
SUM(CAST(S.InWhiteStateMilliseconds AS BIGINT)) AS InWhiteStateMilliseconds,
SUM(CAST(S.InGreenStateMilliseconds AS BIGINT)) AS InGreenStateMilliseconds,
SUM(CAST(S.InYellowStateMilliseconds AS BIGINT)) AS InYellowStateMilliseconds,
SUM(CAST(S.InRedStateMilliseconds AS BIGINT)) AS InRedStateMilliseconds,
SUM(CAST(S.InPlannedMaintenanceMilliseconds AS BIGINT)) AS InPlannedMaintenanceMilliseconds,
SUM(CAST(S.InUnplannedMaintenanceMilliseconds AS BIGINT)) AS InUnplannedMaintenanceMilliseconds,
SUM(CAST(S.InDisabledStateMilliseconds AS BIGINT)) AS InDisabledStateMilliseconds,
SUM(CAST(S.HealthServiceUnavailableMilliseconds AS BIGINT)) AS HealthServiceUnavailableMilliseconds
FROM ' + @StateView + ' AS S
INNER JOIN vManagedEntity AS ME ON ME.ManagedEntityRowId = S.ManagedEntityRowId
INNER JOIN vMonitor AS M ON M.MonitorRowId = S.MonitorRowId
'
SET @SqlStatement = @SelectStatement + @WhereStatement
EXEC sp_executesql @SqlStatement,
@params = N'
@ManagedEntityRowId int,
@StartTime datetime,
@EndTime datetime',
@ManagedEntityRowId = @ManagedEntityRowId,
@StartTime = @StartTime,
@EndTime = @EndTime
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_GetManagedEntityAvailability] TO OpsMgrReader
GO
Home