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

hotfixHere’s a new Knowledge Base article we published today on SCOM 2007.  This one talks about an issue where ACS reports only return a max of 42 days worth of data:

=====

Symptoms

When using System Center Operations Manager 2007, 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.

=====

For the most current version of this article please see the following:

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

J.C. Hornbeck | System Center Knowledge Engineer

Get the latest System Center news on Facebook and Twitter :

clip_image001 clip_image002

App-V Team blog: https://blogs.technet.com/appv/
AVIcode Team blog: https://blogs.technet.com/b/avicode
ConfigMgr Support Team blog: https://blogs.technet.com/configurationmgr/
DPM Team blog: https://blogs.technet.com/dpm/
MED-V Team blog: https://blogs.technet.com/medv/
OOB Support Team blog: https://blogs.technet.com/oob/
Opalis Team blog: https://blogs.technet.com/opalis
Orchestrator Support Team blog: https://blogs.technet.com/b/orchestrator/
OpsMgr Support Team blog: https://blogs.technet.com/operationsmgr/
SCMDM Support Team blog: https://blogs.technet.com/mdm/
SCVMM Team blog: https://blogs.technet.com/scvmm
Server App-V Team blog: https://blogs.technet.com/b/serverappv
Service Manager Team blog: https://blogs.technet.com/b/servicemanager
System Center Essentials Team blog: https://blogs.technet.com/b/systemcenteressentials
WSUS Support Team blog: https://blogs.technet.com/sus/

The Forefront Server Protection blog: https://blogs.technet.com/b/fss/
The Forefront Identity Manager blog : https://blogs.msdn.com/b/ms-identity-support/
The Forefront TMG blog: https://blogs.technet.com/b/isablog/
The Forefront UAG blog: https://blogs.technet.com/b/edgeaccessblog/