Most interactions with auditing are through the auditing database. In this post I’ll give a brief overview of the tables used and their content. With this knowledge reporting from the audit data using your favorite BI tool should be a little easier.
The following diagram shows the six tables in the XI3.x auditing Database.
If you set up auditing when you installed SAP BusinessObjects Enterprise, your auditing database is already configured and has established a connection with the Central Management Server (CMS). If you did not set up auditing when you installed, you must configure all your SIA to connect to an auditing database.
While the audit database can exist as part of the system database (CMS database), I strongly recommended that it be kept separate, to minimize the impact on system performance due to the constantly growing size of the audit database. If you choose, you can use different database software for the CMS system database and the auditing database, and you can maintain these databases on separate servers.
As we already know the basic element of auditing is the event; different auditees and different workflows generate events. These events are stored in the AUDIT_EVENT table, one record for each event, this is the coarsest granularity of auditing. Each event can also have a number of additional details associated with it, and these are stored in the AUDIT_DETAIL table. The join between these tables is a complex join on both the Event_ID and Server_CUID.
It’s as simple as that. The DETAIL_TYPE and the EVENT_TYPE are both lookup tables for the audit detail event description and the event descriptions respectively and join on the ID’s.
So based on this information, using my favorite BI tool I can query the audit DB and take a look at a typical event:-
We can see the Event_ID, User_Name and Start_Timestamp from the AUDIT_EVENT table. The Event _Type_Description from the EVENT_TYPE table and the Detail_Text and Detail_Type_Description from the AUDIT_DETAIL and DETAIL_TYPE tables respectively. It can also be seen that each event can generate a number of details, and that some of these can potentially be quite large (the SQL value for example). This is one of the reasons the audit database can grow significantly and is a topic I will cover in a later post.
In my next post we will cover the final couple of tables in the audit DB.