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

从多个监控类目角度查询SCOM，监控生产环境。

根据生产环境产生的警报最多的对象，服务器，监控项目等，可以对生产环境进行优化，加入自动化脚本对警报的对象进行自动修复。

从事件角度进行查询统计。

每日产生的时间和日志的增量数据查询

SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeAdded, 102)) = 1)

THEN ‘All Days’ ELSE CONVERT(VARCHAR(20), TimeAdded, 102) END AS DayAdded,

COUNT(*) AS EventsPerDay

FROM EventAllView

GROUP BY CONVERT(VARCHAR(20), TimeAdded, 102) WITH ROLLUP

ORDER BY DayAdded DESC

按事件编号和事件源显示的最常见的事件：（这为我们提供了事件源名称，以帮助查看引发这些事件的是什么）

SELECT top 20 Number as EventID, COUNT(*) AS TotalEvents, Publishername as EventSource

FROM EventAllView eav with (nolock)

GROUP BY Number, Publishername

ORDER BY TotalEvents DESC

服务器产生的最多的日志详细信息

SELECT top 20 LoggingComputer as ComputerName, COUNT(*) AS TotalEvents

FROM EventallView with (NOLOCK)

GROUP BY LoggingComputer

ORDER BY TotalEvents DESC

性能角度对Scom数据进行统计分析：

每日性能数据新增总量:

SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeSampled, 102)) = 1)

THEN ‘All Days’

ELSE CONVERT(VARCHAR(20), TimeSampled, 102)

END AS DaySampled, COUNT(*) AS PerfInsertPerDay

FROM PerformanceDataAllView with (NOLOCK)

GROUP BY CONVERT(VARCHAR(20), TimeSampled, 102) WITH ROLLUP

ORDER BY DaySampled DESC

T按 perf 对象和计数器名称显示前 20 个性能数据：（这向我们展示了哪些计数器可能收集过度或具有重复的收集规则，并填充数据库，利于管理员对SCOM进行管理）

SELECT TOP 20 pcv.ObjectName, pcv.CounterName, COUNT (pcv.countername) AS Total

FROM performancedataallview AS pdv, performancecounterview AS pcv

WHERE (pdv.performancesourceinternalid = pcv.performancesourceinternalid)

GROUP BY pcv.objectname, pcv.countername

ORDER BY COUNT (pcv.countername) DESC

查看单个服务器的警报数据:

select Distinct Path, ObjectName, CounterName, InstanceName

from PerformanceDataAllView pdv with (NOLOCK)

inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid

inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId

where path = ‘sql2a.opsmgr.net’

order by objectname, countername, InstanceName

提取给单个服务器，对象，计数器和实例的所有 perf 数据

select Path, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled

from PerformanceDataAllView pdv with (NOLOCK)

inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid

inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId

where path = ‘sql2a.opsmgr.net’ AND

objectname = ‘LogicalDisk’ AND

countername = ‘Free Megabytes’

order by timesampled DESC

接下来的章节我会给大家介绍警报状态变更数据，管理包数据查询等。