Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



Get Number of Microsoft Systems Center (SCOM) Monitor's State changes
by BFarrell (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
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