on 04-03-2014 4:57 PM
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
Hi Markus
Did you ever get a solution to clean up/archive old Provisioning Audit Queue?
Thanks
Sasi
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:
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
>>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.
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
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.