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):
--Gets all the classes derived from a given class
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
--Gets the inheritance hierarchy that a given class is derived from
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
Microsoft
