The following are some useful database queries and in a way a tour of the Service Manager database.  Sometimes it's just easier to query a database to get the information you need.  Please use this information in a "read only" way.  Don't attempt to update the Service Manager database using T-SQL - it's just bound to cause problems as this is a fairly complex database.  Ideally, you should have all programmatic access to Service Manager go through the SDK/Data Access Service.  If for some reason, you absolutely have to access the database directly, please use the provided views that come out of the box or create your own.  There are no guarantees that the schema will remain constant over time.

Management Pack Table

First of all the ManagementPack table stores all of the management packs. Many other tables in the Service Manager database have a foreign key of the ManagementPack table in them. The ManagementPack table is a great place to see the current contents of a management pack – for example:

select MPName, CONVERT(xml,MPXML) from ManagementPack order by MPName

Gets you a result set that lets you simply click on the MPXML link to see the XML document of the management pack. No need to export MPs all the time! J

The Management Pack table is a convenient place to look up the identity (MPName, Version, and PublicKey token of an MP you are taking a dependency on when you are creating management pack references. You can also verify that the MP is sealed so that you can take a dependency on it.

select MPName, MPVersion, MPKeyToken, MPIsSealed from ManagementPack where MPName like '%workitem%'

ManagedType Table

The ManagedType table stores all of the classes (ClassType Management Pack element) in Service Manager.

One useful query involving the ManagedType table looks up the exact ID and which MP that Class is in so that you can use it as a base class for some new class you are creating/extending.

select MT.TypeName, MP.MPName from ManagedType as MT

inner join ManagementPack as MP

on MT.ManagementPackId = MP.ManagementPackId

where MT.TypeName like '%workitem%'

You might also ask yourself sometimes – where is the table/view that I can query to see instances of a particular class?

select TypeName, ManagedTypeTableName, ManagedTypeViewName

from ManagedType

where TypeName like '%workitem%'

order by TypeName

Ever wonder what the parent class is for a given class?

select MTc.TypeName as 'Child Class Name', MTp.TypeName as 'Parent Class Name'

from ManagedType as MTc

LEFT OUTER JOIN ManagedType as MTp

on MTc.BaseManagedTypeId = MTp.ManagedTypeId

There’s an even better way to figure out class inheritance.  Check out these examples provided by Joel Pothering (one of our devs on the database team): 

select BT.TypeName as BaseTypeName, MT.*

from DerivedManagedTypes DMT

inner join ManagedType BT -- base type

on BT.ManagedTypeId = DMT.BaseTypeId

inner join ManagedType MT -- derived type

on MT.ManagedTypeId = DMT.DerivedTypeId

where BT.TypeName = N'System.Device'

order by DMT.Level asc

select BT.TypeName as BaseTypeName, MT.*

from DerivedManagedTypes DMT

inner join ManagedType BT -- base type

on BT.ManagedTypeId = DMT.BaseTypeId

inner join ManagedType MT -- derived type

on MT.ManagedTypeId = DMT.DerivedTypeId

where MT.TypeName = N'Microsoft.Windows.Computer'

order by DMT.Level asc

BaseManagedEntity Table

The BaseMangedEntity table stores a record for every single object in the database. The BaseManagedEntityId is the unique identifier of every object in the system. Fun factoid: the BaseManagedEntityId is generated automatically as a hash of the key property values for a given object. For example, if a Windows Computer has a fully qualified domain name of twright-desktop.contoso.com the BaseManagedEntityId will always be D10F1F6B-24AC-51D7-41E9-23768D0F6CD0 regardless of which management group/database this object exists in (even across SCE/SCOM/SCSM databases). This makes it really easy to join together data from multiple management groups/System Center products that are based on the common System Center platform.

The BaseManagedEntity table has some interesting fields on it:

• FullName – this is a generated field which is <ClassID>:<KeyProperty1>.<KeyProperty2>.<KeyPropertyN>
  • For example: the Microsoft.Windows.Computer key property is PrincipalName (aka FQDN) so the FullName is:

    Microsoft.Windows.Computer:twright-desktop.contoso.com

• Name – this is a generated field which is <KeyProperty1>.<KeyProperty2>.<KeyPropertyN>
  • For example: the System.Domain.User class has two key properties – UserName and DomainName so the Name in this case is:

    CONTOSO.Administrators

• DisplayName – this is the only property defined on the System.Entity class which is inherited to all other classes. It is the name of the object that is displayed in the UI. It can be whatever the code author decides to put in there. For example, the AD connector inserts users with a DisplayName in the format <Domain>\<UserName> like this:

  CONTOSO\Administrator

Now you can start to do some interesting queries with this information. For example:

select FullName, DisplayName from BaseManagedEntity

where FullName like '%Computer%contoso%'

give yous a list of all the computers in the contoso domain.

Of course you have to be careful with like queries and the more specific you can be the better but this can give you a quick idea of something.

You can also look up objects that have been added or updated in a certain period of time like this:

select FullName, DisplayName from BaseManagedEntity

where LastModified > DATEADD(DAY,-1,GETDATE())

select FullName, DisplayName from BaseManagedEntity

where TimeAdded > DATEADD(DAY,-1,GETDATE())

In most queries that you run on the BaseManagedEntity table, you will want to filter out objects which have been marked deleted but have not yet been groomed from the database.

select Count(*) from BaseManagedEntity

where IsDeleted = 0 -- 0 = Not deleted

You may also want to join the BME table the ManagedType table together so you can see what class each object is:

select MT.TypeName, BME.DisplayName from BaseManagedEntity as BME

inner join ManagedType as MT on BME.BaseManagedTypeId = MT.ManagedTypeId

RelationshipType Table

The RelationshipType table stores the RelationshipTypes defined in Management Packs. Some useful queries here such as get the parent type of all Relationship Types so you can see what Relationship types are Reference vs. Containment vs. Membership etc:

select RTc.RelationshipTypeName as 'Relationship Type Child Name', RTp.RelationshipTypeName as 'Relationship Type Parent Name'

from RelationshipType as RTc

left outer join RelationshipType as RTp

on RTc.BaseRelationshipTypeId = RTp.RelationshipTypeId

This query will get you all the relationship types in the system with their source and target types and the names of the source/target pointers:

select RT.RelationshipTypeName, MTs.TypeName as 'Source Type',RT.SourceName, RT.TargetName, MTt.TypeName as 'Target Type'

from RelationshipType as RT, ManagedType as MTs, ManagedType as MTt

where RT.SourceManagedTypeId = MTs.ManagedTypeId and RT.TargetManagedTypeId = MTt.ManagedTypeId

This relationship query will get you a list of all of the relationship types that have special constraints on their cardinality. Most relationship types are many:many but some have constraints on the min or max number of related items that can be on either the source or target side of the relationship type.

select RelationshipTypeName, SourceMaxCardinality, SourceMinCardinality, TargetMaxCardinality, TargetMinCardinality

from RelationshipType

where SourceMaxCardinality <= 1 or TargetMaxCardinality <= 1 or SourceMinCardinality >=1 or TargetMinCardinality >=1

Relationships Table

The relationships table stores all of the actual relationship instances that exist between objects in the database.

You can combine your knowledge of the BaseManagedEntity, RelationshipType, and Relationships table to look up some interesting information.

select BMEs.DisplayName, RT.RelationshipTypeName, BMEt.DisplayName

from BaseManagedEntity as BMEs, BaseManagedEntity as BMEt, Relationship as R, RelationshipType as RT

where BMEs.BaseManagedEntityId = R.SourceEntityId and

BMEt.BaseManagedEntityId = R.TargetEntityId and

RT.RelationshipTypeId = R.RelationshipTypeId

Will give you the source and target object display names and relationship type names for every relationship that exists in the system. You could further filter that down by some using some where clause magic.

select BMEs.DisplayName, RT.RelationshipTypeName, BMEt.DisplayName

from BaseManagedEntity as BMEs, BaseManagedEntity as BMEt, Relationship as R, RelationshipType as RT

where BMEs.BaseManagedEntityId = R.SourceEntityId and

BMEt.BaseManagedEntityId = R.TargetEntityId and

RT.RelationshipTypeId = R.RelationshipTypeId

and BMEs.FullName like '%contoso%'

and RT.RelationshipTypeName = 'System.UserManagesUser'

which will show me only the manager:user relationship that exist for users in the contoso domain.

Management Pack Element Tables

Sometimes taking a look at management pack contents across management packs is useful. The following tables store some of the more common management pack elements:

• Category
• ConsoleTask
• EnumType
• Folder
• Form
• ObjectTemplate
• Rules
• Task
• TypeProjection
• Views

JobHistory Table

In the Troubleshooting Workflows blog post I described how to troubleshoot workflows using the JobHistory table.

The MT_ Tables

The MT_... tables store the object property level data in them. For example the MT_Computer table stores the records for all of the Microsoft.Windows.Computer class properties.

select * from MT_Computer

Every new non-abstract class added to Service Manager via an MP will add a new table as described in the Model-based Database blog post.

What this means is that the property values for any given object can actually reside on multiple tables. For example, in the CSV Connector post we created a new derived class with the following model:

System.Entity [System.Library MP]

    .DisplayName

Microsoft.SystemCenter.Connector [SystemCenter.Library MP]

.Id

.Description

.Name

.DiscoveryDataIsManaged

.DiscoveryDataIsShared

    System.LinkingFramework.DataSource [ServiceManager.LinkingFramework.Library]

    .DataProviderName

    .DataProviderDisplayName

    .Enabled

.SyncTime

    .SolutionName

    .ReaderProfileName

    .Reserved

    .ImpersonationEnabled

    .SyncType

    .SyncInterval

    .SyncNow

        Microsoft.Demo.Connectors.CSVConnector [Microsoft.Demo.Connectors]

        .DataFilePath

        .MappingFilePath

        .NumberMinutes

The DisplayName property is stored on the BasedManagedEntity table. The properties defined on the Microsoft.SystemCenter.Connector class are stored on the MT_Connector table and the properties defined on the System.LinkingFramework.DataSource class are stored on the MT_System$LinkingFramework$DataSource table. A new table was created for the Microsoft.Demo.Connectos.CSVConnector class and stores the properties associated with that.

So – if we run a query that gets the same object off of all the tables you can see how they are stored on multiple tables:

select * from BaseManagedEntity

where BaseManagedEntityId = '3BE18F5E-4FEA-D91B-2E0B-1C4C3963C04D'

select * from MT_Connector

where BaseManagedEntityId = '3BE18F5E-4FEA-D91B-2E0B-1C4C3963C04D'

select * from MT_System$LinkingFramework$DataSource

where BaseManagedEntityId = '3BE18F5E-4FEA-D91B-2E0B-1C4C3963C04D'

select * from MT_Microsoft$Demo$Connectors$CSVConnector

where BaseManagedEntityId = '3BE18F5E-4FEA-D91B-2E0B-1C4C3963C04D'

The Log Tables

Service Manager keeps track of every property value change and relationship add/remove for essentially every object in the system. This information is stored on the "log" tables. Each MT_ table has a corresponding _Log table where all of the changes are kept. For example, we can see a history of property changes of a given class of objects like this:

select * from MT_Computer_Log

As you can see above these were the log entries when these computers were first added to the database. That's why the Pre values are mostly NULL and the post values have a value.

There is an EntityChangeLog table which keeps track of the change that occurred to a given object that spans multiple tables (remember how we just talked about that a given object is stored on multiple MT_ tables?) Each of the _Log tables is mapped back to the EntityChangeLog table by the EntityChangeLogId Foreign Key. The EntityChangeLog table has a ChangeType field on it. This is the meaning of the values there:

• 0 = Add
• 1 = Update
• 2 = Delete

There is also a EntityTransactionLog table. This is a FK to the EntityChangeLog table and keeps track of the changes across multiple objects. For example, if you change an incident to add a comment to the action log, change who the assigned to owner is, and edit an embedded activity's properties you are actually modifying multiple objects at the same time. The EntityTransactionLog table takes care of that bookkeeping.

Here is an example of how a single transaction spans multiple each of the log tables:

EntityTransactionLog - somebody did something (i.e. Add Activity to Incident)

• Who (logged as a string – domain\username)
• Date/Time (UTC)
• Discovery Source (user or connector) – FK from DiscoverySource

EntityChangeLog - for each transaction, one row for each affected object

• ChangeType (0=Create; 1=Update; 2=Delete)
• Affected Object (aka "Entity") ID
• Class or RelationshipType (aka "EntityType") ID
• Related Affected Object ID (if EntityTypeID is a RelationshipTypeID)
• Below the first row is for the creation of the Activity work item object and the second is for the creation of the relationship between the Incident and the Activity

<Class>_Log – record of every property change to an object

• Example: Create an activity, then change the Title
• Below, the first row is for the initial creation and the second row is for the Title update