System Center Operations Manager ACS reports return no more than 42 days of data
This article helps you fix an issue in which Audit Collection Services (ACS) reports return no more than 42 days of data in System Center Operations Manager.
Original product version: Microsoft System Center 2012 Operations Manager, System Center 2012 R2 Operations Manager
Original KB number: 2663919
Symptoms
When using System Center Operations Manager, the audit database data retention period is set to 100 days but ACS reports return no more than 42 days of data.
Cause
The ACS collector service uses DbCreatePartition.sql
to create the partition tables and uses 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 steps:
First create an 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. Don't 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.
On the collector server, navigate to
C:\Windows\System32\Security\AdtServer
. Edit theDbCreatePartition.sql
stored procedure in Notepad. Increase the days to 100 (or as per your requirements) by replacing 42 in the fifth line of forAdtServer.dvall
,AdtServer.dvall5
, andAdtServer.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
On the collector server, navigate to
C:\Windows\System32\Security\AdtServer
. Edit theDbDeletePartition.sql
stored procedure in Notepad. Increase the days to 100 (or as per your requirements) by replacing 42 in the fifth line ofAdtServer.dvall
,AdtServer.dvall5
, andAdtServer.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
Restart the Operations Manager Audit Collection Service on the collector server.
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for