04 May 2009

What if you want to keep ACS data for more than 256 days ? - Part 3

In my previous posts, I have shared a little on the data structure of ACS database and what are the potential issues that we might faced if we do not adhere to best practice such as the 256 table limitation of SQL Server. That being said, many organizations has a requirement to keep their security events/logs up to a certain period of time to comply with a certain standards/compliance.

This is the dilemma which my friend is currently facing now realizing that he will potentially lose the ability to provide Security Audit reports for long term data (which is longer than 256 day). But after some study which we did, we managed to find a workaround for that though this requires alot of manual work and tweaking of the a couple of SQL scripts in ACS.

But before we go into details, just as a disclaimer you better make sure that you have properly and adequately backed up your entire OpsMgr infrastructure before you embark on this "perilous" adventure.

Well to start things with let us recap on the objective: We need to be able to generate Audit reports for up to 2 years worth of data.
  1. So the first thing is to set the retention range for ACS from 14 days (by default) to the range that you need and let ACS do the rest in terms of collecting data and storing to the ACS database
  2. To generate report for the range which is older than 256 day,go to SQL Server Management Studio and identify the partition tables IDs that stores the collected events/data for the date range from dtPartition table using the following SQL statement. SELECT * FROM dtPartition ORDER BY partition time
  3. Now this is the interesting part. Once you have identified the Partition Table IDs, open the dvAll5 view and edit the syntax and include the partition views into the syntax. For example, if I have identified 2 partition tables with the id of 12345_abcde and 67890_fghij as mentioned in Step 2 the modified syntax for the dvAll5 view will look something like this: ALTER VIEW [AdtServer].[dvAll5] AS SELECT * FROM dvAll5_12345_abcde UNION ALL SELECT * FROM dvAll5_67890_fhgij
  4. Execute the syntax and now the dvAll5 view which is the data source for reporting will retrieve the collected events/data that falls within your date range
Of course the steps above is rather tedious for some and quite complex for a few more.
Well, guess what? There is a tool out there that can do whatever I mentioned above easily and reliably.

The solution which can solve an administrator headache to manage long term ACS data is the Audit Collection Archiver brought to you by Secure Vantage.

This solution will archive your ACS data into a series of compressed binary files and provide you the ability to generate reports on the archived data.

If you would like to know more about this cool solution, have a look on this link