I have a friend who set his ACS data retention for 2 years to comply to SOX standards which means he needs to set the data retention range to 2 years without an archiving solution at that time.
It has been a year since he has the ACS data collected and he contacted me recently and complaining about the Audit report takes ages to generate. Well it kinda makes sense for the reporting to be slow because of the sheer volume of the data that was collected.
So what's next? Well both of us have been toying around with the ACS DB and managed to find a workaround to do this.
Well as I have shared on my previous posts, the data source which the Audit Reports retrieved from actually points to the adtserver.dvAll5 view and this view will retrieve all the data collected for the past year.
We have 2 ways to do this:
Option 1: Modify the dvAll5 View
- Go to SQL Management Studio, modify the dvAll5 syntax to only include the views that you want to see.
- Execute the SQL syntax
- Note: Although this is simple and straight forward, you need to constantly go into SQL Management Studio to do this and your settings will eventually be overwritten everytime ACS creates a new Partition table because the DBCreatePartition.sql will rebuild the dvAll5 view
Option 2: Modify the DBCreatePartition.sql file
- This file resides in C:\WINDOWS\system32\Security\AdtServer
- Before you start playing with this, make a backup copy of this file.
- Edit this file and look for select top 42 PartitionId from dtPartition order by PartitionCloseTime desc
- Change the Top value to a lower value. For example, if I just want to generate report for the range of 2 weeks, I just need to modify the statement to select top 14 PartitionId from dtPartition order by PartitionCloseTime desc
- Save the SQL file
- What we did just now is to have ACS to only include the 14 latest Partition views to be included into the data source (which is dvAll5) which means the resultset is much smaller hence the performance will be increased.
