Cleaning Audit DB

[The Boss]

Hi, We have a requirement. We are running BOE XI R2.

We want to keep only the last 3 months data in the Audit database. We want to regularly cleanup the data which are more than 3 months old.

Could anybody shed some light on how to acheive this? Your help is much appreciated.

Thanks.

[haider]

Your DBA should be able to help you in this by looking at the tables structure for XIr2 auditing.
There might be a date column in one of the tables on which the data purge can be done.

.

[jsanzone]

The best that I can determine, there is no official documentation from BusinessObjects regarding a method to "trim" the Auditor database. Being that you are on XI R2, then these notes apply in this case. Here is the scoop:

There are six tables used by Auditor:
1) APPLICATION_TYPE (initialized w/ 13 rows, does not "grow")
2) AUDIT_DETAIL (tracks activity at a granular level, grows)
3) AUDIT_EVENT (tracks activity at a granular level, grows)
4) DETAIL_TYPE (initialized w/ 28 rows, does not "grow")
5) EVENT_TYPE (initialized w/ 41 rows, does not "grow")
6) SERVER_PROCESS (initialized w/ 11 rows, does not "grow")

If you simply want to remove all audit data and start over, then truncate AUDIT_EVENT and AUDIT_DETAIL.

If you want to only remove rows based on a period, then consider that the two tables, AUDIT_DETAIL and AUDIT_EVENT, are transactional in nature, however, AUDIT_DETAIL is a child to the parent table AUDIT_EVENT, thus you will want to remove rows from AUDIT_DETAIL based on its link to AUDIT_EVENT before removing rows from AUDIT_EVENT first. Otherwise, rows in AUDIT_DETAIL will get "orphaned" and never be of any use to you, and worse, you will not readily know how to ever delete these rows again.

Here are the SQL statements:

[Nick Daniels]

Is the audit database really taking up that much space? How much is 3 months worth?

[MarkDavisCraig]

Our Auditing database is getting enormous after 18 months of data have been captured. Has anybody established a regular process to purge old data from their audit tables? We were thinking about creating partitions so that we could still query all the data but only extracted the most recent changes.

[bernard timbal]

Very usefull information jsanzone, exactly what we were looking for our audit purge strategy in XI30. Just a remark in the sql purge code. I think, it is a "in" instead of a "="

[jsanzone]

Bernard,

[J029]

I would like to delete some rows (for a time period) from the audit tables but for the BO 6.5 version. Could you please help me? I need the queries...

Thanks!!!

[bernard timbal]