Home Service Manager

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

Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
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.

Comments

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    Highest of fives @connor_wood !
  • Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
    edited February 2017
    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 &lt; @RetentionDateTimeUTC
        ) 
        AND
        (
            (
    	    RelatedEntityId IS NULL
    	    AND (ECL.RowNum &gt; @RecentHistoryEntriesToKeep OR EntityTypeId &lt;&gt; @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 &lt;&gt; 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 &lt;&gt; 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 &lt;&gt; 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 &lt;&gt; 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 &lt; @SubscriptionWatermark;
    
    	    SELECT @LastErr = @@ERROR;
    	        IF(@LastErr &lt;&gt; 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 &lt;&gt; 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.

Sign In or Register to comment.