SQL queries for Operations Database

NOTE:-This post covers SQL queries for the Operations Manager Database not the Datawarehouse.

A good and quick way to get information about your SCOM environment is to run SQL queries against the databases. Connect to SQL Server Management Studio on your Opsdb server and select the OperationsManager database, now you can execute the following queries (cut and paste them):-

To view grooming info:

SELECT * FROM PartitionAndGroomingSettings WITH (NOLOCK)

or

USE OperationsManager
SELECT ObjectName, DaysToKeep AS Days
FROM PartitionAndGroomingSettings
ORDER BY ObjectName

Information on existing User Roles:

SELECT UserRoleName, IsSystem from userrole

Operational DB version:

select DBVersion from __MOMManagementGroupInfo__

To view all Run-As Profiles, their associated Run-As account, and associated agent name:

select srv.displayname as ‘RunAs Profile Name’,    srv.description as ‘RunAs Profile Description’,     cmss.name as ‘RunAs Account Name’,     cmss.description as ‘RunAs Account Description’,     cmss.username as ‘RunAs Account Username’,     cmss.domain as ‘RunAs Account Domain’,     mp.FriendlyName as ‘RunAs Profile MP’,     bme.displayname as ‘HealthService’     from dbo.SecureStorageSecureReference sssr     inner join SecureReferenceView srv on srv.id = sssr.securereferenceID     inner join CredentialManagerSecureStorage cmss on cmss.securestorageelementID = sssr.securestorageelementID     inner join managementpackview mp on srv.ManagementPackId = mp.Id     inner join BaseManagedEntity bme on bme.basemanagedentityID = sssr.healthserviceid     order by srv.displayname

Number of console Alerts per Day:

SELECT CONVERT(VARCHAR(20), TimeAdded, 102) AS DayAdded, COUNT(*) AS NumAlertsPerDay    FROM Alert WITH (NOLOCK)     WHERE TimeRaised is not NULL     GROUP BY CONVERT(VARCHAR(20), TimeAdded, 102)     ORDER BY DayAdded DESC

Top 20 Alerts in an Operational Database, by 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

Most Common Events by event number:  (This helps us know which event ID’s are the most common in the database)

SELECT top 20 Number as EventID, COUNT(*) AS TotalEvents    FROM EventView with (NOLOCK)     GROUP BY Number     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 20 LoggingComputer as ComputerName, COUNT(*) AS TotalEvents    FROM EventallView with (NOLOCK)     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 20 LoggingComputer as ComputerName, COUNT(*) AS TotalEvents, Number as EventID    FROM EventallView with (NOLOCK)     GROUP BY LoggingComputer, Number     ORDER BY TotalEvents DESC

Noisiest monitors changing state in the database in the last 7 days:

select distinct top 50 count(sce.StateId) as NumStateChanges,
m.DisplayName as MonitorDisplayName,
m.Name as MonitorIdName,
mt.typename AS TargetClass
from StateChangeEvent sce with (nolock)
join state s with (nolock) on sce.StateId = s.StateId
join monitorview m with (nolock) on s.MonitorId = m.Id
join managedtype mt with (nolock) on m.TargetMonitoringClassId = mt.ManagedTypeId
where m.IsUnitMonitor = 1
— Scoped to within last 7 days
AND sce.TimeGenerated > dateadd(dd,-7,getutcdate())
group by m.DisplayName, m.Name,mt.typename
order by NumStateChanges desc

Noisiest Monitor in the database – PER Object/Computer in the last 7 days:

select distinct top 50 count(sce.StateId) as NumStateChanges,
bme.DisplayName AS ObjectName,
bme.Path,
m.DisplayName as MonitorDisplayName,
m.Name as MonitorIdName,
mt.typename AS TargetClass
from StateChangeEvent sce with (nolock)
join state s with (nolock) on sce.StateId = s.StateId
join BaseManagedEntity bme with (nolock) on s.BasemanagedEntityId = bme.BasemanagedEntityId
join MonitorView m with (nolock) on s.MonitorId = m.Id
join managedtype mt with (nolock) on m.TargetMonitoringClassId = mt.ManagedTypeId
where m.IsUnitMonitor = 1
— Scoped to specific Monitor (remove the “–” below):
— AND m.MonitorName like (‘%HealthService%’)
— Scoped to specific Computer (remove the “–” below):
— AND bme.Path like (‘%sql%’)
— Scoped to within last 7 days
AND sce.TimeGenerated > dateadd(dd,-7,getutcdate())
group by s.BasemanagedEntityId,bme.DisplayName,bme.Path,m.DisplayName,m.Name,mt.typename
order by NumStateChanges desc

To find all installed Management Packs and their version:

SELECT MPName, MPFriendlyName, MPVersion, MPIsSealed
FROM ManagementPack WITH(NOLOCK)
ORDER BY MPName

Top 20 Alerts in an Operational Database, by 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

Advertisements
This entry was posted in SQL Queries and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s