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
Travis, how about a script that retrieves an incident with ID, title, and Classification?
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?
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.
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.
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.
@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.
Have you seen this blog post:
This may also help:
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.
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:
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.
is there any way to delete data from datawarehouse without uninstallation