Got these queries from the
Microsoft TechNet OpsMgr Forum in this
thread.
Be careful when running these queries though. When there are high volumes of Alerts these SQL queries must be run during non-business hours since they can consume a lot of IO and will take some time to complete.
Getting statistics for Rule generated Alerts:
| Use OperationsManager
select
rulename as RuleName,
MT1.typename as TargetTypeBase,
MT2.typename as TargetType,
mpname as MPName,
count(*) as RuleAlertNumber,
sum(repeatcount + 1) as RuleAlertTotalNumber
from alert with(nolock)
inner join basemanagedentity with(nolock)
on alert.basemanagedentityid = basemanagedentity.basemanagedentityid
inner join managedtype as MT1 with(nolock)
on basemanagedentity.basemanagedtypeid = MT1.ManagedTypeId
inner join rules with(nolock)
on alert.ruleid = rules.ruleid
inner join managedtype as MT2 with(nolock)
on rules.targetmanagedentitytype = MT2.managedtypeid
inner join managementpack with(nolock)
on rules.managementpackid = managementpack.managementpackid
group by rules.ruleid, rulename, MT1.typename, MT2.typename, mpname
order by count(*) desc |
Getting statistics for Monitor generated Alerts:
| Use OperationsManager
select
monitorname as MonitorName,
MT1.typename as TargetTypeBase,
MT2.typename as TargetType,
mpname as MPName,
count(*) as MonitorAlertNumber,
sum(repeatcount + 1) as MonitorAlertTotalNumber
from alert with(nolock)
inner join basemanagedentity with(nolock)
on alert.basemanagedentityid = basemanagedentity.basemanagedentityid
inner join managedtype as MT1 with(nolock)
on basemanagedentity.basemanagedtypeid = MT1.ManagedTypeId
inner join monitor with(nolock)
on alert.ruleid = monitor.monitorid
inner join managedtype as MT2 with(nolock)
on monitor.targetmanagedentitytype = MT2.managedtypeid
inner join managementpack with(nolock)
on monitor.managementpackid = managementpack.managementpackid
group by monitor.monitorid, monitorname, MT1.typename, MT2.typename, mpname
order by count(*) desc |
No comments:
Post a Comment