More Service Manager Useful Queries – Entity Change Log

Author by Christopher Mank

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. EntityChangeLogId 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. Type Indicates whether the change is an Object or Relationship change. ChangeType Indicates what type of change it is (Add, Update, Delete, Staged, etc.). EntityDisplayName 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. ECL Query 1 Image EntityOrRelationshipType 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. SubscriptionSpecific 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. ContextGenerated This column indicates who made the change. LastModified 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. ECL Query 2 Image Copy/Paste Mode SELECT TOP 1000 ECL.EntityChangeLogId, CASE WHEN ECL.RelatedEntityId IS NULL THEN 'Object' ELSE 'Relationship' END AS 'Type', CASE 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', CASE WHEN ECL.RelatedEntityId IS NULL THEN MT.TypeName ELSE RT.RelationshipTypeName END AS 'EntityOrRelationshipType', ECL.SubscriptionSpecific, ETL.ContextGenerated, dbo.fn_GetLocalTime(ECL.LastModified) AS 'LastModified' FROM EntityChangeLog ECL 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 ORDER BY ECL.EntityChangeLogId DESC 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, Christopher

Christopher Mank

Systems Architect