Useful Operations Manager 2007 SQL queries
Operational Database Section:
Alerts Section:
Most Common Alerts in an Operational Database, by Alert Count (NEW and CLOSED only - modify if you have other resolution states or remove that line):
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
Most Common Alerts in an Operational Database, by Repeat Count (NEW and CLOSED only - modify if you have other resolution states or remove that line):
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:
All Events by count by day, with total for entire database: (this tells us how many events per day we are inserting - and helps us look for too many events, event storms, and the result after tuning rules that generate too many events)
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: (This helps us know which event ID's are the most common in the database)
SELECT top 50 Number, COUNT(*) AS TotalEvents
FROM EventView
GROUP BY Number
ORDER BY TotalEvents DESC
Most common events by event number and event publishername: (This gives us the event publisher name to help see what is raising these events)
SELECT top 50 Number, Publishername, COUNT(*) AS TotalEvents
FROM EventAllView
GROUP BY Number, Publishername
ORDER BY TotalEvents DESC
Most common events, grouped by identical event number, publishername, and event parameters: (This shows use completely redundant events with identical data - but might be different than the above queries... you need to see both data outputs to fully tune)
SELECT top 100 Number, Publishername, EventParameters, COUNT(*) AS TotalEvents
FROM EventAllView
GROUP BY Number, Publishername, EventParameters
ORDER BY TotalEvents DESC
Computers generating the most events: (This shows us which computers create the most event traffic and use the most database space)
SELECT top 50 LoggingComputer, COUNT(*) AS TotalEvents
FROM EventallView
GROUP BY LoggingComputer
ORDER BY TotalEvents DESC
Computers generating the most events, by event number: (This shows the noisiest computers, group by unique event numbers)
SELECT top 50 LoggingComputer, Number, COUNT(*) AS TotalEvents
FROM EventallView
GROUP BY LoggingComputer, Number
ORDER BY TotalEvents DESC
Computers generating the most events, grouped by identical event number and publishername:
SELECT top 50 LoggingComputer, PublisherName, Number, COUNT(*) AS TotalEvents
FROM EventallView
GROUP BY LoggingComputer, PublisherName, Number
ORDER BY TotalEvents 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 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
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 object and counter name: (This is the most interesting - tells us specifically which perf insertions are the noisiest)
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
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'))
To view all performance insertions for a given computer:
select path, objectname, countername, instancename, samplevalue, timesampled from PerformanceDataAllView pdv
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where path = 'dc1.opsmgr.net'
order by countername
To refine a the above query to pull all perf data for a given computer, object, counter, and instance:
select path, objectname, countername, instancename, samplevalue, timesampled from PerformanceDataAllView pdv
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where path = 'dc1.opsmgr.net' AND
objectname = 'Process' AND
countername = '% Processor Time' AND
instancename = 'HealthService'
order by timesampled
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.Exchange.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 '%<AlertLevel>50</AlertLevel>%'
To find all rules per MP with a given alert severity:
declare @mpid as varchar(50)
select @mpid= managementpackid from managementpack where
mpName='Microsoft.Exchange.Server.2003.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 '%<Severity>2</Severity>%'
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
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 <MonitorName> 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 = =<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'))
Agent Info:
To find all managed computers that are currently down and not pingable:
SELECT bme.DisplayName,s.LastModified as LastModifiedUTC, dateadd(hh,-5,s.LastModified) as 'LastModifiedCST (GMT-5)'
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' AND bme.IsDeleted = '0'
ORDER BY s.Lastmodified DESC
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
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
To find a computer name from a HealthServiceID (guid from the Agent proxy alerts)
select DisplayName, Path, basemanagedentityid from basemanagedentity where basemanagedentityid = 'guid'
To view the agent patch list (all hotfixes applied to all agents)
select bme.path AS 'Agent Name', hs.patchlist AS 'Patch List' from MT_HealthService hs
inner join BaseManagedEntity bme on hs.BaseManagedEntityId = bme.BaseManagedEntityId
order by path
To view all agents missing a specific hotfix (change the KB number below to the one you are looking for):
select bme.path AS 'Agent Name', hs.patchlist AS 'Patch List' from MT_HealthService hs
inner join BaseManagedEntity bme on hs.BaseManagedEntityId = bme.BaseManagedEntityId
where hs.patchlist not like '%951380%'
order by path
Misc:
To view grooming info:
SELECT * FROM PartitionAndGroomingSettings WITH (NOLOCK)
Information on existing User Roles:
SELECT UserRoleName, IsSystem from userrole
Operational DB version:
select DBVersion from __MOMManagementGroupInfo__
Data Warehouse:
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)
To view the number of days of total data of each type in the DW:
SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current] FROM Event.vEvent
SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current] FROM Perf.vPerfRaw
SELECT DATEDIFF(d, MIN(DWCreatedDateTime), GETDATE()) AS [Current] FROM Alert.vAlert
To view the oldest and newest recod timestamps of each data type in the DW:
select min(DateTime) from Event.vEvent
select max(DateTime) from Event.vEvent
select min(DateTime) from Perf.vPerfRaw
select max(DateTime) from Perf.vPerfRaw
select min(DWCreatedDateTime) from Alert.vAlert
select max(DWCreatedDateTime) from Alert.vAlert
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'
To get all raw alert data from the data warehouse to build reports from:
select * from Alert.vAlertResolutionState ars
inner join Alert.vAlertDetail adt on ars.alertguid = adt.alertguid
inner join Alert.vAlert alt on ars.alertguid = alt.alertguid
To view data on all alerts modified by a specific user:
select ars.alertguid, alertname, alertdescription, statesetbyuserid, resolutionstate, statesetdatetime, severity, priority, managedentityrowID, repeatcount
from Alert.vAlertResolutionState ars
inner join Alert.vAlert alt on ars.alertguid = alt.alertguid
where statesetbyuserid like '%username%'
order by statesetdatetime
To view a count of all alerts closed by all users:
select statesetbyuserid, count(*) as 'Number of Alerts'
from Alert.vAlertResolutionState ars
where resolutionstate = '255'
group by statesetbyuserid
order by 'Number of Alerts' DESC
Misc 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
How to identify your version of OpsMgr 2007:
RTM: 6.0.5000.0
SP1-RC: 6.0.6246.0
SP1: 6.0.6278.0
To get better performance manually:
Update Statistics (will help speed up reports and takes less time than a full reindex):
EXEC sp_updatestats
Show index fragmentation (to determine how badly you need a reindex – logical scan frag > 10% = bad. Scan density below 80 = bad):
DBCC SHOWCONTIG
DBCC SHOWCONTIG WITH FAST (less data than above – in case you don’t have time)
Reindex the database:
USE OperationsManager
go
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
EXEC SP_MSForEachTable "Print 'Reindexing '+'?' DBCC DBREINDEX ('?')"
Table by table:
DBCC DBREINDEX (‘TableName’)
Query to view the index job history on domain tables in the databases:
select *
from DomainTable dt
inner join DomainTableIndexOptimizationHistory dti
on dt.domaintablerowID = dti.domaintableindexrowID
ORDER BY optimizationdurationseconds DESC
Query to view the update statistics job history on domain tables in the databases:
select *
from DomainTable dt
inner join DomainTableStatisticsUpdateHistory dti
on dt.domaintablerowID = dti.domaintablerowID
ORDER BY UpdateDurationSeconds DESC
Data Warehouse query to examine the index and statistics history - run the following query for the Alert, Event, Perf, and State tables (these are non-domain tables):
select basetablename, optimizationstartdatetime, optimizationdurationseconds,
beforeavgfragmentationinpercent, afteravgfragmentationinpercent,
optimizationmethod, onlinerebuildlastperformeddatetime
from StandardDatasetOptimizationHistory sdoh
inner join StandardDatasetAggregationStorageIndex sdasi
on sdoh.StandardDatasetAggregationStorageIndexRowId = sdasi.StandardDatasetAggregationStorageIndexRowId
inner join StandardDatasetAggregationStorage sdas
on sdasi.StandardDatasetAggregationStorageRowId = sdas.StandardDatasetAggregationStorageRowId
ORDER BY optimizationdurationseconds DESC
