Changing SCOM 2007 R2 ACS Data Retention Period

Came across the situation where DBA team was reluctant this morning to change the database field related to ACS database retention period settings without formal lengthy official procedure. As OpsMgr Admin, I have only read-only permission on the ACS database; I couldn’t just update the field, which is good though.

After some researches on the web, from TechNet article: Managing Audit Collection Services in Operations manager 2007, found there is no option available in the OpsMgr console to control the ACS data retention period setting. The only work around is to update the database field directly. Even though, it mentioned on TechNet and other ACS deployment documents, default ACS data-retention period is set to 14, but I found it set as 2 in all my default installations, without any changes done by me during setup. So, I had to update that to 14. ACS keeps each day’s security events in separate tables called partition. ID =6 row of dtConFig table in the ACS database holds the settings corresponding to number of partitions or day’s events database will keep. Default as per the TechNet is 14 days. This means that every days, all database partitions (and their data) that are older than 14 days are dropped or deleted. The official procedure of changing the retention period for the ACS data is:

1. Log on to the computer running SQL Server that hosts the ACS database with an account that has administrative rights to the ACS database.

2. Open the SQL Server Management Studio tool, and connect to the database engine.

3. Expand the Databases folder, and select the OperationsManagerAC database.

4. Right-click to open the context menu and select New Query….

5. Run the following command to the current retention period: Select * from dtConfig

6. update the value, in the Query pane, type the following, where number of days to retain data + 1 equals the number of days you want to pass before data that has aged past that point is deleted. For example, if you want to retain data for 14 days, type 15

USE OperationsManagerAC UPDATE dtConfig SET Value = WHERE Id = 6 and then click the Execute button on the toolbar. This runs the query and then opens the Messages pane, which should read (1 row(s) affected).

7. To view the new setting, delete the previous query text from the Query pane and type SELECT * FROM dtConfig. This opens the Results pane below the Query pane.

8. Look at the value in the sixth row; it should now equal the value you entered for .

8. Restart the Operations Manager Audit Collection Service for this to take effect.

However, as DBA doesn’t ready to change retention period, instantly for me, I started looking alternative way to accomplish this, eureka, there it is, which is almost not mentioned anywhere:

Each Collector has AdtAdmin.exe command to configure ACS related settings and options. This AdtAdmin command is mainly use to define the filter to collect the security events. However, 2 parameters of this command are: -getpartitions and -setpartitions. Which are to show and manipulate the partition settings of the ACS database. –getpartitions is to get the current number of partitions settings and the –setpartitions is to update the desire value on the database according to retention period. The procedure to update the period using the –setpartitions is:

1. Log on to the collector server

2. Open the command prompt (if Windows 2008 or 2008 R2, open with elevated access)

3. Browse to %systemroot%\System32\Security\AdtServer

4. Type the following command:

AdtAdmin.exe –getpartitions

This will show the current number of partitions settings from database

5. Type the following command to update the database:

AdtAdmin –setPartitions –value: (e.g: AdtAdmin –setPartitions –value:14)

6. Type the following command again:

Adtadmin.exe –getpartitions

This will show the updated value

7. Restart “Operations Manager Audit Collection Service (AdtServer)” service on the collector for this to take effect

Finally, I was able to update the ACS database retention period without the direct SQL Server access privilege and bypassing DBA.