T-SQL: Get Number of SCOM Monitor's State changes
SELECT DISTINCT TOP 50
count(sce.StateId) as NumberStateChanges,
m.DisplayName as MonitorName,
m.Name as MonitorID,
mt.typename AS TargetClass
FROM StateChangeEvent sce with (nolock)
Inner Join state s with (nolock) on sce.StateId = s.StateId
Inner Join monitorview m with (nolock) on s.MonitorId = m.Id
Inner Join managedtype mt with (nolock) on m.TargetMonitoringClassId = mt.ManagedTypeId
where m.IsUnitMonitor = 1
AND sce.TimeGenerated > dateadd(dd,-7,getutcdate()) -- Scoped to within last 7 days
group by m.DisplayName, m.Name, mt.typename
order by NumberStateChanges desc
|
|
|
|
|
|