Service Manager Database Tour & Useful Queries

Service Manager Database Tour & Useful Queries

  • Comments 27
  • Likes

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Travis, how about a script that retrieves an incident with ID, title, and Classification?

  • @DougSigmon

    Doing that with a database query in the ServiceManager database is kind of tricky and not really a recommend approach.  If you really want that I can show you of course but have you seen our new PowerShell cmdlet project with incident cmdlets?  

    blogs.technet.com/.../new-codeplex-project-service-manager-powershell-cmdlets.aspx

    Would that work for you or do you still want to see a T-SQL way to do it?

  • Thanks Travis.  I've downloaed your cmdlets and will try that approach.  

    My goal is to determine what ID I need to use to import tickets from my Altiris system into Service Manager. I'm stuck on the classification and can't determine the right ID for my custom classification list (social.technet.microsoft.com/.../e3253185-f01c-4f33-bcf2-ca5773f26a8b).  I was hoping to dig into and existing ticket and see what SCSM 2010 entered into that field.  

  • @Doug Sigmon -

    OK, makes sense.  This is the query you need to run:

    select * from MTV_System$WorkItem$Incident

    You might want to do a where clause for a particular ID or order by CreatedDate to make it easier to find a particular incident.

  • Travis, one question.

    The info of the table MT_System$WorkItem$TroubleTicket$AnalystCommentLog_Log in ServiceManager is load to database DWDataMart?

    I need this information, but I can not find it.

    Thank's in advance.

  • Travis,

    Is there a discussion like what you present above, but pertaining to the tables and views of the data warehouse? I like the insight you provide regarding the relationships among the tables in the Service Manager database, and I'd like to see more on the Service Manager database, and on the data warehouse database.

    Thank you.

    Adam Bryer

  • @Adam -

    There isnt a blog post on that topic yet, because the documentation team is working on some official documentation to cover that topic.  Hopefully, we'll have it out pretty soon.   I'll announce it on the blog when it is available because I know a lot of people are looking for that.

  • Travis, it would be nice (mgmt, who owns the purse strings, wants animal picture reports) to query for incidents not assigned to an analyst or top X whatever tickets using the accelerator dashboard with grid view since there are no web parts for analysts views of incidents.  Is the tricky SQL for incidents that bad? thx

  • Hi, where does SCSM keep the workItem-History log in the database? I need it to be able to report on the status-changes of workitems.

    Greetings

  • @Peter De Coninck

    It is stored on a combination fo the <Class>_Log and EntityChangeLog tables.  I wouldnt recommend querying on those tables for reporting purposes though.  You should use the DWDataMart database for report data.

  • I was planning on querying the DWDataMart views, but I need to extract specific details about when an incident was changed, what the new status is, who changed it, .... and I can't find that data in DWDataMart.

  • @Peter  -

    Have you seen this blog post:

    blogs.technet.com/.../service-manager-data-warehouse-schema-now-available.aspx

    This may also help:

    blogs.technet.com/.../create-a-report-model-with-localized-outriggers-aka-lists.aspx

  • hi,

    i want to delete all incidents which i have created in testing from scsm 2010 sp1 and i want to reset my IR counter as well need urgent help.

  • @shumail -

    Deleting incidents -

    Download SMLets (http://smlets.codeplex.com

    Then run this command:

    Get-SCSMObject -Class (Get-SCSMClass -Name System.WorkItem.Incident$) | Remove-SCSMObject -Force

    To reset the counter see this blog post:

    blogs.technet.com/.../top-secret-trick-how-to-change-the-auto-incrementing-value-range.aspx

    If you have set up the data warehouse before you should unregister the data warehouse, uninstall it, and then reinstall and re-register it after you have deleted all the data.

  • thanx

    is there any way to delete data from datawarehouse without uninstallation