Post dated data in your OpsMgr database

About two years ago I posted about the complete lack of input validation in OpsMgr that could lead to all sort of troubles. The post dated data is one I’ve come though several times Post dated data – reprise.

Recently a new KB has been published on the subject "Configuration may not update in System Center Operations Manager 2007". While the author checks for post dated discovery data, he doesn’t check for state data, which is equally troublesome. With this post I try to merge the KB articles and my old findings in a one stop source for post dated data issues.

The first step you need to take is to check if any post dated data is in your database, the following queries are checking for any data more than a day in the future

/*                                                                            */
/*                                                                            */
/*                                                              */
/*           http://support.microsoft.com/kb/2635742                          */
/*                                                                            */
/*                                                                            */  /*  If the timestamp with the greatest value in the table is greater than the current time (in UTC format) you have Post Dated Data.  */
Select GetUTCDate()as 'Current Time',
MAX(TimeGeneratedOfLastSnapshot) as 'DiscoverySource Timestamp' from DiscoverySource  Select GetUTCDate()as 'Current Time',
MAX(timegenerated) as 'DiscoverySourceToTypedManagedEntity Timestamp' from DiscoverySourceToTypedManagedEntity   Select GetUTCDate()as 'Current Time',
MAX(timegenerated) as 'DiscoverySourceToRelationship Timestamp' from DiscoverySourceToRelationship
 
 
/*	The following three queries can be used to determine which COMPUTERS may have      */
/*	submitted discovery data with a future timestamp.                                  */
/*	If the system clocks on these agents are not current, set them to current          */
/*  time before taking any additional action.                                          */
    -- Find all COMPUTERS with DiscoverySource Timestamp more than one day in future --
Select DisplayName, *  from BaseManagedEntity
where BaseManagedEntityID in
 (select BaseManagedEntityId from BaseManagedEntity BME   join DiscoverySource DS on DS.BoundManagedEntityId = BME.BaseManagedEntityId   where DS.TimeGeneratedOfLastSnapshot > DATEADD (d, 1, GETUTCDATE())   and FullName like 'Microsoft.Windows.Computer%')   -- Find all COMPUTERS with DiscoverySourceToTypedManagedEntity Timestamp more than one day in future --
Select DisplayName, *  from BaseManagedEntity
where BaseManagedEntityID in
 (select BaseManagedEntityId from BaseManagedEntity BME   join DiscoverySourceToTypedManagedEntity DSTME on DSTME.TypedManagedEntityId = BME.BaseManagedEntityId   where DSTME.TimeGenerated > DATEADD (d, 1, GETUTCDATE())   and FullName like 'Microsoft.Windows.Computer%')   -- Find all COMPUTERS with DiscoverySourceToRelationship Timestamp more than one day in future --
Select DisplayName, *  from BaseManagedEntity
where BaseManagedEntityID in
  (select BaseManagedEntityId from BaseManagedEntity BME    join DiscoverySource DS on DS.BoundManagedEntityId = BME.BaseManagedEntityId    join DiscoverySourceToRelationship DSR on DSR.DiscoverySourceId = DS.DiscoverySourceId    where DSR.TimeGenerated > DATEADD (d, 1, GETUTCDATE())    and FullName like 'Microsoft.Windows.Computer%')
 
/*                                                                            */
/*                                                                            */
/*                           My Query                                    */
/*                                                                            */
/*                                                                            */
/*                                                                            */  -- We have two tables involved here StateChangeEvent and State.  --
-- The former collects all event state events, the ones you can check in health explorer --
-- The latter reports the last known state for any given managed entity / monitor pair.   --
  /*                  StateChangeEvent Table                                    */  select * from dbo.StateChangeEvent  where TimeGenerated > DATEADD (d, 1, GETUTCDATE())  
 
/*                     State Table                                            */  select ME.FullName, M.MonitorName, State.* from dbo.State with (nolock)  inner join dbo.BaseManagedEntity ME with (nolock) on ME.BaseManagedEntityId=State.BaseManagedEntityId  inner join dbo.Monitor M with (nolock) on M.MonitorId=State.MonitorId  where State.LastModified > DATEADD (d, 1, GETUTCDATE())
 
 
/* Discovery Data */
 
select * from [dbo].[DiscoverySource] d  inner join BaseManagedEntity B  on d.BoundManagedEntityId = B.BaseManagedEntityId
where d.IsDeleted = 0 and B.IsDeleted = 0 and TimeGeneratedOfLastSnapshot > DATEADD (d, 1, GETUTCDATE())

The second step is to get rid of the time difference on agents, if not bad data will continue to flow in.

Third step is to fix the data, once again a marge between KB and my findings:

/*                                                                            */
/*                                                                            */
/*                                                           */
/*           http://support.microsoft.com/kb/2635742                          */
/*                                                                            */
/*                                                                            */  Update DiscoverySource
Set TimeGeneratedOfLastSnapshot = GETUTCDATE()
where TimeGeneratedOfLastSnapshot > GETUTCDATE()      Update DiscoverySourceToTypedManagedEntity  Set TimeGenerated = GETUTCDATE()
where TimeGenerated > GETUTCDATE()       Update DiscoverySourceToRelationship  Set TimeGenerated = GETUTCDATE()
where TimeGenerated > GETUTCDATE()
 
 
/*                                                                            */
/*                                                                            */
/*               From Quae Nocent Docent                        */
/*                                                                            */
/*                                                                            */
/*                                                                            */  update dbo.StateChangeEvent set TimeGenerated=TimeAdded  where TimeGenerated > GETUTCDATE()  update dbo.State set LastModified = GETUTCDATE()  where State.LastModified > GETUTCDATE()
 
- Daniele 

This posting is provided "AS IS" with no warranties, and confers no rights.