Cleaning Audit DB

other Auditor
1

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.

The Boss :india: (BOB member since 2008-02-08)

2

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.

.

haider :es: (BOB member since 2005-07-18)

3

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:

delete from AUDIT_DETAIL
where event_id =(select Event_ID from AUDIT_EVENT 
                 where Start_Timestamp between '1/1/2006' and '12/31/2006')
go
delete from AUDIT_EVENT 
                 where Start_Timestamp between '1/1/2006' and '12/31/2006'
go

One word of caution is to down your BOE application before doing this maintenance work, otherwise there is a possibility that Auditor will be busy trying to bring new rows to your database while you’re busy deleting rows and you might encounter an unwanted table lock, either on the work you’re doing or the work that BOE is trying to perform.

Good luck!

jsanzone :us: (BOB member since 2006-09-12)

4

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

Nick Daniels :uk: (BOB member since 2002-08-15)

5

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.

MarkDavisCraig (BOB member since 2006-02-15)

6

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 “=”

select * from AUDIT_DETAIL where event_id IN (select Event_ID from AUDIT_EVENT where Start_Timestamp between '01/01/2008' and '31/12/2008')

bernard timbal :fr: (BOB member since 2003-05-26)

7

Bernard,

:oops:
Yep, you’re right, thank you for noting the needed correction.
Good to go.
John

jsanzone :us: (BOB member since 2006-09-12)

8

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!!!

J029 (BOB member since 2005-09-27)

9 

DELETE FROM OBJ_A_EVENT_DETL DT WHERE DT.A_EVDET_N_ID IN (SELECT LG.A_EVLOG_N_ID FROM OBJ_A_EVENT_LOG LG WHERE LG.A_EVLOG_D_STARTIME < ADD_MONTHS(SYSDATE, -15));
DELETE FROM OBJ_A_EVENT_LOG LG WHERE LG.A_EVLOG_D_STARTIME < ADD_MONTHS(SYSDATE, -15);
DELETE FROM OBJ_A_SITE_LOG WHERE OBJ_A_SITE_LOG.A_STLOG_D_LOGTIME < ADD_MONTHS(SYSDATE, -15);

Here the code we use in production to have a 15 rolling months period regarding the oracle sysdate

Regards

bernard timbal :fr: (BOB member since 2003-05-26)