Before exploring further uses of the BI platform Audit Data I wanted to begin to answer a common question on the BI Platform Auditing: ‘Why is my audit DB so Large?’
This is a difficult question to answer; there are so many factors (apart from the obvious, how big is too big?). How long has the BI platform auditing been enabled and gathering events? How many auditees are there and how many events are being audited? How ‘busy’ is the BI system, both user interactive activities and system (scheduled) activities?
The other important question to consider is why are you auditing your BI system and what are the questions you are expecting to answer with the BI platform audit data.
All these factors can affect the size of the audit DB and there is no right answer as to the expected size. Only the AUDIT_EVENT and EVENT_DETAIL tables will ever grow significantly. There are a couple of things to check if you believe that the audit database is becoming too large. The first is the amount of details you are auditing.
Each auditable server has a number of events that can be selected, some have more than others. For example here is the WebIntelligenceProcessingServer audit configuration page.
Switch off unwanted events and check on the table growth to assess whether the details you have been logging have been making the audit DB grow too large. Do you really need to capture the SQL generated by document refreshes? Remember to ensure that you adjust the auditing events for all the servers in your deployment.
I usually suggest taking a look at the AUDIT_DETAIL table (this should be the ‘big’ table) and specifically the Detail_Text column. For example Detail_Type_ID of 19 is the SQL value. This is a Detail_Text value that we expect to be large, as typically the SQL generated can be long and complex.
The other Detail_Text that can get large is the ‘user groups’ (Detail_Type_ID of 42). This can be a significant amount of data if the user is in a lot of groups. This is stored for every event.
The next question is usually: ‘Can I reduce the size of my audit database?’
There is no supported way to reduce the size of an already large BI platform audit database. However armed with the knowledge provided in this post and former posts you should be able to figure out some possible solutions that we could theoretically use.
Just to get you started, here is a sample of some SQL that could theoretically be used to remove audit records for a particular date range. The AUDIT_DETAIL table stores no date information, so two queries are needed. The first to delete everything from the AUDIT_DETAIL table where the date is in the AUDIT_EVENT table. The second will remove the records form the AUDIT_EVENT table itself.
delete from audit_detail
where audit_detail.event_id+server_cuid in (
select event_id+server_cuid from audit_event
where convert(char(11),start_timestamp,112) between '20090101' and '20090630')
delete from audit_event where convert(char(11),start_timestamp,112) between '20090101' and '20090630'
Another example would be if you decide that the SQL values are taking up too much space and you would like to remove them. As we know the Detail_Type_ID for the SQL value in the AUDIT_DETAIL table is 19. So it would be simple to write some SQL to remove these audit event detail records.
Of course it may be more appropriate to plan an archiving strategy for your audit database as the deletion of records could be irreversible. Database horizontal partitioning is one option to consider for archiving the audit data. Your exact archiving strategy will depend on your underlying audit database type. Has anyone got an archiving strategy they would like to share? Or other tips for BI platform size optimization?
2 Comments
