info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
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)
2017-08-17








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