Client Management Suite

I'm having an issue regarding File Detail inventory.

I don't know one of two things either my exclusion list is not working and inventory is being gathered on a location I have specifically excluded or

purging of file detail inventory data is not properly running.

So I would like to know how to determine the date a specific record was written regarding an inventory collection.  If I go to the run a sql statement on this data I don't see where any date data shows for a specific record.  This makes it very hard to determine exactly when a record was collected and where my actually is.  I believe it is in the purging but would like to put this to rest.  Also this type of data would also be very valuable for reporting.

There must be a way because if there was not how could they do purging based on time?

If I run:

SELECT DISTINCT *

FROM dbo.Inv_Installed_File_Details fd

where

('AcroRd32.dll'='%' or LOWER (fd.Name) LIKE LOWER ('AcroRd32.dll'))

AND ('%\Program Files%\Adobe\Reader%\Reader'='%' or LOWER (fd.Path) LIKE LOWER ('%\Program Files%\Adobe\Reader%\Reader'))

AND ('%'='%' or LOWER (ISNULL (fd.ModifiedTime,'')) LIKE LOWER ('%'))

Order by ModifiedTime

The only date related field I get is the ModifiedTime which is the modified date of the File not the modified or created date of the record itself.

Is what I'm trying to get impossible?  I thought there were some default SQL columns in every table containing SQL created date and SQL modified date.  Are these hidden or am I incorrect in that assumption.

We stored created date and modified date for inventory and events per resesource on the database.

However this is not done on each dataclass table. It is done centrally on the ResourceUpdateSummary table.

This table stores the created date (if I'm not mistaken - I can't verify now), the last inventory received date and the last modified date (we don't write the inventory if the data received via NSE matches the content of the data table).

Here's the table definition:

IF NOT EXISTS (SELECT 1 FROM sysobjects so WHERE so.name='ResourceUpdateSummary' AND so.type = 'U')
BEGIN
	CREATE TABLE [dbo].[ResourceUpdateSummary] (
		[InventoryClassGuid] [uniqueidentifier] NOT NULL ,
		[ResourceGuid] [uniqueidentifier] NOT NULL ,
		[CreatedDate] [datetime] NOT NULL ,
		[ModifiedDate] [datetime] NOT NULL ,
		[RowCount] [int] NOT NULL ,
		[SMSForwardDate] [datetime] NULL ,
		[DataHash] char(32) null,
		-- can be nullable cause we may not calculate it for partial updates 
		[DataLastChangedDate] datetime NULL
		CONSTRAINT [PK_ResourceUpdateSummary] PRIMARY KEY (InventoryClassGuid,ResourceGuid)
	)
END

Ok I can take the above and see the data related to a specific machine via:

SELECT *

FROM dbo.vComputer i

JOIN dbo.ResourceUpdateSummary rus

ON rus.ResourceGuid = i.Guid

WHERE i.name = 'MACHINENAME'

Order by rus.[ModifiedDate]

but can I see it specific to a specific record in dbo.Inv_Installed_File_Details?

Hi j,

You need to turn on History on the dataclass itself if you want to records the changes overtime.

I have not practical experience of this with SMP to share but it should be is the product documentation.

If there is no way to track this detail based on date how the heck does "purging of file detail inventory data" function! 

How can I say delete data 7 days old if it has no clue to the date it was written or modified?

The ResourceUpdateSummary table keeps track of when data is inserted (createddate) and modified (modifieddate) on the dataclass table, if this is what you are after.

This record is for all entries in the dataclass table, not for individually entries there.