Spotlight on the Notification Server ResourceUpdateSummary Table

What is the ResourceUpdateSummary table and why should you know about it?

Content:

• Introduction
• ResourceUpdateSummary as in coredatabase.config
• Looking inside ResourceUpdateSummary
• Valuable data in the table
• ResourceUpdateSummary use in the DataLoading process
• Potential problem with the "rus"
• Conclusion

Introduction

Here's a case I have encountered recently: Patch Management Solution standard inventory policies are applied so the various inventory rules are running properly on a computer.

The data is sent out to the NS (we capture it via the 'Transport\Capture Event Folder' registry entry) and received there with no problems. The NSE's don't go into a bad folder and no error shows in the Altiris logs.

However when we check on the resource manager for the computer we do not see any data in the Inventory tab for the specific Software Release and Service Pack inventory sent. How can this be?

This can be explained by the ResourceUpdateSummary table (aka rus), specifically it's content! So before we explain how the table and its content is used first lets get acquainted with the table structure, see what it maps to and how we can review it's content.

Back to top

ResourceUpdateSummary as in coredatabase.config

ResourceUpdateSummary is defined in CoreDatabase.config (under '%installdir%\Notification Server\config') within the following xml entry:

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,
				[DataLastChangedDate] datetime NULL
		CONSTRAINT [PK_ResourceUpdateSummary] PRIMARY KEY (InventoryClassGuid,ResourceGuid)
	)
END
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ResourceUpdateSummary' AND COLUMN_NAME = 'DataLastChangedDate')
BEGIN 
	ALTER TABLE ResourceUpdateSummary ADD DataLastChangedDate datetime NULL
END
IF NOT EXISTS (SELECT 1 FROM sysindexes where name ='IDX_ResourceUpdateSummary_ModifiedDate' AND [id] = OBJECT_ID('dbo.ResourceUpdateSummary'))
BEGIN 
	CREATE INDEX [IDX_ResourceUpdateSummary_ModifiedDate] ON ResourceUpdateSummary(ModifiedDate,ResourceGuid)
END 
IF NOT EXISTS (SELECT 1 FROM sysindexes where name ='IDX_ResourceUpdateSummary_All' AND [id] = OBJECT_ID('dbo.ResourceUpdateSummary'))
BEGIN 
	CREATE UNIQUE INDEX [IDX_ResourceUpdateSummary_All] ON [ResourceUpdateSummary]([ResourceGuid],
 [InventoryClassGuid], [CreatedDate], [ModifiedDate], [RowCount])
END

Here are some comments on some of the columns:

• [InventoryClassGuid] [uniqueidentifier]: This is the guid of the dataclass for which the summary data is stored
• [ResourceGuid] [uniqueidentifier]: This is the guid of the resource that report the inventory data.
• [CreatedDate] [datetime]
• [ModifiedDate] [datetime]
• [RowCount] [int]: The count of rows present in the dataclass table for the resource.
• [SMSForwardDate] [datetime]
• [DataHash] char(32): The checksum of the inventory information that was loded in the dataclass table.
• [DataLastChangedDate] datetime

Back to top

Looking inside ResourceUpdateSummary

You can check the content of the table with a few queries. First let's get a count of rows on the table, and then a count InventoryClassGuid per resource, with the sum of rows involved:

--QUERY #1.1
select count(*) from ResourceUpdateSummary
--QUERY #1.2
select ResourceGuid, count(InventoryClassGuid) as 'Inventory classes', sum([RowCount] as 'Data rows in table'
  from ResourceUpdateSummary
 group by ResourceGuid
 order by [Inventory classes] desc

On my small test environment I have 362,357 entries in the rus table. The top resource on the second query contains 105 inventory class entries and 9993 rows in the 105 dataclass tables. Now what would be more interesting is to find out the type and name of the resources. So let's extend the query to return these entries, by mapping to the item table and class table [we'll probably write some articles on both these table in the future - as they are both worthy of inspection].

--QUERY #2
select it.classguid, cl.type, count(rus.resourceguid) as 'Resource count'
  from ResourceUpdateSummary rus
  join Item it
    on rus.ResourceGuid = it.Guid
  join Class cl
    on it.classguid = cl.guid
 group by it.classguid, cl.type
 order by [Resource count] desc

This yield the following results (unabridged): Table #1

What's really interesting from this query is that we albeit Computer resource types are dominating they are not the only user of this table (hence the generic use of the resource term instead of computer). Now that we've looked at the table itself let's look at the entries for my top user reported in query set #1.2 above. So we run another query to show only a small subset of the data in the rus:

--QUERY #3
select top 20 *
  from ResourceUpdateSummary rus
 where ResourceGuid = '8865CA6F-44FC-428C-86CE-4553529EBAC7'

Which gives us the following (abridged):

[1] This is a random class guid, so we don't need to have it in full here.
[2] {8865CA6F-44FC-428C-86CE-4553529EBAC7} is the computer guid.

Back to top

The valuable data in the table

So now what does this mean? What's the interest in looking into that table? In details we can see the value-added by that table is with the "modifieddate" and "datalastchangeddate" columns (c4 and c8, if we start with c1=column1).

These two fields allow us to check when the data was modified (reviewed by an internal process) and updated (datalastmodifieddate) which is very useful (to determine when the information was last changed).

From a system perspective (the NS itself) the table contains a column that is most useful to limit the SQL Server activities: the datahash column (c7).

The datahash column contains a hash of the inventory data contained within the table. It is checked when data loaders are receiving inventory and have an in-memory representation of that information. A hash is run on the in-memory data and compared with the datahash from the rus table. If the datahashes do match the content of the inventory in-memory is the same as the one in the database, so we will not update (insert back) the database.
Back to top

ResourceUpdateSummary use in the DataLoading process

The dataloading process (process of inserting inventory or event data from NSE files into the SQL database) makes use of the ResourceUpdateSummary datahash field as described above before it stores information into the database.

This can be better illustrated with the following diagram:

Back to top

Potential problem with the ResourceUpdateSummary

And now I can see the spark in my smart readers' eyes! Couldn't this be the source of our problem (data not being inserted into the database)? It sure can be in specific conditions. For example if a user (or external process like a direct db import etc) tampers the datatable content without clearing the update the ResourceUpdateSummary!
Back to top

Conclusion

So we have seen that the ResourceUpdateSummary is very important, and also that tampering the dataclass tables content is a no go (no, no, no!!!). I hope this article helped shed some light on this area of the data management inside a Notification Server.
Back to top