System Center Operations Manager ACS reports return no more than 42 days of data

Article ID: 2663919 - View products that this article applies to.
Expand all | Collapse all

SYMPTOMS

When using System Center Operations Manager, the audit database data retention period is set to 100 days but Audit Collection Services (ACS) reports return no more than 42 days data.

CAUSE

The ACS collector service uses DbCreatePartition.sql to create the partition tables and DbDeletePartition.sql (C:\Windows\System32\Security\AdtServer on the Collector server) to delete the partition tables based on the retention period. It also creates the views AdtServer.dvall, AdtServer.dvall5 and AdtServer.dvheader. In DbCreatePartition.sql and DbDeletePartition.sql, the dvall , dvall5 and dvheader views use a union of only the top 42 partition tables.

RESOLUTION

To resolve this issue, complete the following:

1. First create a ACS data retention period as per your requirements. To update the data retention period, run the following SQL query:

USE OperationsManagerAC
UPDATE dtConfig
SET Value = <number of days to retain data + 1>
WHERE Id = 6

For example, to retain 7 days of data set <Value> = 8. Data is accumulated at approximately 7.6 MB per day per workstation.

NOTE Microsoft SQL Server 2005 has a limitation that allows only 255 partition tables in a view. Do not set the value more than 255. The higher the value, the longer it can take to fetch the data in the reports, thus performance may be affected.

2. On the Collector Server, navigate to C:\Windows\System32\Security\AdtServer. Edit the DbCreatePartition.sql SQL stored procedure in Notepad. Increase the days to 100 (or as per your requirements) by replacing 42 in the 5th line of for AdtServer.dvall, AdtServer.dvall5 and AdtServer.dvheader as shown below:


/*************************************************
*
* Create or update dvAll, the view across all partition views
*
**************************************************/

declare @iIsFirst int
declare @vchStmt nvarchar(max)
declare @vchPartitionId nchar(36)
declare cPartition cursor for
select top 42 PartitionId from dtPartition order by PartitionCloseTime desc 


/***************************************************
*
* Create or update dvAll5, the view across all partition views limited to the first 5 strings
*
****************************************************/

declare @iIsFirst int
declare @vchStmt nvarchar(max)
declare @vchPartitionId nchar(36)
declare cPartition cursor for
select top 42 PartitionId from dtPartition order by PartitionCloseTime desc


/****************************************************
*
* Create or update dvHeader, the view across all partition views with no dtstring joins
*
*****************************************************/

declare @iIsFirst int
declare @vchStmt nvarchar(max)
declare @vchPartitionId nchar(36)
declare cPartition cursor for
select top 42 PartitionId from dtPartition order by PartitionCloseTime desc

3. On the Collector Server, navigate to C:\Windows\System32\Security\AdtServer. Edit the DbDeletePartition.sql SQL stored procedure in Notepad. Increase the days to 100 (or as per your requirements) by replacing 42 in the 5th line of AdtServer.dvall, AdtServer.dvall5 and AdtServer.dvheader as shown below:


/*****************************************************
*
* Create or update dvAll, the view across all partition views
*
******************************************************/

declare @iIsFirst int
declare @vchStmt nvarchar(max)
declare @vchPartitionId nchar(36)
declare cPartition cursor for
select top 42 PartitionId from dtPartition order by PartitionCloseTime desc

 
/******************************************************
*
* Create or update dvAll5, the view across all partition views limited to the first 5 strings
*
*******************************************************/

declare @iIsFirst int
declare @vchStmt nvarchar(max)
declare @vchPartitionId nchar(36)
declare cPartition cursor for
select top 42 PartitionId from dtPartition order by PartitionCloseTime desc

 
/******************************************************
*
* Create or update dvHeader, the view across all partition views with no dtstring joins
*
*******************************************************/

declare @iIsFirst int
declare @vchStmt nvarchar(max)
declare @vchPartitionId nchar(36)
declare cPartition cursor for
select top 42 PartitionId from dtPartition order by PartitionCloseTime desc

4. Restart the “Operations Manager Audit Collection Service” on the Collector Server.
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 2663919 - Last Review: July 9, 2012 - Revision: 4.0
APPLIES TO
  • Microsoft System Center Operations Manager 2007
  • Microsoft System Center Operations Manager 2007 R2
  • Microsoft System Center Operations Manager 2007 Service Pack 1
  • Microsoft System Center 2012 Operations Manager
Keywords: 
kbtshoot KB2663919

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com