| 437-991-3573 | Data Engineering Services
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance

Get Number of Microsoft Systems Center (SCOM) Monitor's State changes
by BF (Principal Consultant; Architecture; Engineering)

T-SQL: Get Number of SCOM Monitor's State changes

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