Data Warehouse Queries for Noisy Rules

Here are a few SQL queries that you can run on the Operations Manager Data warehouse to determine what are the Noisy Event and Performance rules in your enviroment.

Noisy Event Rules

select count(*) as cnt, RuleDefaultName
from Event.vEvent as ev
Left Join Event.vEventRule as evi
On ev.eventOriginID = evi.eventOriginID
Left Join vRule as r
On r.RuleRowId = evi.RuleRowId
group by RuleDefaultName
order by cnt desc

Noisy Performance Rules

select count(*) as cnt, RuleDefaultName
from Perf.vPerfRaw as pr
Left Join vPerformanceRuleInstance as pri
On pr.PerformanceRuleInstanceRowId = pri.PerformanceRuleInstanceRowId
Left Join vRule as r
On r.RuleRowId = pri.RuleRowId
group by RuleDefaultName
order by cnt desc


As expected, in a few enviroments I tested, some of the busiest performance rules were the Processor and Memory collection rules.

However there were several unexpectedly busy Event rules that accounted for a significant portion of all the event data and some of these were subsequently disabled.

Thanks to Reut for pointing me in the right direction for the initial query.

Data Warehouse Query

If you have multiple management groups rolling up to a single data warehouse here is a query that will allow you to return a computer name and its associated management group. Not great or anything but the first attempt at digging in to the db.

SELECT ManagedEntity.DisplayName, ManagementGroup.ManagementGroupDefaultName
from OperationsManagerDW.dbo.ManagedEntity
INNER JOIN OperationsManagerDW.dbo.ManagementGroup ON
ManagedEntity.ManagementGroupRowId
= ManagementGroup.ManagementGroupRowId
WHERE fullname like 'Microsoft.Windows.Computer:%'