cancel
Showing results for 
Search instead for 
Did you mean: 

Cleaning up IDM audit data

markus_sauer
Explorer
0 Kudos

I am uncertain how to clean up the audit data stored in the database without causing problems/inconsistencies in the datebase tables.

Chapter 5.6.3 of the SAP NW IDM Solution Operation Guide document describes the SQL statements which can be used to clean up old audit data.

But how can I decide which datasets can be removed without causing problems?

As far as I know where are references inbetween the tables MXI_LINK and MXI_LINK_AUDIT which are used in the role/privilege reconcilation process.

So my fear is that removing the wrong datasets, ie. auditdata of PERSONs with roles and privileges will cause deprovisioning or reconcilation problems.

Is there any further documentation about this topic?

A second question concerns the table MXP_Ext_Audit.

Is it correct that this table is not really essential for the operation of the IDM?

According to the documentation this table is filled then  "Enable trace" is activated on the IDM "Option"-Tab in the MMC.

See screenshot:

So this sounds kind of optional...

Am I right that it will do no harm to disable the trace and to TRUNCATE the table MXP_Ext_Audit as long as we are  not using this table by any custom process?

This table is growing very fast and it will slim down the database if we could get rid of it.

BR,

Markus

Accepted Solutions (0)

Answers (2)

Answers (2)

sasi_reddy7
Participant
0 Kudos

Hi Markus

Did you ever get a solution to clean up/archive old Provisioning Audit Queue?

Thanks

Sasi

ChrisPS
Contributor
0 Kudos

Hello Markus,
                     you could cross check against the provisioning queue to check if the auditid
is still there otherwise it will be safe to remove it. As best practice you should archive the
the data if you think it is required for auditing or you want to retain a backup in case you
need to restore for any reason.

I'll check with our documentation team to see if it is possible to provide some clarification
in the guide going forward.

Just for reference (for anyone checking the thread later) the guidelines below

5.6.3 Cleaning up the audit information

The audit tables are used by the provisioning functionality for auditing every provision request andappropriate status. Further this table will link provision tasks together where typically sub tasks isstarted by use of OnOk, OnFail, OnChainOK, OnChainFail.


Remove the entries older than a specific audit ID.


To remove entries with audit ID < 1000000, do as follows:


delete from mxi_link_audit where mcAuditID < 1000000 and mcAuditID <> -1


delete from MXP_Audit_Variables where auditID < 1000000


delete from MXP_Ext_Audit where aud_ref < 1000000


delete from mxp_audit where auditid < 1000000


The tables MXP_Audit_Variables and MXP_Ext_Audit has audit ID columns referring to


MXP_Audit.auditid, so the entries in these tables must be done before cleaning up the mxp_audit


table.

Regards,

Chris

SAP Active Global Support 

markus_sauer
Explorer
0 Kudos

Hi Chris!

One main reason why I am quite hesitant about deleting audit-data is the stored procedure

mxi_Impl_provision.

There the tables mxi_link and mxp_audit are joined in an SQL statement which is used to decide wether it is necessary to start the deprovisioning task or not. (see line 90 of the stored Proc.)

the statement is like

SELECT count(mcUniqueID) FROM mxi_link LEFT OUTER JOIN mxp_audit on mxi_link.mcProvAudit = mxp_audit.AuditID WHERE ....

If I am not wrong, this statement will deliver a result != 0, if the value stored in mxi_link.mcProvAudit is not found in mxp_audit.AuditID.

And if the result  is != 0 no Deprovisioning Task will be started if the privilege is removed from the user.

So I believe it might be dangerous just to delete old auditIDs as described in the operation guide.

Maybe the rules for deleting Audit data should be more like this:

  • AuditIDs can be deleted savely, if the concerned attribut is not MXREF_MX_PRIVILEGE.
  • AuditIDs concerning MXREF_MX_PRIVILEGE should only be deleted IF the mclinkState in mxi_link is 2, i.e. the privilege is already removed from the user.

And last but not least ...

What about MXP_Ext_Audit? Am I right about my assumption that this table is not important at all and connected to the "Enable trace" Option in MMC?

BR,

Markus


markus_sauer
Explorer
0 Kudos

Hi!

Still waiting 🙂

Markus

ChrisPS
Contributor
0 Kudos

Hi Markus,
                can you open an incident via the Service Marketplace and we can check the issue via
this channel.

Regards,

Chris


SAP AGS Support

siarhei_pisarenka3
Active Contributor
0 Kudos

>>And last but not least ...

What about MXP_Ext_Audit? Am I right about my assumption that this table is not important at all and connected to the "Enable trace" Option in MMC?

I think so as well. The MXP_Ext_Audit is not important, enabled by the "Enable trace" option. It could be cleaned up safely.

Former Member
0 Kudos

Ext_audit can be cleaned safely and is not used by the procedures, but you will loose the details of the task execution (all details from the subtasks from the root task will be missing).

I would be very careful around the mxp_audit table though, and atleast limit the deletion to entries older than X amount of years that are not related to currently active assignments. You will also loose the ability to trace an (historic) attribute change to an event that is now missing in the audit.

Any particular reason or need for the cleanup?

Br,

Per Christian