Useful Operations Manager 2007 SQL queries - prettified and PoShified
Posted by Jeremy on May 29th, 2008
Kevin Holman, who writes a lot about SCOM2007, posted an article some time ago with a bunch of extremely useful SQL queries to use with SCOM2007. Over several installments, I’ll take that file and show you some useful SCOM PoSh tools you can use to - including a set of cmdlets I wrote to help the process out.
First, let’s take the actual file itself. I’ve taken it and cleaned it up some, changing formats and fixing errors I found, and made it into an actual .SQL file you can use inside of SQL Studio.
Download the file, and save it somewhere useful. SQL Studio 2005 likes to save query files in My Documents\SQL Server Management Studio\Projects, and it’s the first place it goes when you select open, so that makes a great choice.
If you’re not used to SQL server, you might want to know how to use this file. When you open up the file, you’ll notice a ton of different queries - surely you don’t want to run them all at once1 - and you don’t have to. To run an individual query, simply highlight it and either hit F5, or click on the execute button.
That’s all there is to it. If you wanted to take it a step further, you could hardcode the database name and user before the table name (So instead of “from ManagedEntityGenericView” you’d alter it to (In my case, yours may be different) “from OperationsManager.dbo.ManagedEntityGenericView”. That way, you don’t even have to worry about switching which database to use before running it.
Next up, how to make these more useful to you on an every day basis. And if you’re not one to blindly download things, read on for the pasted SQL file.
-
–======================================================================
-
–
-
– SQL Source File — Created with SAPIEN Technologies PrimalScript 4.1
-
–
-
– NAME: Useful_SCOM2007_SQL_Queries.sql
-
–
-
– AUTHOR: Jeremy D. Pavleck , JPavleck@GMail.com
-
– DATE : 4/17/2008
-
–
-
– COMMENT: Some useful SQL queries you can use in SCOM2007.
-
– Taken verbatim from Kevin Holman’s OpsMgr Blog, and re-written to be SQL friendly.
-
–
-
– URL: http://blogs.technet.com/kevinholman/archive/2007/10/18/useful-operations-manager-2007-sql-queries.aspx
-
–
-
–======================================================================
-
-
– Find all managed servers that are down and not pingable
-
SELECT bme.DisplayName, s.LastModified
-
FROM state AS s, BaseManagedEntity AS bme
-
WHERE s.basemanagedentityid = bme.basemanagedentityid AND s.monitorid
-
IN (SELECT MonitorId FROM Monitor WHERE MonitorName = ‘Microsoft.SystemCenter.HealthService.ComputerDown’)
-
AND s.Healthstate = ‘3′
-
ORDER BY s.Lastmodified DESC
-
-
– Operational Database Version
-
SELECT DBVersion FROM __MOMManagementGroupInfo__
-
-
– Find a computer name from it’s Health Service ID (guid from agent proxy alerts)
-
SELECT id, path, fullname, displayname FROM ManagedEntityGenericView WHERE ID = ”
-
-
– ALERTS SECTION –
-
——————–
-
-
– Most common alerts, by alert count
-
SELECT AlertStringName, AlertStringDescription, AlertParams, Name, SUM(1) AS AlertCount, SUM(RepeatCount+1) AS AlertCountWithRepeatCount
-
FROM Alertview WITH (NOLOCK)
-
WHERE ResolutionState = (0|255)
-
GROUP BY AlertStringName, AlertStringDescription, AlertParams, Name
-
ORDER BY AlertCount DESC
-
-
– TOP 10 common alerts
-
SELECT Top(10) AlertStringName, AlertStringDescription, AlertParams, Name, SUM(1) AS AlertCount, SUM(RepeatCount+1) AS AlertCountWithRepeatCount
-
FROM Alertview WITH (NOLOCK)
-
WHERE ResolutionState = (0|255)
-
GROUP BY AlertStringName, AlertStringDescription, AlertParams, Name
-
ORDER BY AlertCount DESC
-
-
– Most common alerts, by repeat count
-
SELECT AlertStringName, AlertStringDescription, AlertParams, Name, SUM(1) AS AlertCount, SUM(RepeatCount+1) AS AlertCountWithRepeatCount
-
FROM Alertview WITH (NOLOCK)
-
WHERE ResolutionState = (0|255)
-
GROUP BY AlertStringName, AlertStringDescription, AlertParams, Name
-
ORDER BY AlertCountWithRepeatCount DESC
-
-
– Number of alerts per day
-
SELECT CONVERT(VARCHAR(20), TimeAdded, 101) AS DayAdded, COUNT(*) AS NumAlertsPerDay
-
FROM Alert WITH (NOLOCK)
-
GROUP BY CONVERT(VARCHAR(20), TimeAdded, 101)
-
ORDER BY DayAdded DESC
-
-
– Number of alerts per day by resolution state
-
SELECT
-
CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeAdded, 101)) = 1) THEN ‘All Days’ ELSE CONVERT(VARCHAR(20), TimeAdded, 101) END AS [Date],
-
CASE WHEN(GROUPING(ResolutionState) = 1) THEN ‘All Resolution States’ ELSE CAST(ResolutionState AS VARCHAR(5)) END AS [ResolutionState],
-
COUNT(*) AS NumAlerts
-
FROM Alert WITH (NOLOCK)
-
GROUP BY CONVERT(VARCHAR(20), TimeAdded, 101), ResolutionState WITH ROLLUP
-
ORDER BY DATE DESC
-
-
– EVENTS SECTION –
-
——————–
-
-
– Most common events by day by count
-
SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeAdded, 101)) = 1)
-
THEN ‘All Days’
-
ELSE CONVERT(VARCHAR(20), TimeAdded, 101) END AS DayAdded,
-
COUNT(*) AS NumEventsPerDay
-
FROM EventAllView
-
GROUP BY CONVERT(VARCHAR(20), TimeAdded, 101) WITH ROLLUP
-
ORDER BY DayAdded DESC
-
-
– Most common events by event number
-
SELECT Number, COUNT(*) AS "Number of Events"
-
FROM EventView
-
GROUP BY Number
-
ORDER BY "Number of Events" DESC
-
-
– PERFORMANCE SECTION –
-
————————-
-
-
– Performance Insertions per day
-
SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeSampled, 101)) = 1)
-
THEN ‘All Days’ ELSE CONVERT(VARCHAR(20), TimeSampled, 101)
-
END AS DaySampled, COUNT(*) AS NumPerfPerDay
-
FROM PerformanceDataAllView
-
GROUP BY CONVERT(VARCHAR(20), TimeSampled, 101) WITH ROLLUP
-
ORDER BY DaySampled DESC
-
-
– Most common performance insertions by perf object and counter name:
-
SELECT 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
-
-
– Most common performance insertions by perf object name:
-
SELECT pcv.objectname, count (pcv.countername) AS total FROM performancedataallview AS pdv, performancecounterview AS pcv
-
WHERE (pdv.performancesourceinternalid = pcv.performancesourceinternalid)
-
GROUP BY pcv.objectname
-
ORDER BY count (pcv.countername) DESC
-
-
– Most common performance insertions by perf counter name:
-
SELECT pcv.countername, count (pcv.countername) AS total FROM performancedataallview AS pdv, performancecounterview AS pcv
-
WHERE (pdv.performancesourceinternalid = pcv.performancesourceinternalid)
-
GROUP BY pcv.countername
-
ORDER BY count (pcv.countername) DESC
-
-
– STATE SECTION –
-
——————-
-
-
– State changes per day:
-
SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeGenerated, 101)) = 1)
-
THEN ‘All Days’ ELSE CONVERT(VARCHAR(20), TimeGenerated, 101)
-
END AS DayGenerated, COUNT(*) AS NumEventsPerDay
-
FROM StateChangeEvent WITH (NOLOCK)
-
GROUP BY CONVERT(VARCHAR(20), TimeGenerated, 101) WITH ROLLUP
-
ORDER BY DayGenerated DESC
-
-
– MANAGEMENT PACK INFO –
-
————————–
-
-
– To find all installed Management Packs and their version:
-
SELECT MPName, MPFriendlyName, MPVersion, MPIsSealed
-
FROM ManagementPack WITH(NOLOCK)
-
ORDER BY MPName
-
-
– 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
-
-
– Monitors Per MP:
-
SELECT mp.MPName, COUNT(*) AS MonitorsPerMPPerCategory
-
FROM Monitor m
-
INNER JOIN ManagementPack mp ON mp.ManagementPackID = m.ManagementPackID
-
GROUP BY mp.MPName
-
ORDER BY COUNT(*) DESC
-
-
– To find your Monitor by common name:
-
SELECT * FROM Monitor
-
INNER JOIN LocalizedText LT ON LT.ElementName = Monitor.MonitorName
-
WHERE LTValue = ‘My Monitor Name’
-
-
– To find all Rules per MP that generate an alert:
-
declare @mpid AS varchar(50)
-
SELECT @mpid= managementpackid FROM managementpack WHERE
-
mpName=‘Microsoft.BizTalk.Server.2006.Monitoring’
-
SELECT rl.rulename,rl.ruleid,md.modulename FROM rules rl, module md
-
WHERE md.managementpackid = @mpid
-
AND rl.ruleid=md.parentid
-
AND moduleconfiguration LIKE ‘%50%’
-
-
– To find all rules per MP with a given alert severity:
-
declare @mpid AS varchar(50)
-
SELECT @mpid= managementpackid FROM managementpack WHERE
-
mpName=‘Microsoft.BizTalk.Server.2006.Monitoring’
-
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%’
-
-
– Number of instances of a type: (Number of disks, computers, databases, etc that OpsMgr has discovered)
-
SELECT mt.ManagedTypeID, mt.TypeName, COUNT(*) AS NumEntitiesByType
-
FROM BaseManagedEntity bme WITH(NOLOCK)
-
LEFT JOIN ManagedType mt WITH(NOLOCK) ON mt.ManagedTypeID = bme.BaseManagedTypeID
-
WHERE bme.IsDeleted = 0
-
GROUP BY mt.ManagedTypeID, mt.TypeName
-
ORDER BY COUNT(*) DESC
-
-
– Number of Views per Management Pack:
-
SELECT mp.MPName, v.ViewVisible, COUNT(*) AS ViewsPerMP
-
FROM [Views] v
-
INNER JOIN ManagementPack mp ON mp.ManagementPackID = v.ManagementPackID
-
GROUP BY mp.MPName, v.ViewVisible
-
ORDER BY v.ViewVisible DESC, COUNT(*) DESC
-
-
– Grooming:
-
SELECT * FROM PartitionAndGroomingSettings WITH (NOLOCK)
-
-
– All managed computers count:
-
SELECT COUNT(*) AS NumManagedComps FROM (
-
SELECT bme2.BaseManagedEntityID
-
FROM BaseManagedEntity bme WITH (NOLOCK)
-
INNER JOIN BaseManagedEntity bme2 WITH (NOLOCK) ON bme2.BaseManagedEntityID = bme.TopLevelHostEntityID
-
WHERE bme2.IsDeleted = 0
-
AND bme2.IsDeleted = 0
-
AND bme2.BaseManagedTypeID = (SELECT TOP 1 ManagedTypeID FROM ManagedType WHERE TypeName = ‘microsoft.windows.computer’)
-
GROUP BY bme2.BaseManagedEntityID
-
) AS Comps
-
-
– Classes available in the DB:
-
SELECT * FROM ManagedType
-
-
– Classes available in the DB for Microsoft Windows type:
-
SELECT * FROM ManagedType
-
WHERE TypeName LIKE ‘Microsoft.Windows.%’
-
-
– Every property of every class:
-
SELECT * FROM MT_Computer
-
-
– All instances of all types once discovered
-
SELECT * FROM BaseManagedEntity
-
-
– To get the state of every instance of a particular monitor the following query can be run, (replace with the name of the monitor):
-
SELECT bme.FullName, bme.DisplayName, s.HealthState FROM state AS s, BaseManagedEntity AS bme
-
WHERE s.basemanagedentityid = bme.basemanagedentityid
-
AND s.monitorid IN (SELECT MonitorId FROM Monitor WHERE MonitorName = ”)
-
-
– For example, this gets the state of the Microsoft.SQLServer.2005.DBEngine.ServiceMonitor for each instance of the SQL 2005 Database Engine class.
-
SELECT bme.FullName, bme.DisplayName, s.HealthState
-
FROM state AS s, BaseManagedEntity AS bme
-
WHERE s.basemanagedentityid = bme.basemanagedentityid
-
AND s.monitorid IN (SELECT MonitorId FROM Monitor WHERE MonitorName = ‘Microsoft.SQLServer.2005.DBEngine.ServiceMonitor’)
-
-
– To find the overall state of any object in OpsMgr the following query should be used to return the state of the System.EntityState monitor:
-
SELECT bme.FullName, bme.DisplayName, s.HealthState
-
FROM state AS s, mt_managedcomputer AS mt, BaseManagedEntity AS bme
-
WHERE s.basemanagedentityid = bme.basemanagedentityid
-
AND s.monitorid IN (SELECT MonitorId FROM Monitor WHERE MonitorName = ‘System.Health.EntityState’)
-
-
– 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.Windows.Server.2003′)
-
-
– 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’)
-
-
– The Alert table contains all alerts currently open in OpsMgr. This includes resolved alerts until they are groomed out of the database. To get all alerts across all instances of a given monitor use the following query and substitute in the required monitor name:
-
-
SELECT * FROM Alert WHERE ProblemID IN (SELECT MonitorId FROM Monitor WHERE MonitorName = ‘Microsoft.SQLServer.2005.DBEngine.ServiceMonitor’)
-
-
– To retrieve all alerts for all instances of a specific class use the following query and substitute in the required table name, in this example MT_DBEngine is used to look for SQL alerts:
-
SELECT * FROM Alert WHERE BaseManagedEntityID IN (SELECT BaseManagedEntityID FROM MT_DBEngine)
-
-
– To determine which table is currently being written to for event and performance data use the following query:
-
SELECT * FROM PartitionTables WHERE IsCurrent = 1
-
-
– To retrieve events generated by a specific rule use the following query and substitute in the required rule ID:
-
SELECT * FROM Event_00 WHERE RuleId = (SELECT RuleId FROM Rules WHERE RuleName = ‘Microsoft.Windows.Server.2003.OperatingSystem.CleanShutdown.Collection ‘)
-
-
– To retrieve all events generated by rules in a specific Management Pack the following query can be used where the Management Pack name is substituted with the required value:
-
SELECT * FROM EventAllView
-
WHERE RuleID IN (SELECT RuleId FROM Rules WHERE ManagementPackId =
-
(SELECT ManagementPackId FROM ManagementPack WHERE MPName = ‘Microsoft.Windows.Server.2003′))
-
-
– To retrieve all performance data for a given rule in a readable format use the following query:
-
SELECT pc.ObjectName, pc.CounterName, ps.PerfmonInstanceName, pd.SampleValue, pd.TimeSampled
-
FROM PerformanceDataAllView AS pd, PerformanceCounter AS pc, PerformanceSource AS ps
-
WHERE pd.PerformanceSourceInternalId IN (SELECT PerformanceSourceInternalId FROM PerformanceSource
-
WHERE RuleId = (SELECT RuleId FROM Rules WHERE RuleName =‘ Microsoft.Windows.Server.2003.LogicalDisk.FreeSpace.Collection’))
-
-
– Information on existing User Roles:
-
SELECT UserRoleName, IsSystem FROM userrole
-
-
– Grooming in the DataWarehouse:
-
– Grooming no longer uses SQL agent jobs. Grooming is handled by scheduled stored procedures, that run much more frequently, which provides less impact than in the previous version.
-
– Default grooming for the DW for each dataset, to examine Data Warehouse grooming settings:
-
SELECT AggregationIntervalDurationMinutes, BuildAggregationStoredProcedureName, GroomStoredProcedureName, MaxDataAgeDays, GroomingIntervalMinutes FROM StandardDatasetAggregation
-
-
– The first row is the interval in minutes.
-
– NULL is raw data, 60 is hourly, and 1440 is daily.
-
– The second and third row shows what data it is
-
– MaxDataAgeDays has the retention period in days - this is the field to update if the administrator wants to lower the days of retention.
-
– RAW alert – 400 days
-
– RAW event – 100 days
-
– RAW perf – 10 days (hourly and daily perf = 400 days)
-
– RAW state – 180 days (hourly and daily state = 400 days)
-
-
– AEM Queries (Data Warehouse):
-
-
– Default query to return all RAW AEM data:
-
SELECT * FROM [CM].[vCMAemRaw] Rw
-
INNER JOIN dbo.AemComputer Computer ON Computer.AemComputerRowID = Rw.AemComputerRowID
-
INNER JOIN dbo.AemUser Usr ON Usr.AemUserRowId = Rw.AemUserRowId
-
INNER JOIN dbo.AemErrorGroup EGrp ON Egrp.ErrorGroupRowId = Rw.ErrorGroupRowId
-
INNER JOIN dbo.AemApplication App ON App.ApplicationRowId = Egrp.ApplicationRowId
-
-
– Count the raw crashes per day:
-
SELECT CONVERT(char(10), DateTime, 101) AS "Crash Date (by Day)", COUNT(*) AS "Number of Crashes"
-
FROM [CM].[vCMAemRaw]
-
GROUP BY CONVERT(char(10), DateTime, 101)
-
ORDER BY "Crash Date (by Day)" DESC
-
-
– Count the total number of raw crashes in the DW database:
-
SELECT count(*) FROM CM.vCMAemRaw
-
-
– Default grooming for the DW for the AEM dataset: (Aggregated data kept for 400 days, RAW 30 days by default)
-
SELECT AggregationTypeID, BuildAggregationStoredProcedureName, GroomStoredProcedureName, MaxDataAgeDays, GroomingIntervalMinutes
-
FROM StandardDatasetAggregation WHERE BuildAggregationStoredProcedureName = ‘AemAggregate’
-
-
– MISCELLANEOUS SECTION –
-
—————————
-
-
– Simple query to display large tables, to determine what is taking up space in the database:
-
SELECT so.name,
-
8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END) AS data_kb,
-
Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) AS index_kb,
-
Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0) AS blob_kb
-
FROM dbo.sysobjects AS so JOIN dbo.sysindexes AS si ON (si.id = so.id)
-
WHERE ‘U’ = so.type GROUP BY so.name ORDER BY data_kb DESC
-
-
– Is SQL broker enabled?
-
SELECT is_broker_enabled FROM sys.DATABASES WHERE name = ‘OperationsManager’
-
-
– How to identify your version of SQL server:
-
SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)
-
-
– SQL 2005:
-
– SQL Server 2005 RTM 2005.90.1399
-
– SQL Server 2005 SP1 2005.90.2047
-
– SQL Server 2005 SP1 plus 918222 2005.90.2153
-
– SQL Server 2005 SP2 2005.90.3042
-
- And don’t call me Shirley [↩]
June 10th, 2008 at 2:41 pm
[...] essentially mirroring this from the original one on my blog, but since I’ve decided to transition to a tech blog and a non-tech blog, it seems [...]