Awhile back, Travis posted a blog on some useful queries as a way to tour the CMDB (found here
). As he mentions, sometimes it is just faster and easier to get the data you are looking for just by querying the database itself.
Over the past months, I’ve found myself building my own useful queries that have helped me tremendously in learning more about how the system works and assist in troubleshooting various issues. So, I wanted to share these queries in hopes that others will also find them useful. Today’s query will focus on the Entity Change Log.
Entity Change Log
The Service Manager CMDB does a great job of tracking all of the changes made to it. You can see all of these changes in the EntityChangeLog table. Any time an object/relationship is added/changed/deleted, it creates an entry in this table. This is all well and good, however the table is mostly made up of GUIDs that relate back to other tables. So, I wanted a query that would show everything that's going in the environment from this table. Let's take a look at each column.
Each change is given a unique Id. This is used to relate back to other tables. This column is in the query merely for informational purposes.
Indicates whether the change is an Object or Relationship change.
Indicates what type of change it is (Add, Update, Delete, Staged, etc.).
This column is the Display Name of the object being modified. If it is a relationship, it shows the Display Name of the source object.
This column shows what type of object or relationship the Entity being modified is. This will show either a Class name or Relationship Type name.
This column indicates whether or not the change was initiated from a Subscription. You shouldn't see too many of these since there is a groom job that runs every 15 minutes to clear these out.
This column indicates who made the change.
This column indicates when the change took place. You'll notice in the query it uses a custom SQL function called fn_GetLocalTime. Please refer to the following blog post
on what this function does.
SELECT TOP 1000
WHEN ECL.RelatedEntityId IS NULL THEN 'Object' ELSE 'Relationship'
END AS 'Type',
WHEN ECL.ChangeType = 0 THEN 'Add'
WHEN ECL.ChangeType = 1 THEN 'Update'
WHEN ECL.ChangeType = 2 THEN 'Delete'
WHEN ECL.ChangeType = 3 THEN 'Staged'
ELSE CONVERT(VARCHAR, ECL.ChangeType)
END AS 'ChangeType',
BME.DisplayName AS 'EntityDisplayName',
WHEN ECL.RelatedEntityId IS NULL THEN MT.TypeName ELSE RT.RelationshipTypeName
END AS 'EntityOrRelationshipType',
dbo.fn_GetLocalTime(ECL.LastModified) AS 'LastModified'
LEFT OUTER JOIN EntityTransactionLog ETL
ON ECL.EntityTransactionLogId = ETL.EntityTransactionLogId
LEFT OUTER JOIN ManagedType MT
ON ECL.EntityTypeId = MT.ManagedTypeId
LEFT OUTER JOIN RelationshipType RT
ON ECL.EntityTypeId = RT.RelationshipTypeId
LEFT OUTER JOIN BaseManagedEntity BME
ON ECL.EntityId = BME.BaseManagedEntityId
And that’s all there is to it. Stay tuned for more useful queries you can use for your own Service Manager environment.
Until the Whole World Hears,