ACS reports return no more than 42 days old data

Updated on January 17, 2012: Microsoft has released KB article 2663919 “System Center Operations Manager 2007 ACS reports return no more than 42 days of data”  http://support.microsoft.com/kb/2663919

The information below is retrieved from Microsoft support forum:

The ACS collector service uses the “DbDeletePartition.sql” (Path C:\Windows\System32\Security\AdtServer) to delete partition table based on retention period and also create the views AdtServer.dvall, AdtServer.dvall5 and AdtServer.dvheader. In the DbDeletePartition.sql the dvall , dvall5 and dvheader views use union of only the top 42 partition tables. See 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
set @iIsFirst = 1
set @vchStmt = N’create view [AdtServer].dvAll as’
open cPartition
fetch next from cPartition into @vchPartitionId
while @@fetch_status = 0
begin
if @iIsFirst = 0
set @vchStmt =  @vchStmt + N’ union all’
set @vchStmt =  @vchStmt + N’ select * from dvAll_’ + @vchPartitionId
set @iIsFirst = 0
fetch next from cPartition into @vchPartitionId
end
close cPartition
deallocate cPartition
if exists (
select * from dbo.sysobjects where
id = object_id(N'[AdtServer].dvAll’) and
objectproperty(id, N’IsView’) = 1)
drop view [AdtServer].dvAll
exec (@vchStmt)
go
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
set @iIsFirst = 1
set @vchStmt = N’create view [AdtServer].dvAll5 as’
open cPartition
fetch next from cPartition into @vchPartitionId
while @@fetch_status = 0
begin
if @iIsFirst = 0
set @vchStmt =  @vchStmt + N’ union all’
set @vchStmt =  @vchStmt + N’ select * from dvAll5_’ + @vchPartitionId
set @iIsFirst = 0
fetch next from cPartition into @vchPartitionId
end
close cPartition
deallocate cPartition
if exists (
select * from dbo.sysobjects where
id = object_id(N'[AdtServer].dvAll5′) and
objectproperty(id, N’IsView’) = 1)
drop view [AdtServer].dvAll5
exec (@vchStmt)
go
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
set @iIsFirst = 1
set @vchStmt = N’create view [AdtServer].dvHeader as’
open cPartition
fetch next from cPartition into @vchPartitionId
while @@fetch_status = 0
begin
if @iIsFirst = 0
set @vchStmt =  @vchStmt + N’ union all’
set @vchStmt =  @vchStmt + N’ select * from dvHeader_’ + @vchPartitionId
set @iIsFirst = 0
fetch next from cPartition into @vchPartitionId
end
close cPartition
deallocate cPartition
if exists (
select * from dbo.sysobjects where
id = object_id(N'[AdtServer].dvHeader’) and
objectproperty(id, N’IsView’) = 1)
drop view [AdtServer].dvHeader
exec (@vchStmt)
go

As a workaround, you may try to increase this value. For example, change “select top 42 PartitionId from dtPartition order by PartitionCloseTime desc” to “select top 60 PartitionId from dtPartition order by PartitionCloseTime desc” in dvall, dvall5 and dvheader, and you should get 60 days old data in ACS report.

DbCreatepartition.sql (path C:\Windows\System32\Security\AdtServer) is called to create new partition tables, while DbDeletePartition.sql (path C:\Windows\System32\Security\AdtServer) is called to delete old tables that are ready to be groomed out. Please edit both .sql files

Note: SQL 2005 has a limitation and allows only 255 partition tables in a view. Do not set the value more than 255. Also higher the value, it can take longer to fetch the data in the reports and performance may degrade.

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: