SCSM Add-on - Advanced History Groomer [w/ Source Code]

Conner_Wood

Hi all, I felt it would be beneficial to post on the Cireson Community Forums about the SCSM 2012 R2 - Advanced History Groomer Add-on.

Personally I think Cireson could benefit by looking at the source code provided.  Since we heavily rely on Cireson, we also heavily depend on their success.  Notably, there is code for retrieving SCSM Run As Account credentials for Impersonation.

As for other SCSM 2012 R2 Developers, if you are interested in creating your own C# Workflows using Visual Studio Community, this working example will be of great interest.

Adam_Dzyacky

Highest of fives @connor_wood !

Conner_Wood

For those who want to manually purge history from the SCSM database (in case workflows have too much to handle), I figure you could use this SQL script which is what the Advanced History Groomer Add-on uses:

USE ServiceManager;

-- HardwareAsset (Cireson.AssetManagement.HardwareAsset) == MTV_Cireson$AssetManagement$HardwareAsset == C0C58E7F-7865-55CC-4600-753305B9BE64
-- SoftwareAsset (Cireson.AssetManagement.SoftwareAsset) == MTV_Cireson$AssetManagement$SoftwareAsset == 81E3DA4F-E41C-311E-5B05-3CA779D030DB
-- Organization (Cireson.AssetManagement.Organization) == MTV_Cireson$AssetManagement$Organization == ED0D8659-FBA9-6E08-C213-5CD88F5480A8
-- CostCenter (Cireson.AssetManagement.CostCenter) == MTV_Cireson$AssetManagement$CostCenter == 128BDB2D-F5BD-F8B6-440E-E3F7D8AB4858

DECLARE @ClassInstancesViewName varchar(max) = 'MTV_Cireson$AssetManagement$CostCenter';
DECLARE @MPClassID varchar(max) = '128BDB2D-F5BD-F8B6-440E-E3F7D8AB4858'; -- BE SURE YOU SPECIFY THE CORRECT CLASS, OR IT WILL ERASE ALL HISTORY FROM TABLE ITEMS.
DECLARE @RecentHistoryEntriesToKeep INT = 1;
DECLARE @RetentionPeriodInMinutes INT = 2880;
DECLARE @BatchSize INT = 2000;


---
--SCRIPT BELOW THAT PURGES THE HISTORY AS DEFINED ABOVE.
---
DECLARE @RetentionDateTimeUTC DateTime = DATEADD(minute, <b>-</b>@RetentionPeriodInMinutes, GETUTCDATE()); --Calculated Retention Date, any record older should be purged!
DECLARE @ItemsToGroomTable table (BaseManagedEntityId uniqueidentifier);  --local table to mesh all the dynamic table finding stuff together as securely as possible

    DECLARE @sqlCommand varchar(1000);
    SET @sqlCommand = 'SELECT [BaseManagedEntityId] FROM [dbo].' + QUOTENAME(@ClassInstancesViewName);

    DECLARE @SubscriptionWatermark bigint = 0;     
    SELECT @SubscriptionWatermark = dbo.fn_GetEntityChangeLogGroomingWatermark();

    INSERT INTO @ItemsToGroomTable
    EXEC (@sqlCommand);
                                                            
    DECLARE @LastErr int;
    DECLARE @RowCount int = 1;
    DECLARE @TotalRowCount int = 0;

    IF (OBJECT_ID('tempdb.dbo.#EntityChangeLogIds', 'U') IS NOT NULL) DROP TABLE #EntityChangeLogIds;
    --This creates a temp table for our specific connection, we execute this code and close our connection and the table goes away.
    CREATE TABLE #EntityChangeLogIds
    (
        EntityChangeLogId bigint
    );

    INSERT INTO #EntityChangeLogIds
    SELECT ECL.EntityChangeLogId
    FROM
    (
	    SELECT ECL.EntityChangeLogId, 
		    ECL.EntityId,
		    ECL.EntityTypeId,
		    ECL.RelatedEntityId,
		    ECL.SubscriptionSpecific,
		    ECL.LastModified,
		    ROW_NUMBER() OVER (PARTITION BY ECL.EntityId, ECL.EntityTypeId ORDER BY ECL.EntityChangeLogId DESC) AS RowNum
	    FROM @ItemsToGroomTable ItemsToGroomTable

	    JOIN [dbo].[EntityChangeLog] ECL (nolock)
	    ON ECL.EntityId = ItemsToGroomTable.BaseManagedEntityId

    ) AS ECL
    WHERE 
    (
	    LastModified < @RetentionDateTimeUTC
    ) 
    AND
    (
        (
	    RelatedEntityId IS NULL
	    AND (ECL.RowNum > @RecentHistoryEntriesToKeep OR EntityTypeId <> @MPClassID) -- Ensures we keep latest history entry log of the object itself
        )
        OR 
        (
	    RelatedEntityId IS NOT NULL
        )
    )
    ORDER BY ECL.EntityChangeLogId ASC;

    --We are no longer using this table so remove data.
    DELETE FROM @ItemsToGroomTable;

    WHILE(@RowCount <> 0)
    BEGIN
	    --It's not a temporary table because we only need it for this loop.
	    DECLARE @EntityChangeLogId_Batch table (EntityChangeLogId bigint);

	    --Now we have our temp table to go through in batches.
	    INSERT INTO @EntityChangeLogId_Batch
	    SELECT TOP (@BatchSize) EntityChangeLogId FROM #EntityChangeLogIds ORDER BY EntityChangeLogId ASC;

	    SELECT @LastErr = @@ERROR, @RowCount = @@ROWCOUNT;
	        IF(@LastErr <> 0)
            GOTO Err;

	    SET @TotalRowCount = @TotalRowCount + @RowCount;

	    --NOW DELETE ENTRIES FROM RECL THAT MATCH FROM @EntityChangeLogId_Batch
	    DELETE RECL FROM [dbo].[RelatedEntityChangeLog] RECL 
	    JOIN @EntityChangeLogId_Batch DEL ON RECL.EntityChangeLogId = DEL.EntityChangeLogId; 

	    SELECT @LastErr = @@ERROR;
	        IF(@LastErr <> 0)
            GOTO Err;

	    --NOW DELETE ENTRIES FROM ECL THAT MATCH FROM @EntityChangeLogId_Batch
	    DELETE ECL FROM [dbo].[EntityChangeLog] ECL 
	    JOIN @EntityChangeLogId_Batch DEL ON ECL.EntityChangeLogId = DEL.EntityChangeLogId; 

	    SELECT @LastErr = @@ERROR;
	        IF(@LastErr <> 0)
            GOTO Err;

	    --THIS IS A CLEANUP ON ETL (IT WAS POSSIBLE TO GET A CONSTRAINT ERROR 547 DUE TO THE DESIGN OF HOW SCSM WORKS. Using the watermark for criteria will prevent the error)
	    DELETE ETL FROM [dbo].[EntityTransactionLog] ETL
	    WHERE NOT EXISTS (SELECT 1 FROM [dbo].[EntityChangeLog] ECL WHERE ECL.EntityTransactionLogId = ETL.EntityTransactionLogId) 
	    AND NOT EXISTS (SELECT 1 FROM [dbo].[RelatedEntityChangeLog] RECL WHERE RECL.EntityTransactionLogId = ETL.EntityTransactionLogId)
        AND ETL.EntityTransactionLogId < @SubscriptionWatermark;

	    SELECT @LastErr = @@ERROR;
	        IF(@LastErr <> 0)
            GOTO Err;

	    --Now remove the EntityChangeLogId rows from #EntityChangeLogIds that we've gone through with @EntityChangeLogId_Batch.
	    DELETE FROM #EntityChangeLogIds
	    WHERE EntityChangeLogId IN 
	    (
	        SELECT EntityChangeLogId
	        FROM @EntityChangeLogId_Batch 
	    );

	    SELECT @LastErr = @@ERROR;
	        IF(@LastErr <> 0)
            GOTO Err;

        DELETE FROM @EntityChangeLogId_Batch;

    END

    SELECT @TotalRowCount;
    RETURN;

    Err:
    BEGIN
	    SELECT -1;
	    RETURN;
    END

Note:  If you have the class GUID, you can get what the viewname is by executing the following SQL Stored Procedure:

exec [dbo].[p_GetTableAndViewName] 'ED0D8659-FBA9-6E08-C213-5CD88F5480A8'

I recommend using SCSM Entity Explorer to get the class GUID.