Service Manager Change Log Grooming – Part 2: Custom Grooming

Author by Christopher Mank

In the first part of this blog series, we took a deep dive into the Service Manager Change Log grooming workflow and explained exactly how it works.  In this last part, we'll walk through how we can build our own custom Change Log groom job and what the use cases are for doing so. Part 1: Deep Dive Part 2: Custom Grooming (this post) Use Cases Before jumping into how you can create your own custom Change Log grooming, we first need to identify some use cases as to why you might consider doing so.  Here are the big ones that I've seen. 1.  As mentioned in the first post, there is code in the out-of-box groom job that numbers every change for an object, so that the most recent change does not get deleted, even if it meets the other grooming criteria.  The issue here is if you have hundreds of thousands of objects in your CMDB, this portion of the code can cause the workflow to run longer than the default 30 minutes.  A way to get around this is to write a custom job so that we can allow it to run longer than 30 minutes without throwing an error. Entity Changes Image 2.  I have found there are times where it would be helpful to apply different change log retention periods for different objects.  A great example of this are asset related records, if you are using Service Manager as your asset management system.  Since change logs do NOT move over to the Data Warehouse, many organizations require their asset databases to store history records for longer periods of time.  Since we don't want to track that many changes on all objects, we can write a custom job that will exclude those 'special' objects. Custom Change Log Grooming Now that we've seen a few use cases, the below steps walk through how we can build our own custom groom job using Orchestrator. 1.  The first thing we need to do is turn off the out-of-box groom job.  Since this exists in a sealed Management Pack we can use an override to accomplish this.  For more information on how to create an override, please refer to the following link.  Below is an example of what some of the main pieces would look like. GroomChangeLogOverrideReferences Image GroomChangeLogOverride Image 2. Next, we need to create an Orchestrator Runbook that will do the change log grooming for us.  Below is an example of what this could look like. Groom Change Log Runbook image The activities are pretty straight forward, with the exception of the Groom Change Logs database query.  Let's step through what that does. In this activity, instead of just executing the p_GroomChangeLogs stored procedure, we took all of the SQL code and placed it directly in the activity.  The main reason for this is to keep the out-of-box job intact, just in case we ever need to go back to it. The full code can be found below, but we should highlight a few areas of importance: A.  The code does not take watermarks into account.  The reason for this is because in many of the real large environments, we have most workflows off and Orchestrator is performing those actions.  This can certainly be added back in if you wish. B.  We are not taking into account the most recent change.  When working with very large environments, removing this code helps to speed up the groom process.  This can certainly be added back in if you wish. C.  If you notice the sections below, this is how we are limiting which object history gets deleted and which ones do not (Use Case #2).  We take the query provided by the native groom job and add a couple joins to the ManagedType and ManagementPack tables.  This allows us to add criteria to the query to exclude either certain classes/relationships or even every class/relationship in an entire Management Pack (which is what we're doing below).  This can be super helpful to provide custom change log grooming for those MPs that you have written custom for your organization. Groom Change Log Exceptions Image Copy/Paste Mode: DECLARE @RowCount int = 1 DECLARE @TotalRowCount int = 0 DECLARE @RetentionDateTime datetime DECLARE @Command nvarchar(max) DECLARE @Comment nvarchar(max) DECLARE @GroomHistoryId bigint DECLARE @RetentionPeriodInMinutes int = 259200 --6 months DECLARE @BatchSize int = 5000 CREATE TABLE #EntityChangeLogIds ( EntityChangeLogId bigint ); SELECT @RetentionDateTime = DATEADD(mi, -@RetentionPeriodInMinutes, getutcdate()) SET @Command = N'Exec dbo.p_GroomChangeLogs, ' + CAST(@RetentionPeriodInMinutes AS nvarchar(10)) + ', ' + CAST(@BatchSize AS nvarchar(10)) EXEC dbo.p_InternalJobHistoryInsert @Command, @GroomHistoryId OUT WHILE(@RowCount > 0) BEGIN -- Entity Changes INSERT INTO #EntityChangeLogIds SELECT TOP (@BatchSize) EntityChangeLogId FROM dbo.EntityChangeLog E LEFT OUTER JOIN ManagedType M ON E.EntityTypeId = M.ManagedTypeId LEFT OUTER JOIN ManagementPack MP ON M.ManagementPackId = MP.ManagementPackId WHERE E.RelatedEntityId IS NULL AND E.SubscriptionSpecific = 0 AND E.LastModified < @RetentionDateTime AND MP.MPFriendlyName NOT LIKE '%YourMpNameHere%' SET @RowCount = @@ROWCOUNT -- Relationship Changes INSERT INTO #EntityChangeLogIds SELECT TOP (@BatchSize) EntityChangeLogId FROM dbo.EntityChangeLog E LEFT OUTER JOIN RelationshipType R ON E.EntityTypeId = R.RelationshipTypeId LEFT OUTER JOIN ManagementPack MP ON R.ManagementPackId = MP.ManagementPackId WHERE E.RelatedEntityId IS NOT NULL AND E.LastModified < @RetentionDateTime AND MP.MPFriendlyName NOT LIKE '%YourMpNameHere%' SET @RowCount = @RowCount + @@ROWCOUNT -- Delete (p_GroomChangeLogsInternal) DELETE RECL FROM dbo.RelatedEntityChangeLog RECL JOIN #EntityChangeLogIds DEL ON RECL.EntityChangeLogId = DEL.EntityChangeLogId; DELETE ECL FROM dbo.EntityChangeLog ECL JOIN #EntityChangeLogIds DEL ON ECL.EntityChangeLogId = DEL.EntityChangeLogId; DELETE ETL FROM EntityTransactionLog ETL WHERE NOT EXISTS (SELECT 1 FROM EntityChangeLog ECL WHERE ECL.EntityTransactionLogId = ETL.EntityTransactionLogId) AND NOT EXISTS (SELECT 1 FROM RelatedEntityChangeLog RECL WHERE RECL.EntityTransactionLogId = ETL.EntityTransactionLogId) TRUNCATE TABLE #EntityChangeLogIds; SET @TotalRowCount = @TotalRowCount + @RowCount; END SET @Comment = N'EntityChangeLog: ' + CAST(@TotalRowCount AS nvarchar(10)) EXEC dbo.p_InternalJobHistoryUpdate @GroomHistoryId, 1, @Comment And there you have it.  That is one way to limit which object history gets deleted and which do not.  And of course, you can certainly modify this to even more specific criteria depending on your organization's requirements.  I hope this helps. Until the Whole World Hears, Christopher

Author

Christopher Mank

Systems Architect