| 437-991-3573 | Data Tier Services
Explore Tools
Experts in Microsoft SQL Server, Clusters, Always On, Migrations, ERP, Performance & Cloud

Get Number of Microsoft Systems Center (SCOM) Monitor's State changes
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)

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