How do I purge reconciliation audit logs in IDM (All versions)?

Overview

The autoPurgeAuditRecon.js file (located in the /path/to/idm/bin/defaults/script/audit directory) calls an openidm.action against the repo/audit/recon endpoint; this then executes the "purge-by-..." queries in the repo.jdbc.json file (located in the /path/to/idm/conf directory).

The autoPurgeAuditRecon.js file and the corresponding "purge-by-..." queries in the repo.jdbc.json file can be customized to purge reconciliation audit logs as required. You can add new commands or queries to limit the amount of data that is deleted in one operation, for example:

• You could set up queries that pass in the reconid as a parameter and then delete only entries from that reconciliation.
• You could add LIMIT or FETCH FIRST n ROWS ONLY to the defined query to provide a limit.

Limiting the amount of data deleted in one operation is particularly relevant if you have a lot of directories or input sources, and frequent reconciliation runs since this scenario can cause the auditrecon and auditactivity tables to grow rapidly. The default purge process does a simple delete against the auditrecon table, which can consume large amounts of database memory and log space when this table is large. Additionally, a delete operation with many rows to be deleted can lock the database and prevent IDM from functioning properly as threads can stall when trying to perform audit logging.

The example provided below using a stored procedure will allow you to purge those tables much quicker, as it purges records in increments with a database commit; this makes it much less intrusive to your database server.

Known issues with the IBM® DB2 repository

If you use the IBM DB2 repository, you need to make the following changes to the repo.jdbc.json file for compatibility with DB2:

• Remove the following "purge-by-recon-number-of" query: "purge-by-recon-number-of" : "set @num := 0, @mapping := ''; DELETE r FROM ${_dbSchema}.${_table} r INNER JOIN ( SELECT reconId, mapping, activitydate, @num := if(@mapping = mapping, @num + 1, 1) AS row_number, @mapping := mapping as m FROM ${_dbSchema}.${_table} WHERE mapping LIKE ${includeMapping} AND mapping NOT LIKE ${excludeMapping} AND entryType = 'summary' ORDER BY mapping, activitydate desc ) AS x ON r.reconId = x.reconId WHERE x.row_number > ${numberOf}"
• Ensure the following purge-by-recon-expired query exists and add if not: "purge-by-recon-expired" : "DELETE r FROM ${_dbSchema}.${_table} r INNER JOIN (SELECT reconId FROM ${_dbSchema}.${_table} WHERE mapping LIKE ${includeMapping} AND mapping NOT LIKE ${excludeMapping} AND activitydate < ${timestamp} AND entryType = 'summary') AS x ON x.reconId = r.reconId"

Example

This example process demonstrates deleting records from the auditrecon and auditactivity tables in batches of 10,000 records using a stored procedure. A stored procedure is one possible approach, but you could use a different method depending on your setup. The stored procedure commands given in this example are specific to the Microsoft® SQL Server; if you use a different database for your repository, you will need to adjust these commands accordingly.

To delete records in batches of 10,000:

1. Create a stored procedure with the following commands. USE [openidm] GO /****** Object: StoredProcedure [dbo].[sp_audit_purge] Script Date: 12/14/2016 8:02:32 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE OR ALTER PROC [dbo].[sp_audit_purge] (@includeMapping nvarchar(511) = NULL, @excludeMapping nvarchar(511) = NULL, @purgeDate datetime, @batchsize int = 10000, @maxloops int = 1000) AS SET NOCOUNT ON DECLARE @run_count int = 0 DECLARE @error int = 0 DECLARE @rc int = 1 DECLARE @where varchar(500), @sql varchar(1000) Declare @purgeDate_char nvarchar(29) Set @purgeDate_char = CONVERT(nvarchar(29), @purgeDate, 127) WHILE @rc <> 0 and @run_count < @maxloops BEGIN BEGIN TRAN Set @where = 'Where activitydate < ''' + @purgeDate_char + '''' If @includeMapping is not NULL Set @where = @where + ' and mapping LIKE ''' + @includeMapping + '''' If @excludeMapping is not NULL Set @where = @where + ' and mapping NOT LIKE ''' + @excludeMapping + '''' Set @sql = 'DELETE TOP (' + convert(varchar(10), @batchsize) + ') FROM openidm.auditrecon ' + @where --Print @sql Exec (@sql) SELECT @rc = @@ROWCOUNT, @error = @@ERROR IF @error <> 0 GOTO EXIT_PROC COMMIT TRAN SET @run_count += 1 END set @run_count = 0 set @rc = 1 WHILE @rc <> 0 and @run_count < @maxloops BEGIN BEGIN TRAN DELETE TOP (@batchsize) FROM openidm.auditactivity WHERE activitydate < @purgeDate_char SELECT @rc = @@ROWCOUNT, @error = @@ERROR IF @error <> 0 GOTO EXIT_PROC COMMIT TRAN set @run_count += 1 END RETURN EXIT_PROC: ROLLBACK TRAN RETURN GO
2. Update the "purge-by-recon-expired" script in the repo.jdbc.json file to invoke this stored procedure. The default "purge-by-recon-expired" script is: "purge-by-recon-expired" : "DELETE FROM audit_recon WHERE mapping LIKE ${includeMapping} AND mapping NOT LIKE ${excludeMapping} AND timestamp < ${timestamp}",You should replace it with the following version: "purge-by-recon-expired" : "EXECUTE sp_audit_purge ${includeMapping}, ${excludeMapping}, ${timestamp}"
3. Set up a schedule for the auto purge based on the sample file (schedule-autoPurgeAuditRecon.json which is located in the /path/to/idm/samples/example-configurations/schedules directory. You should use the purgeByExpired purgeType, for example: { "enabled" : true, "type" : "cron", "schedule" : "0 0 */12 * * ?", "persisted" : true, "misfirePolicy" : "doNothing", "invokeService" : "script", "invokeContext" : { "script" : { "type" : "text/javascript", "file" : "audit/autoPurgeAuditRecon.js", "input" : { "mappings" : [ "%" ], "purgeType" : "purgeByExpired", "numOfRecons" : 1, "intervalUnit" : "minutes", "intervalValue" : 1 } } } }

See Also

How do I add logging to JavaScript files in IDM (All versions)?

How do I add logging to Groovy scripts in IDM (All versions)?

Purge Obsolete Audit Information

Audit

Related Training

N/A

Related Issue Tracker IDs

N/A