微软SCOM管理中最有用的SQL查询(三)

SCOM的管理包（Package）通常包含应用程序和服务的监视设置。 将管理包导入到管理组后，System Center - Operations Manager 会立即根据由管理包定义的默认配置和阈值开始监视对象。
每个管理包可能包含以下一项或所有部件：
监视器，定向代理以跟踪管理组件的不同部件的状态。
规则，定向代理以收集性能和发现数据，发送警报和事件等等。
任务，定义代理或控制台可以执行的活动。
知识，提供文字建议以帮助操作员诊断和解决问题。
视图，为监视和管理此组件提供自定义的用户界面。
报表，定义报告此管理组件相关信息的特定方式。
对象发现，识别要监视的对象。
运行方式配置文件，允许你在不同计算机上使用不同帐户运行不同的规则、任务、监视器或发现。
管理包是SCOM监控的核心，以下SQL查询可以帮助管理员对Package进行快速查询和统计信息。

SCOM管理包Rules：

要查找给定规则 ID 名称的通用规则名称：

SELECT DisplayName from RuleView where name = ‘Microsoft.SystemCenter.GenericNTPerfMapperModule.FailedExecution.Alert’ --Rules per MP: SELECT mp.MPName, COUNT() As RulesPerMP FROM Rules r INNER JOIN ManagementPack mp ON mp.ManagementPackID = r.ManagementPackID GROUP BY mp.MPName ORDER BY RulesPerMP DESC --Rules per MP by category: SELECT mp.MPName, r.RuleCategory, COUNT() As RulesPerMPPerCategory FROM Rules r INNER JOIN ManagementPack mp ON mp.ManagementPackID = r.ManagementPackID GROUP BY mp.MPName, r.RuleCategory ORDER BY RulesPerMPPerCategory DESC --To find all rules per MP with a given alert severity: declare @mpid as varchar(50) select @mpid= managementpackid from managementpack where mpName=‘Microsoft.SystemCenter.2007’ select rl.rulename,rl.ruleid,md.modulename from rules rl, module md where md.managementpackid = @mpid and rl.ruleid=md.parentid and moduleconfiguration like ‘%2%’ --Rules are stored in a table named Rules. This table has columns linking rules to classes and Management Packs. --To find all rules in a Management Pack use the following query and substitute in the required Management Pack name: SELECT * FROM Rules WHERE ManagementPackID = (SELECT ManagementPackID from ManagementPack WHERE MPName = ‘Microsoft.SystemCenter.2007’) --To find all rules targeted at a given class use the following query and substitute in the required class name: SELECT * FROM Rules WHERE TargetManagedEntityType = (SELECT ManagedTypeId FROM ManagedType WHERE TypeName = ‘Microsoft.Windows.Computer’) --Rules by Class targeted and enabled by default excluding perf collection and discovery rules SELECT mtv.Name AS ‘ClassName’, mtv.DisplayName, COUNT(*) AS ‘COUNT’ FROM RuleView rv JOIN ManagedTypeView mtv ON rv.TargetMonitoringClassId = mtv.Id WHERE mtv.LanguageCode = ‘ENU’ AND rv.LanguageCode = ‘ENU’ AND rv.Enabled NOT IN (0) AND rv.Category NOT IN (‘PerformanceCollection’,‘