SQL Query – OperationManager Database Statistics

Thanks for taking my Poll!

OperationManager Database Statistics

A good friend of mine provided me with this SQL Query that will give you a bunch of Statistics about your Operations Manager Database. This is such great information I asked him if I could share it with the world and he said it was ok. Below is the query, quick note though you will want to choose the “Results to Text” from the query manager tool bar. Otherwise it will output in separate tables and can be difficult to read.

SQL Query :

— Some basic statistics queries
select ‘MP element numbers’
select ‘MP’, count(*) from ManagementPack
select ‘Discovery’, count(*) from Discovery
select ‘Monitor’, count(*) from Monitor
select ‘Rules’, count(*) from Rules
select ‘MonitorOverride’, count(*) from MonitorOverride
select ‘ModuleOverride’, count(*) from ModuleOverride
select ‘InstanceOverride’, count(*) from InstanceOverride

select ‘Agent numbers’
select v.IsAgent, v.IsGateway, v.IsManagementServer, count(*) from MTV_healthservice v
group by v.IsAgent, v.IsGateway, v.IsManagementServer
order by v.IsAgent, v.IsGateway, v.IsManagementServer

select ‘Computer numbers’
select count(*) from MTV_Computer

select ‘Action partitions’
select * from Partitiontables where iscurrent = 1

select ‘Table sizes’
SELECT s.object_id, o.type_desc, sum ( used_page_count ) * 8 as SizeKB,
sum(row_count) as [RowCount], object_name ( s.object_id ) AS TableName
FROM sys.dm_db_partition_stats s
inner join sys.objects o on s.object_id = o.object_id
WHERE index_id=0 or index_id=1
GROUP BY s.object_id, o.type_desc
ORDER BY TableName

select ‘Performance sample statistics’
select COUNT(*), min(timeadded), max(timeadded)
from PerformanceDataInsertView with(nolock)

select ‘Event statistics’
select COUNT(*), min(timeadded), max(timeadded)
from EventInsertView with(nolock)

select ‘Event Message statistics’
select
‘EventMsgLength’,
(len(LocalizedText.LTValue)/100)*100,
(len(LocalizedText.LTValue)/100)*100+100,
COUNT(distinct LocalizedText.LTStringId),
COUNT(*)
from EventInsertView with(nolock)
inner join PublisherMessages with(nolock)
on EventInsertView.PublisherId = PublisherMessages.PublisherId
and EventInsertView.FullNumber = PublisherMessages.MessageId
inner join LocalizedText with(nolock)
on LocalizedText.LTStringId = PublisherMessages.MessageStringId
group by (len(LocalizedText.LTValue)/100)*100
order by (len(LocalizedText.LTValue)/100)*100

select ‘Alert statistics’
select
DATEPART(yyyy,TimeAdded) AS ‘YEAR’,
DATEPART(mm,TimeAdded) AS ‘MONTH’,
DATEPART(dd,TimeAdded) AS ‘DAY’,
COUNT(*),
sum(Alert.RepeatCount + 1)
from Alert with(nolock)
group by
DATEPART(yyyy,TimeAdded),
DATEPART(mm,TimeAdded),
DATEPART(dd,TimeAdded)
order by
DATEPART(yyyy,TimeAdded),
DATEPART(mm,TimeAdded),
DATEPART(dd,TimeAdded)

select ‘State change statistics’
select
DATEPART(yyyy,TimeAdded) AS ‘YEAR’,
DATEPART(mm,TimeAdded) AS ‘MONTH’,
DATEPART(dd,TimeAdded) AS ‘DAY’,
COUNT(*)
from StateChangeEvent with(nolock)
group by
DATEPART(yyyy,TimeAdded),
DATEPART(mm,TimeAdded),
DATEPART(dd,TimeAdded)
order by
DATEPART(yyyy,TimeAdded),
DATEPART(mm,TimeAdded),
DATEPART(dd,TimeAdded)

select ‘Relationship Discovery statistics’
select
DATEPART(yyyy,ecl.LastModified) AS ‘YEAR’,
DATEPART(mm,ecl.LastModified) AS ‘MONTH’,
DATEPART(dd,ecl.LastModified) AS ‘DAY’,
ecl.ChangeType,
COUNT(*),
count(distinct ecl.EntityTransactionLogId)
from EntityChangeLog ecl with(nolock)
where ecl.RelationshipId is not null
group by
DATEPART(yyyy, ecl.LastModified),
DATEPART(mm,ecl.LastModified),
DATEPART(dd,ecl.LastModified),
ecl.ChangeType
order by
DATEPART(yyyy,ecl.LastModified),
DATEPART(mm,ecl.LastModified),
DATEPART(dd,ecl.LastModified),
ecl.ChangeType

select ‘Entity Discovery statistics’
select
DATEPART(yyyy,ecl.LastModified) AS ‘YEAR’,
DATEPART(mm,ecl.LastModified) AS ‘MONTH’,
DATEPART(dd,ecl.LastModified) AS ‘DAY’,
ecl.ChangeType,
COUNT(*),
count(distinct ecl.EntityTransactionLogId)
from EntityChangeLog ecl
where ecl.RelationshipId is null
group by
DATEPART(yyyy, ecl.LastModified),
DATEPART(mm,ecl.LastModified),
DATEPART(dd,ecl.LastModified),
ecl.ChangeType
order by
DATEPART(yyyy,ecl.LastModified),
DATEPART(mm,ecl.LastModified),
DATEPART(dd,ecl.LastModified),
ecl.ChangeType

select ‘Performane signature statistics’
select ‘Data’, COUNT(*) from PerformanceSignatureData with(nolock)
select ‘History’, COUNT(*) from PerformanceSignatureHistory with(nolock)

select ‘Instance space statistics’
select ‘BME’, COUNT(*) from BaseManagedEntity with(nolock)
select ‘TME’, COUNT(*) from TypedManagedEntity with(nolock)
select ‘Relationship’, COUNT(*) from Relationship with(nolock)
select ‘RecursiveMembership’, COUNT(*) from RecursiveMembership with(nolock)
select ‘DiscoverySourceToRelationship’, COUNT(*) from DiscoverySourceToRelationship with(nolock)
select ‘DiscoverySourceToTypedManagedEntity’, COUNT(*) from DiscoverySourceToTypedManagedEntity with(nolock)

I hope this helps others as much as it has me.

Good Luck!

Advertisements

~ by Josh on July 1, 2013.

5 Responses to “SQL Query – OperationManager Database Statistics”

  1. not sure what version of SQL you are running this on as some of those tables don’t exist in a 2012 setup? Have had no joy getting this to work and have tried manipulating it quite a lot! Any help please?

  2. The version of SQL I was using is SQL Server Enterprise 2008 R2 SP2. Also it is something to note that my version of SCOM is currently 2012 with Update Roll-up 3 applied. (Note: It is not on SP1) Also one think to be sure to take note of is that it has to be ran against the OpsMgr Database not the Data Warehouse Database. The tables that are being selected here are pretty much standard and to my knowledge do not change in SP1 or R2.

  3. Hi, thanks for that, yes I run 2012 SP1 UR3 and SQL 2012 SP1, I don’t see the same tables as you in my version so have been trying to adjust the query to reflect that (no success yet!)

  4. I get lots of errors like this and when I exapnd the Tables and look for those columns I can see they don’t exist in the OperationsManager DB? Any help at all please?

    Msg 207, Level 16, State 1, Line 1
    Invalid column name ‘MP element numbers’.
    Msg 207, Level 16, State 1, Line 2
    Invalid column name ‘MP’.
    Msg 207, Level 16, State 1, Line 3
    Invalid column name ‘Discovery’.
    Msg 207, Level 16, State 1, Line 4
    Invalid column name ‘Monitor’.
    Msg 207, Level 16, State 1, Line 5
    Invalid column name ‘Rules’.
    Msg 207, Level 16, State 1, Line 6
    Invalid column name ‘MonitorOverride’.
    Msg 207, Level 16, State 1, Line 7
    Invalid column name ‘ModuleOverride’.
    Msg 207, Level 16, State 1, Line 8
    Invalid column name ‘InstanceOverride’.
    Msg 207, Level 16, State 1, Line 10
    Invalid column name ‘Agent numbers’.

    • C,
      Unfortunately at this time I don’t have a SCOM 2012 SP1 database to look at as reference and testing. It is possible that this query will only work for SCOM 2012 version. I do not think the schema would have changed that much to SP1, however I thought the same thing from 2007 R2 to 2012 as well and was wrong. Once I get my hands on a SP1 DB I will let you know what I find.

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

 
%d bloggers like this: