Purging Audit Data

other Auditor
1

We are running XI 3.0 and the audit database has reached 5GB. I know the tables that need to be purged (Audit_Event & Audit_Detail). I also have the SQL statements to perform the purge.

My question is does a tool exist within Business Objects or CMC that can be set to keep a set amount of records (i.e. 6 months) or do we need to set up a SQL job to perform this purge?

I have looked through the documentation and also searched through this forum and found nothing that points to any tool.

Lisa

lisakrebs (BOB member since 2010-03-03)

2

You would need to create the tool to do this.

jwhite9 :us: (BOB member since 2006-07-28)

3

I asked business objects about this and they said when the audit database gets too big, log a ticket with them and they’ll walk you through creating a new database. they said they do not support or recommend purging data.

gjwl_8 (BOB member since 2010-02-17)

4

Creating a new audit database does not address the issue of wanting to report on data over 6 months old. Do you create a view merging the old audit database(s) and the current, then modify the audit universe so that you can report off of it? Then you would have to modify this process every 6 months to ensure the active audit database doesn’t get too big? When my audit database gets much over 7 months worth of data, reports running against it become deadlock victims or worse the audit process in BO that writes the records into the database from text files becomes deadlock victim and stops working (until restarting all BO processes). :hb: We would like to be able to trend our BO usage for at least the past 12 months. One alternative I have not had time to try is using (SQL Server) replication to copy the data to a mirror set of tables, then temporarily stopping replication to run reports.

Being able to trend usage is an important part of determining hardware requirements (SAN, Servers, network) for the next fiscal year.

Any BO Guru’s have thoughts/suggestions on this? It would be GREATLY appreciated.

Thank you.

tmcd :us: (BOB member since 2005-10-02)

5

in case anyone is after the code to do this, have a little read of this.

In the Audit database, AUDIT_DETAIL and AUDIT_EVENT are the growing tables. All other tables stay static. You can purge these two tables based on the timestamp of records. I would also recommend you have a complete backup of the Auditing database before doing this.

There are no recommended strategies for purging the Audit database.
Customer Assurance does not support any purging of data from either the
CMS or Audit Databases.

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:

Code:
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.

Note: Please take back up of your Audit database before performing any activity.

I’m told that an enhancement is on the cards, but no date yet

Xavier :ireland: (BOB member since 2006-12-18)

6

Thank you for posting that Xavier! That is exactly how we clear records out of our audit database. We run the process weekly to clear off records older than 7 months (runs 20-30 min). This guarantees our reporting is good for the past 6 months (month to month comparisons) and the BO reports complete (95% of the time) without deadlocking. We would really like to have 2+ years to do annual comparisons. However, the audit table and corresponding audit universe design do not allow that to run successfully without locking/blocking from the BO process that populates those tables.

My thoughts on the replication were to move the data into a database/tables that do not require a universe to have derived tables like the audit universe currently has. This would also avoid the deadlocking with the process that populates the table. A lot of work however for an audit report.

tmcd :us: (BOB member since 2005-10-02)

7

We use the same code, but isn’t that big of an IN LIST very inefficient? Is there some way to do it based on the Join? For example, Truncate the Event table, then keep only Details where there is a matching key in the Event table? I’m assuming the Event_ID and Server_CUID make a unique row. Here’s the join, AUDIT_DETAIL.Event_ID=AUDIT_EVENT.EVENT_ID and AUDIT_DETAIL.Server_CUID=AUDIT_EVENT.SERVER_CUID

Thoughts/suggestions? The other way looks like it won’t perform well and in our data would be 30,000,000 events. Can the DB handle that much al at once? I’m guessing we’ll have to shrink the number of months to keep until it gets back to a manageable amount. We have built up 18 months of data since they let the script lapse.

pcgeekus :us: (BOB member since 2003-10-16)

8

I’ve modified the way we do this process. I’ve created an Activity History universe which points to another server which contains a copy of the audit “History” database. A job runs daily during off-hours to delete data older than 14 days out of the audit database. During the day there is a job that “replicates” the audit Events and Detail information over to the audit “History” database. It only does top 100 Event records at a time then all the corresponding detail records with those 100 records. Take the Max(object id) from history as a starting point.

It runs every 5 minutes (I had it running every 2 minutes but that was overkill for our environment). Running every 5 minutes prevents deadlocks with the BO process that loads the data from .txt files. Having only 14 days worth of data keeps my daily schedule success/failure reports running fast (relatively). When I need to run long reports against audit “History”, I do so during a time (morning) when the replication job is not running.

tmcd :us: (BOB member since 2005-10-02)

9

Thanks for the reply, but our environment writes over 1 million audit records per day. I don’t think your approach is feasable for us.

I have removed some audits to help but don’t know what the % reduction is yet. For example, Generate SQL. Not sure why I’d need those entries. I’d guess even prompts refreshing would add an entry on that one.

pcgeekus :us: (BOB member since 2003-10-16)

10

I think your on the right path with reducing what gets audited. The question to ask is do you need 18 months worth of data. Do you need to perform trending analysis on BO usage? IF so, some sort of replication may be necessary. Perhaps you can partition the database based on startTime (weekly, Monthly)? For us it has been a balancing act between running reports and Business Objects loading the .txt files into the audit database. A million records per day that is probably even more of an issue.

Is there any “downtime” for your BO environment? Perhaps another route you could take is to only keep 1 day’s worth of transaction in the Event and Detail files. Do a “rename Table” and recreate empty Event & Detail tables. It would only take a couple seconds to do this then you load the daily into the “historical” copy using the “historical” copy for long term reporting and the daily for operational. I use SQL Server primarily so I’m not sure if there is rename functionality in all databases…

Another thought: make sure proper indexes are on the event and detail files. I just deleted 1.6 million and inserted 800k records to the event table (25 million rows) and neither one of them took over 15 seconds.

tmcd :us: (BOB member since 2005-10-02)