IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Visualization_Library_GetCurrentManagedEntityState' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE SDK.Microsoft_SystemCenter_Visualization_Library_GetCurrentManagedEntityState AS RETURN 1')
END
GO
ALTER PROCEDURE [SDK].[Microsoft_SystemCenter_Visualization_Library_GetCurrentManagedEntityState]
@ManagedEntityRowId int,
@HealthState int OUTPUT
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 @Offset int
BEGIN TRY
-- This table will be used to hold the health states of the given entity
-- as measured by the various methods we track state.
-- The state with the latest time measured and highest state is the
-- current state
CREATE TABLE #HealthStateTable(
HealthState int
,TimeMeasured datetime)
-- State as determined by the latest state change event
-- Find the right offset to use based on the retention settings
-- This allows us to constrain the raw data search to just the
SELECT @Offset = MaxDataAgeDays
FROM StandardDatasetAggregation sda
INNER JOIN StandardDataset d ON d.DatasetId = sda.DatasetId
WHERE d.SchemaName = 'State'
AND AggregationTypeId = 0
INSERT INTO #HealthStateTable
SELECT TOP 1
SR.NewHealthState
,SR.DateTime
FROM State.vStateRaw AS SR
INNER JOIN vManagedEntityMonitor AS MEM ON MEM.ManagedEntityMonitorRowId = SR.ManagedEntityMonitorRowId
INNER JOIN vMonitor AS M ON M.MonitorRowId = MEM.MonitorRowId
WHERE SR.DateTime BETWEEN DATEADD(dd, -@Offset, GETUTCDATE()) AND GETUTCDATE()
AND MEM.ManagedEntityRowId = @ManagedEntityRowId
AND M.MonitorSystemName = 'System.Health.EntityState'
ORDER BY SR.DateTime desc
-- State as determined by hourly aggregation
SELECT @Offset = MaxDataAgeDays
FROM StandardDatasetAggregation sda
INNER JOIN StandardDataset d ON d.DatasetId = sda.DatasetId
WHERE d.SchemaName = 'State'
AND AggregationTypeId = 20
INSERT INTO #HealthStateTable
SELECT TOP 1
SR.IntervalEndHealthState
,DATEADD(hh, 1, SR.DateTime)
FROM State.vStateHourly AS SR
INNER JOIN vManagedEntityMonitor AS MEM ON MEM.ManagedEntityMonitorRowId = SR.ManagedEntityMonitorRowId
INNER JOIN vMonitor AS M ON M.MonitorRowId = MEM.MonitorRowId
WHERE SR.DateTime BETWEEN DATEADD(dd, -@Offset, GETUTCDATE()) AND GETUTCDATE()
AND MEM.ManagedEntityRowId = @ManagedEntityRowId
AND M.MonitorSystemName = 'System.Health.EntityState'
ORDER BY SR.DateTime desc
-- State as determined by being in planned or unplanned maintenance mode
-- 101 is Planned maintenance mode
-- 100 is Unplanned maintenance mode
INSERT INTO #HealthStateTable
SELECT TOP 1
100 + PlannedMaintenanceInd
,'99991231'
FROM vMaintenanceMode AS MM
INNER JOIN vManagedEntity AS ME ON MM.ManagedEntityRowId = ME.ManagedEntityRowId
WHERE ME.ManagedEntityRowId = @ManagedEntityRowId
AND MM.PlannedMaintenanceInd = 1
AND MM.EndDateTime is NULL
ORDER BY MM.StartDateTime desc
-- State as determined by health service outage
INSERT INTO #HealthStateTable
SELECT TOP 1
50
,'99991231'
FROM vRelationship AS R
INNER JOIN vRelationshipType AS RT ON RT.RelationshipTypeRowId = R.RelationshipTypeRowId
INNER JOIN vManagedEntity AS ME ON ME.TopLevelHostManagedEntityRowId = R.TargetManagedEntityRowId
INNER JOIN vHealthServiceOutage AS HSO ON HSO.ManagedEntityRowId = R.SourceManagedEntityRowId
WHERE ME.ManagedEntityRowId = @ManagedEntityRowId
AND RT.RelationshipTypeSystemName = 'Microsoft.SystemCenter.HealthServiceManagesEntity'
AND HSO.EndDateTime is NULL
AND NOT EXISTS
(SELECT * FROM vHealthServiceOutage as HSO2
WHERE HSO2.DWLastModifiedDateTime = HSO.DWLastModifiedDateTime
AND HSO2.ManagedEntityRowId = HSO.ManagedEntityRowId
AND HSO2.ReasonCode = HSO.ReasonCode
AND HSO2.RootHealthServiceInd = HSO.RootHealthServiceInd
AND HSO2.StartDateTime = HSO.StartDateTime
AND HSO2.EndDateTime IS NOT NULL)
ORDER BY HSO.StartDateTime DESC
-- Choose the latest state with the highest state value
-- No information means it is state 1 (green)
SELECT TOP 1 @HealthState = ISNULL(HST.HealthState, 1)
FROM #HealthStateTable AS HST
ORDER BY HST.TimeMeasured DESC, HST.HealthState DESC
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_GetCurrentManagedEntityState] TO OpsMgrReader
GO
Home