Useful SQL queries in SCOM database

This is the part two of a set of articles related to SCOM

Part1 - SCOM BASICS

Which are the differences between Operations and Datawarehouse databases.

Extracted from Microsoft web:

• The operational database is a SQL Server database that contains all configuration data for the management group and stores all monitoring data that is collected and processed for the management group. The operational database retains short-term data, by default 7 days.

• The data warehouse database is a SQL Server database that stores monitoring and alerting data for historical purposes. Data that is written to the Operations Manager database is also written to the data warehouse database, so reports always contain current data. The data warehouse database retains long-term data.

The data retentions in the operational database can be extended from the administration console in the menu Administration --> Settings --> Database Grooming. It is a good idea to extend them to two weeks if you do not have a lot of events in SCOM, because you can plot larger performance charts from the console or search alert events from the console without entering into reporting services. Remember, this is an operational database, so more than four weeks of retention usally can impact  the SCOM perfromance negatively.



The database warehouse by default, is well optimized in terms of  space and speed. For example a database with 50 devices monitorized grows about 3GB every month, but it depends strongly on rules that you have applied on the monitored devices.

During the installation, make sure that you are using one of the following collations:
SQL_Latin1_General_CP1_CI_AS, French_CI_AS, Cyrillic_General_CI_AS, Chinese_PRC_CI_AS, Japanese_CI_AS, Traditional_Spanish_CI_AS, or Latin1_General_CI_AS. No other collation settings are supported.

Finally, in a production enviroments, at least one dedicated database server for both databases is highly recommended

Database structures

Database structures are well documented, Microsoft provides Datawarehouse database schema and some examples in these URLs: http://technet.microsoft.com/en-us/library/gg508713.aspx and http://technet.microsoft.com/en-us/library/gg508714.aspx

SCOM 2012 database schema is provided in HTML by SystemCentral in this URL: http://www.systemcentercentral.com/download/opsmgr-2012-database-schemas-operationsmanager-db/

Useful queries for operations database

In this URL there is an excellent post about SQL queries for operations DBs: http://blogs.technet.com/b/kevinholman/archive/2007/10/18/useful-operations-manager-2007-sql-queries.aspx?PageIndex=4

We are going to rewrite the most useful examples:

Top 20 Alerts in an Operational Database, by Alert Count.

 SELECT TOP 20 SUM(1) AS AlertCount, AlertStringName, AlertStringDescription, MonitoringRuleId, Name   
 FROM Alertview WITH (NOLOCK)   
 WHERE TimeRaised is not NULL   
 GROUP BY AlertStringName, AlertStringDescription, MonitoringRuleId, Name   
 ORDER BY AlertCount 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  

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  

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   

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 groups for a given computer/object (change “computername” in the query below):

 SELECT SourceMonitoringObjectDisplayName AS 'Group'   
 FROM RelationshipGenericView   
 WHERE TargetMonitoringObjectDisplayName like ('%computername%')   
 AND (SourceMonitoringObjectDisplayName IN   
 (SELECT ManagedEntityGenericView.DisplayName   
 FROM ManagedEntityGenericView INNER JOIN   
 (SELECT   BaseManagedEntityId   
 FROM     BaseManagedEntity WITH (NOLOCK)   
 WHERE   (BaseManagedEntityId = TopLevelHostEntityId) AND (BaseManagedEntityId NOT IN   
 (SELECT   R.TargetEntityId   
 FROM     Relationship AS R WITH (NOLOCK) INNER JOIN   
 dbo.fn_ContainmentRelationshipTypes() AS CRT ON R.RelationshipTypeId = CRT.RelationshipTypeId   
 WHERE   (R.IsDeleted = 0)))) AS GetTopLevelEntities ON   
 GetTopLevelEntities.BaseManagedEntityId = ManagedEntityGenericView.Id INNER JOIN   
 (SELECT DISTINCT BaseManagedEntityId   
 FROM     TypedManagedEntity WITH (NOLOCK)   
 WHERE   (ManagedTypeId IN   
 (SELECT   DerivedManagedTypeId   
 FROM dbo.fn_DerivedManagedTypes(dbo.fn_ManagedTypeId_Group()) AS fn_DerivedManagedTypes_1))) AS GetOnlyGroups ON   
 GetOnlyGroups.BaseManagedEntityId = ManagedEntityGenericView.Id))   
 ORDER BY 'Group'  

To find all members of a given group (change the group name below).

 select SourceMonitoringObjectDisplayName as 'Group Name',   
 TargetMonitoringObjectDisplayName as 'Group Members'   
 from RelationshipGenericView   
 where isDeleted=0   
 AND SourceMonitoringObjectDisplayName = 'Agent Managed Computer Group'   
 ORDER BY TargetMonitoringObjectDisplayName  

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  

Useful queries for datawarehouse.

This database is well document by microsoft as we told the provide some basic examples.

As we did with operations database queries, we are going to highlight some queries that we consider very useful.
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 the last 30 days values for a performance counter

 SELECT   
 vPerf.DateTime,  
 vPerf.SampleCount,   
 vPerf.AverageValue,  
 vPerf.MinValue,  
 vPerf.MaxValue,  
 vPerf.StandardDeviation,   
 vPerformanceRuleInstance.InstanceName,   
 vManagedEntity.Path,   
 vPerformanceRule.ObjectName,   
  vPerformanceRule.CounterName  
 FROM Perf.vPerfHourly AS vPerf INNER JOIN  
  vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN  
  vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN  
  vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId   
 WHERE   
 (vPerformanceRule.ObjectName like '%disk%')  
 AND vPerformanceRule.CounterName like '%sec/w%'  
 AND   
 vManagedEntity.Path like '%Hyperclus%'  
 and  
 vPerf.DateTime > getdate()-30  

To view the biggest peaks of a performance counter in last 30 days

 SELECT top 15  
 vPerf.DateTime,  
 vPerf.SampleCount,   
 vPerf.AverageValue,  
 vPerf.MinValue,  
 vPerf.MaxValue,  
 vPerf.StandardDeviation,   
 vPerformanceRuleInstance.InstanceName,   
 vManagedEntity.Path,   
 vPerformanceRule.ObjectName,   
  vPerformanceRule.CounterName  
 FROM Perf.vPerfHourly AS vPerf INNER JOIN  
  vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN  
  vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN  
  vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId   
 WHERE   
 (vPerformanceRule.ObjectName like '%disk%')  
 AND vPerformanceRule.CounterName like '%sec/w%'  
 AND   
 vManagedEntity.Path like '%BD%'  
 and   
 vPerformanceRuleInstance.InstanceName like '%z:\pcs-data'  
 and  
 vPerf.DateTime > getdate()-30  
 ORDER BY vPerf.AverageValue desc  

To view data in performance counter every first day of the month:

 with qry as(  
  SELECT   
  ROW_NUMBER() OVER( PARTITION BY path,CONVERT( VARCHAR, DateTime, 112 )ORDER BY DateTime DESC ) as rn,  
 vPerf.DateTime,  
 vPerf.SampleCount,   
 vPerf.AverageValue,  
 vPerf.MinValue,  
 vPerf.MaxValue,  
 vPerf.StandardDeviation,   
 vPerformanceRuleInstance.InstanceName,   
 vManagedEntity.Path,   
 vPerformanceRule.ObjectName,   
  vPerformanceRule.CounterName  
 FROM Perf.vPerfHourly AS vPerf INNER JOIN  
  vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN  
  vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN  
  vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId   
 WHERE   
 (vPerformanceRule.ObjectName like 'Memory')  
 AND vPerformanceRule.CounterName like 'Available MBytes'  
 AND   
 vManagedEntity.Path like '%Hyper%'  
 and  
 day(vPerf.DateTime) =1   
 )  
 select * from qry q1  
 where rn=1   
 order by datetime  

Most Common Events by event number: 

 SELECT top 50 EventDisplayNumber, COUNT(*) AS TotalEvents   
 FROM Event.vEvent   
 GROUP BY EventDisplayNumber   
 ORDER BY TotalEvents DESC  

How to improve the database performance

In these URLs are well described best practices to mantain and improve the SQL performance.
Pre installation:
http://blog.scomfaq.ch/2012/02/28/system-center-sql-server-2008-r2-performance-tuning/
Post installation:
http://blogs.technet.com/b/kevinholman/archive/2008/04/12/what-sql-maintenance-should-i-perform-on-my-opsmgr-databases.aspx
For example, we have this maintenance plan scheduled every Sunday for SCOM 2007, it could work with SCOM 2012 as well.

 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 ('?')"