SCOM2012 Scripts to Investigate Environment

After running SCOM2012 it’s definately to hold grip on your environment. You monitor your servers with a reason, so you’ll have to look on a weekly base what alarms and events are being created and keep those counts low. Hereby a few SQL scripts helping you find useful information in the OpsMgrDB.

All Events by count by day, with total for entire OpsMgrDB

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

Large table (find out what events create noise in OpsMgrDB)

SELECT TOP 1000 
a2.name AS [tablename], (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved, 
a1.rows as row_count, a1.data * 8 AS data, 
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size, 
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused, 
(row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1, 
a3.name AS [schemaname] 
FROM (SELECT ps.object_id, SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows], 
SUM (ps.reserved_page_count) AS reserved, 
SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) 
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, 
SUM (ps.used_page_count) AS used 
FROM sys.dm_db_partition_stats ps 
GROUP BY ps.object_id) AS a1 
LEFT OUTER JOIN (SELECT it.parent_id, 
SUM(ps.reserved_page_count) AS reserved, 
SUM(ps.used_page_count) AS used 
FROM sys.dm_db_partition_stats ps 
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) 
WHERE it.internal_type IN (202,204) 
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) 
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) 
WHERE a2.type <> N'S' and a2.type <> N'IT'  

Top-20 alerts in OpsMgrDB (repeat count)

SELECT TOP 20 SUM(RepeatCount+1) AS RepeatCount, AlertStringName, AlertStringDescription, MonitoringRuleId, Name 
FROM Alertview WITH (NOLOCK) 
WHERE Timeraised is not NULL 
GROUP BY AlertStringName, AlertStringDescription, MonitoringRuleId, Name 
ORDER BY RepeatCount DESC

Database size and used space

USE OperationsManager 
select a.FILEID, 
[FILE_SIZE_MB]=convert(decimal(12,2),round(a.size/128.000,2)), 
[SPACE_USED_MB]=convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)), 
[FREE_SPACE_MB]=convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) , 
[GROWTH_MB]=convert(decimal(12,2),round(a.growth/128.000,2)), 
NAME=left(a.NAME,15), 
FILENAME=left(a.FILENAME,60) 
from dbo.sysfiles a

Comments