Service Manager Change Log Grooming – Part 1: Deep Dive

Author by Christopher Mank

As you know, Service Manager natively tracks every property and relationship change on every object in the CMDB.  This information is visible in the console via the History tab.  Although there are some forms that do not show this tab (e.g. User), this information is being tracked in the database. SCSM History Tab Image In conjunction with this logging, there exists groom jobs that assist in keeping the database clean to prevent it from growing out of hand.  To get a better idea of all the groom jobs that exist in Service Manager, check out Travis' blog on grooming (found here). The groom job we'll focus on in this blog series is the Groom Change Logs workflow.  As it mentions in the above post, this rule grooms the object history logs that keep track of every property and relationship change of many objects in the database.  It runs every day at 2:00 in the morning on the management server. So what exactly does this rule do?  When this rule is triggered, it executes a SQL Stored Procedure (SP) called p_GroomChangeLogs.  To better understand what this SP does, let's take a deep dive of the logic. Parameters The SP takes in 4 parameters (TargetId, RetentionPeriodInMinutes, GroomingCriteria, and BatchSize).  These settings are stored in the MT_GroomingConfiguration table.  Let's look at each one. TargetId:  The Id passed is always the same.  It is the ManagedTypeId of the System.Entity class.  This means that the grooming applies to every object in the CMDB, since every object inherits from System.Entity. RetentionPeriodInMinutes:  This is the data retention settings that tells the groom job when it should purge a particular log entry.  The default is 365 days.  This setting is configurable via the console found in Administration --> Settings --> Data Retention Settings.  Days are converted to minutes for this groom job. History Retention Time Image GroomingCriteria:  This value is used in other scripts as a mechanism to pass in SQL code that is used later on in the SP.  An example of this is in the p_GroomManagedEntity SP.  For our example, however, this is not used. BatchSize:  This value indicates how many entries are deleted in a given cycle.  By breaking up the SP in this way, it helps to control the load on the SQL server.  The default is 1000. Parameters Image Internal Job History Insert The next code block declares some variables to be used later in the SP.  The main piece of this section is highlighted in yellow.  Simply what this command does is call another SP that logs a record in the InternalJobHistory table that this job was started. Internal Job History Insert Image Set Variables In this code block, the SP creates a temp table to store the EntityChangeLogIds of those records that need to be deleted.  It is also running a SQL function to determine the last processed watermark (more information about watermarks can be found here).  The last thing it does is set the retention datetime based on the retention period set in the console. Set Variables Image Entity Changes This next code block is the meat of the groom job.  The green commented area does a great job of explaining what is going on.  The one area that is worth noting is highlighted in yellow.  This portion of the code is adding a row number to every change of an object.  The reason for that is the groom job does not delete the most recent change of any object.  If you have a lot of objects in your CMDB, there is potential this job will take much longer than the default 30 minutes to run.  If this sounds like you, stay tuned for Part 2! Entity Changes Image Relationship Changes This code block is very similar to the Entity changes above, but it relates just to relationship changes.  The green commented area does a great job of explaining what is going on. Relationship Changes Image Groom Change Logs Internal This code block runs another SP that takes all of the EntityChangeLogIds added to the temp table and deletes them from the EntityChangeLog, EntityTransactionLog and RelatedEntityChangeLog tables. Groom Change Logs Internal Image Internal Job History Update This last code block updates the InternalJobHistory table with the status, whether success or failure. Internal Job History Update Image I really hope this post has been helpful.  Now that you know how the out-of-box groom job functions, the next post will show you how to create your own custom Change Log grooming and will present some of the use cases on why you might want to do that. Stay Tuned! Part 1: Deep Dive (this post) Part 2: Custom Grooming Until the Whole World Hears, Christopher

Christopher Mank

Systems Architect