Noisy monitors

Here's some good queries to identify noisy monitors. Got them from Kev. Run against Ops db.

Noisiest monitors in the last 7 days

SELECT DISTINCT TOP 50 count(sce.StateId) as StateChanges, 

  m.DisplayName as MonitorName, 

  m.Name as MonitorId, 

  mt.typename AS TargetClass 

FROM StateChangeEvent sce with (nolock) 

join state s with (nolock) on sce.StateId = s.StateId 

join monitorview m with (nolock) on s.MonitorId = m.Id 

join managedtype mt with (nolock) on m.TargetMonitoringClassId = mt.ManagedTypeId 

where m.IsUnitMonitor = 1 

  -- Scoped to within last 7 days 

AND sce.TimeGenerated > dateadd(dd,-7,getutcdate()) 

group by m.DisplayName, m.Name,mt.typename 

order by StateChanges desc

Noisiest monitor per object/computer in the last 7 days

select distinct top 50 count(sce.StateId) as NumStateChanges, 

bme.DisplayName AS ObjectName, 

bme.Path, 

m.DisplayName as MonitorDisplayName, 

m.Name as MonitorIdName, 

mt.typename AS TargetClass 

from StateChangeEvent sce with (nolock) 

join state s with (nolock) on sce.StateId = s.StateId 

join BaseManagedEntity bme with (nolock) on s.BasemanagedEntityId = bme.BasemanagedEntityId 

join MonitorView m with (nolock) on s.MonitorId = m.Id 

join managedtype mt with (nolock) on m.TargetMonitoringClassId = mt.ManagedTypeId 

where m.IsUnitMonitor = 1 

   -- Scoped to specific Monitor (remove the "--" below): 

   -- AND m.DisplayName like ('%HealthService%') 

   -- Scoped to specific Computer (remove the "--" below): 

   -- AND bme.Path like ('%sql%') 

   -- Scoped to within last 7 days 

AND sce.TimeGenerated > dateadd(dd,-7,getutcdate()) 

group by s.BasemanagedEntityId,bme.DisplayName,bme.Path,m.DisplayName,m.Name,mt.typename 

order by NumStateChanges desc