Creating Reports for SCOM and SCVMM - Microsoft System Center Reporting - page 235

The IntervalEndHealthState column in the State.vStateHourly view con-
tains the state that the object was in at the end of the monitoring interval. You can join this
column on the HealthStateRowId column of the vHealthState view to retrieve
the name of the health state.
The following query returns yesterday's hourly state aggregates, including the monitors,
managed entities, and end health states:
SELECT
M.MonitorDefaultName,
ME.FullName,
HS.HealthStateDefaultName,
S.DateTime,
S.InRedStateMilliseconds,
S.InYellowStateMilliseconds,
S.InDisabledStateMilliseconds,
S.InPlannedMaintenanceMilliseconds,
S.InUnplannedMaintenanceMilliseconds,
S.HealthServiceUnavailableMilliseconds
FROM
State.vStateHourly S
INNER JOIN vManagedEntityMonitor MEM ON
S.ManagedEntityMonitorRowId =
MEM.ManagedEntityMonitorRowId
INNER JOIN vManagedEntity ME ON
MEM.ManagedEntityRowId = ME.ManagedEntityRowId
INNER JOIN vMonitor M ON
MEM.MonitorRowId = M.MonitorRowId
INNER JOIN vHealthState HS ON
S.IntervalEndHealthState = HS.HealthStateRowId
WHERE
YEAR(S.DateTime) = YEAR(DATEADD(d, -1, GETDATE()))
AND MONTH(S.DateTime) = MONTH(DATEADD(d, -1, GETDATE()))
AND DAY(S.DateTime) = DAY(DATEADD(d, -1, GETDATE()))
State.vStateRaw
The State.vStateRaw view contains the raw state data without aggregates. It in-
cludes one row per state change for each managed entity. You can find the date/time that