Posts in this blog are provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of UseAre you interested in having a dedicated engineer that will be your Mic

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 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

Number of console Alerts per Day by Resolution State:

SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeAdded, 102)) = 1) THEN 'All Days' ELSE CONVERT(VARCHAR(20), TimeAdded, 102) 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) WHERE TimeRaised is not NULL GROUP BY CONVERT(VARCHAR(20), TimeAdded, 102), ResolutionState WITH ROLLUP ORDER BY DATE DESC

(Note: There will be more alerts in the "Alert" table in the form of rows, than exist in the console. This is because there are non-console alerts where TimeRaised is NULL - these have to do with driving state change records, and are not included in the above queries by design)

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, 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

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

Most common events by event number and event publishername: (This gives us the event source name to help see what is raising these events)

SELECT top 20 Number as EventID, COUNT(*) AS TotalEvents, Publishername as EventSource FROM EventAllView eav with (nolock) 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 as EventID, COUNT(*) AS TotalEvents, Publishername as EventSource, EventParameters FROM EventAllView with (NOLOCK) 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 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

Computers generating the most events, grouped by identical event number and publishername:

SELECT top 20 LoggingComputer as ComputerName, COUNT(*) AS TotalEvents, PublisherName as EventSource, Number as EventID FROM EventallView with (NOLOCK) GROUP BY LoggingComputer, PublisherName, Number ORDER BY TotalEvents DESC

Performance Section:

Performance insertions per day:

SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeSampled, 102)) = 1) THEN 'All Days' ELSE CONVERT(VARCHAR(20), TimeSampled, 102) END AS DaySampled, COUNT(*) AS PerfInsertPerDay FROM PerformanceDataAllView with (NOLOCK) GROUP BY CONVERT(VARCHAR(20), TimeSampled, 102) WITH ROLLUP ORDER BY DaySampled DESC

Top 20 performance insertions by perf object and counter name:

select top 20 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

SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeGenerated, 102)) = 1) THEN 'All Days' ELSE CONVERT(VARCHAR(20), TimeGenerated, 102) END AS DayGenerated, COUNT(*) AS StateChangesPerDay FROM StateChangeEvent WITH (NOLOCK) GROUP BY CONVERT(VARCHAR(20), TimeGenerated, 102) WITH ROLLUP ORDER BY DayGenerated 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

Performance Signature Section:

To find the rules collecting the most Performance Signature data in the database:

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:

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

Find find the entity data on all members of a given group (change the group name below):

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')

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:

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

To find a computer name from a HealthServiceID (guid from the Agent proxy alerts)

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

UPDATE MT_HealthService SET IsManuallyInstalled=0 WHERE IsManuallyInstalled=1

Now – the above query will set ALL agents back to “Remotely Manageable = Yes” in the console. If you want to control it agent by agent – you need to specify it by name here:

UPDATE MT_HealthService SET IsManuallyInstalled=0 WHERE IsManuallyInstalled=1 AND BaseManagedEntityId IN (select BaseManagedEntityID from BaseManagedEntity where BaseManagedTypeId = 'AB4C891F-3359-3FB6-0704-075FBFE36710' AND DisplayName = 'servername.domain.com')

Get the instance space of all agents (Thanks to Hui and Michael Pearson)

To clean up old StateChangeEvent data for state changes that are older than the defined grooming period, such as monitors currently in a disabled, warning, or critical state. By default we only groom monitor statechangeevents where the monitor is enabled and healthy at the time of grooming.

USE [OperationsManager] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO BEGIN

-- This is to update the settings table -- with the max groomed data SELECT @MaxTimeGroomed = MAX(TimeGenerated) FROM dbo.StateChangeEvent WHERE TimeGenerated < @GroomingThresholdUTC

IF @MaxTimeGroomed IS NULL GOTO Success_Exit

-- Instead of the FK DELETE CASCADE handling the deletion of the rows from -- the MJS table, do it explicitly. Performance is much better this way. DELETE MJS FROM dbo.MonitoringJobStatus MJS JOIN dbo.StateChangeEvent SCE ON SCE.StateChangeEventId = MJS.StateChangeEventId JOIN dbo.State S WITH(NOLOCK) ON SCE.[StateId] = S.[StateId] WHERE SCE.TimeGenerated < @GroomingThresholdUTC AND S.[HealthState] in (0,1,2,3)

SELECT @Err = @@ERROR IF (@Err <> 0) BEGIN GOTO Error_Exit END

WHILE (@RowCount > 0) BEGIN -- Delete StateChangeEvents that are older than @GroomingThresholdUTC -- We are doing this in chunks in separate transactions on -- purpose: to avoid the transaction log to grow too large. DELETE TOP (10000) SCE FROM dbo.StateChangeEvent SCE JOIN dbo.State S WITH(NOLOCK) ON SCE.[StateId] = S.[StateId] WHERE TimeGenerated < @GroomingThresholdUTC AND S.[HealthState] in (0,1,2,3)

select statesetbyuserid, count(*) as 'Number of Alerts' from Alert.vAlertResolutionState ars where resolutionstate = '255' group by statesetbyuserid order by 'Number of Alerts' DESC

Events Section:

To inspect total events in DW, and then break it down per day: (this helps us know what we will be grooming out, and look for partitcular day event storms)

SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), DateTime, 101)) = 1) THEN 'All Days' ELSE CONVERT(VARCHAR(20), DateTime, 101) END AS DayAdded, COUNT(*) AS NumEventsPerDay FROM Event.vEvent GROUP BY CONVERT(VARCHAR(20), DateTime, 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 EventDisplayNumber, COUNT(*) AS TotalEvents FROM Event.vEvent GROUP BY EventDisplayNumber ORDER BY TotalEvents DESC

Most common events by event number and raw event description (this will take a very long time to run but it shows us not only event ID - but a description of the event to help understand which MP is the generating the noise)

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)

Here is a better view of the current data retention in your data warehouse:

select ds.datasetDefaultName AS 'Dataset Name', sda.AggregationTypeId AS 'Agg Type 0=raw, 20=Hourly, 30=Daily', sda.MaxDataAgeDays AS 'Retention Time in Days' from dataset ds, StandardDatasetAggregation sda WHERE ds.datasetid = sda.datasetid ORDER by ds.datasetDefaultName

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'

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:

You can get space used by type by using the large table query.... which is why I include and use that one all the time. It could be made prettier... but it works. There is also a report in SQL 2005 SP2 for reporting on table by size.... which will show you this. The tables are named to be fairly self explanatory.

Anonymous

17 Jul 2008 5:50 PM

I've been getting a few questions lately about slow performance with the console so I thought a post

sna

20 Nov 2008 2:11 AM

Hi - This is a great post.

I was wondering if you could help me with a question.

In the view “PerformanceDataAllView” in the “OperationsManager” database, what do the columns “TimeSampled” and “TimeAdded” mean?

Thanks,

Sna

Availability Query

18 Dec 2008 11:29 PM

Hello,

Can you post a query one would run to get availability of a server for a certain time period?

Your report works fine, but I needed to get last 7 days data which not included in your report because you are getting data from Datawarehouse by default we know Opsmgr keeps 7 days alert data in the OperationManager DB so I figured out from your query and modify it against OperationManager DB.

My issue was I could not figured out which field should I for data and time parameter. I think I found out,