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.
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%'
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
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
--Gets the inheritance hierarchy that a given class is derived from
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:
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:
where LastModified > DATEADD(DAY,-1,GETDATE())
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
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
where SourceMaxCardinality <= 1 or TargetMaxCardinality <= 1 or SourceMinCardinality >=1 or TargetMinCardinality >=1
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.
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.
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:
In the Troubleshooting Workflows blog post I described how to troubleshoot workflows using the JobHistory table.
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]
Microsoft.SystemCenter.Connector [SystemCenter.Library MP]
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
select * from MT_System$LinkingFramework$DataSource
select * from MT_Microsoft$Demo$Connectors$CSVConnector
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:
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)
EntityChangeLog - for each transaction, one row for each affected object
<Class>_Log – record of every property change to an object
Sorry no there isnt
i'm having a problem with a missing ManagementPackEnumeration ID.
i get an error in the portal about this, but if i search the DB sure enough it isn't there.
is there a way to find a missing ID elsewhere in the DB, or is it gone?
@Simon - once it is gone it is gone.
thanks for the answer, i was afraid you might say that.
where would i need to start looking to get rid of the error do you think?
should i try re-creating lists hoping i get the right name? or am i pretty much stuffed?!
@shumail - we should be able to track it down
what is the error and where is it appearing?
I need a SQL query which show active incidents of analyst
I have an issue where I need to extract current data from the ServiceManager database about Incidents by Analyst Per queue to create Service Manager Dashboards for certain teams. They are not satisfied with the 1 - 1.5 hour old data in the DWDataMart. Im having trouble figuring out how to relate opened tickets per tier queue to which user opened them in ServiceManager db. Would you happen to know what tables i can join to collect this information?
How to get the cluster server information using ServiceManager database. Say for example. If I give server name as 'Servm1', it should return cluster name as 'cluServ' and nodes as 'Servm01' and 'Servm02'. Is it possible to use a query to find it.
I am noticing in some environments that in the EntityChangeLog table, there are ChangeTypes of 3, 4 or 5. What do those represent? It looks like the 5s may show up in the History tab (Add/Remove Installed Software Item) but it doesn't look like the 3s and 4s. do.
I would like to know a query to run that would pull the active IR(s) and SR(s) for a given analyst?