Asset Intelligence custom labels do not get replicated to child sites in System Center Configuration Manager 2007

Symptoms

This can occur if the column "KeyValue" is set to an incorrect HEX value for the column "KeyName" where KeyName is "Replication" in the AI_Generic table.

Cause

To resolve this issue, follow the steps below:

1. Create a test custom label.
2. Open SQL Management Studio and connect to the central site's SQL server.
3. Run the following query on the Central Site's database:

select * from LU_Category_Local where type=2

4. Check the entry there for the custom label you just created. Note the value for the rowversion column for this entry.
5. Run the following query to correct the AI_Generic table.

Update AI_Generic Set KeyValue = '<HEX value from the first SQL query>' where KeyName='Replication'

6. Now, for each of the custom labels which need to be replicated from the central to the child site just add a description in their properties.
7. Once all the required changes have been made, wait for a couple of minutes and then restart the SMS Executive service.
8. Monitor the AIKBMGR.LOG and you should see messages similar to the ones below indicating successful replication of these objects:

Successfully replicated SMS Object Asset Intelligence batched data{00ff83ae-6df4-408f-ad4d-4f98ce7c5be2}, ReplVarFileName:D:\PROGRAM FILES\MICROSOFT CONFIGURATION MANAGER\inboxes\AIKbMgr.box\10x5bpkx.TMP
Successfully replicated to all sites. Object - ID= Asset Intelligence batched data{00ff83ae-6df4-408f-ad4d-4f98ce7c5be2} - Name= Asset Intelligence batched data{00ff83ae-6df4-408f-ad4d-4f98ce7c5be2}

Once you start seeing these messages in the log you can be sure that the custom labels are now getting replicated. In order to confirm this, just open the ConfigMgr 2007 console on the child site and refresh the custom labels section.

Resolution

Below is a detailed look into the mechanics of how this process works. To view most of this information you will need to have SQL logging enabled. No component level logging was enabled for this process.

For each object (custom label or other AI sync information) that is created or modified, there is a change request that gets generated. The SMS Provider is the component that has the responsibility of updating the respective tables in SQL with this change.

You would see messages similar to this one in the SMSProv.log on the site server:

DEBUG> IF NOT EXISTS (SELECT CategoryID FROM LU_Category_Local WHERE Type=2 AND CategoryName = 'Not Approved' AND IsDeleted=0)     IF ((SELECT count(*) FROM LU_Category_Local WHERE Type=2 AND IsDeleted=0) < 100)    BEGIN         insert into v_LU_Category_Editable (CategoryName, Description, LanguageID, Type) values ('Not Approved', '', null, 2)

This shows that it is adding the newly created custom label into our LU_Category_Local table.

After this, it waits for a trigger to initiate the checking for changes that need to be replicated by the AIKBMGR. This trigger kicks off every 30 minutes.

Looking further into the LU_Category_Local table, we find that there is a column there which is called rowversion. This column gets updated to a new HEX value every time the respective custom label is updated.

Now, when the AIKBMGR component has to check for changes, it runs a stored procedure by the name of sp_ComputeBatchRange. This function takes two parameters. The first one is an integer which sets the value of the size of the batch job and the other is the value of the "KeyValue" column for Replication in the AI_Generic table.

So how does this value get populated in the AI_Generic table? Simply put, it starts with the first object which gets added for AI when it is enabled. As mentioned earlier, each AI object has a rowversion value. The value of the column in the AI_Generic table gets updated at the time of the first batch replication job. Until then it will show up as 0x0000000000000000. It gets updated to the value of the rowversion of the object which was the latest one updated at the time the stored procedure ran.

How does AIKBMGR know what all objects are to be replicated in case of multiple changes made since the last time the replication took place? To answer this one, let's have a look at an excerpt of the definition of the stored procedure itself.

SELECT max(ChangeTables.RV) FROM
        (
            SELECT max(rowversion) AS 'RV' FROM LU_CPU
            UNION
            SELECT max(rowversion) AS 'RV' FROM LU_CPU_Local
            UNION
            SELECT max(rowversion) AS 'RV' FROM LU_Category
            UNION
            SELECT max(rowversion) AS 'RV' FROM LU_Category_Local
            UNION
            SELECT max(rowversion) AS 'RV' FROM LU_SoftwareList
            UNION
            SELECT max(rowversion) AS 'RV' FROM LU_SoftwareList_Local
            UNION
            SELECT max(rowversion) AS 'RV' FROM LU_SoftwareHash
            UNION
            SELECT max(rowversion) AS 'RV' FROM LU_SoftwareCode
            UNION
            SELECT max(rowversion) AS 'RV' FROM LU_HardwareRequirements
            UNION
            SELECT max(rowversion) AS 'RV' FROM LU_HardwareRequirements_Local
            UNION
            SELECT max(rowversion) AS 'RV' FROM LU_MSProd
    UNION
    SELECT max(rowversion) AS 'RV' FROM LU_CAL_ProductList
        ) AS ChangeTables

From the above section, it is pretty clear that the procedure runs and checks for the max rowversion values in each of the tables listed above. Then, it checks for the max rowversion value from these max values from each of these tables. This gives us the latest updated object.

This will log something similar to this in the AIKBMGR.LOG:

1 changes in range [0x0000000002668156-0x000000000266AFCA]

Here, the left HEX rowversion value (which is referred to as lowerrowversion in the SP), will be 0x0000000000000000 if this is the first time a replication is taking place for these objects. In case this is not the first time, then, this will show the value of the last replicated object (and again this is from the AI_Generic table). So, in effect, this gives us a range of objects we need to check to know exactly what needs to be replicated.

Now, the next step is to check each of the above tables for objects which are in the above range. In my case the range is [0x0000000002668156-0x000000000266AFCA] and the site code of the central site is BCS. Here are the queries which are run:

select ID,CPUHash,Manufacturer,BrandID,PCache,NormSpeed,Mobile,Name,CPU_Birth,SourceSite,LastUpdated,rowversion,CONVERT(int, IsDeleted) from LU_CPU where ( creationversion > 0x0000000002668156 and creationversion <= 0x000000000266AFCA  or  creationversion <= 0x0000000002668156 and rowversion > 0x0000000002668156 and rowversion <= 0x000000000266AFCA ) and ( SourceSite = 'BCS')  order by  creationversion asc, rowversion asc

select ID,CPUHash,Manufacturer,BrandID,PCache,NormSpeed,Mobile,Name,CPU_Birth,SourceSite,LastUpdated,rowversion,CONVERT(int, IsDeleted) from LU_CPU_Local where ( creationversion > 0x0000000002668156 and creationversion <= 0x000000000266AFCA  or  creationversion <= 0x0000000002668156 and rowversion > 0x0000000002668156 and rowversion <= 0x000000000266AFCA ) and ( SourceSite = 'BCS')  order by  creationversion asc, rowversion asc

select CategoryID,Type,CategoryName,LanguageID,Description,SourceSite,LastUpdated,rowversion,CONVERT(int, IsDeleted),RemoteID from LU_Category where ( creationversion > 0x0000000002668156 and creationversion <= 0x000000000266AFCA  or  creationversion <= 0x0000000002668156 and rowversion > 0x0000000002668156 and rowversion <= 0x000000000266AFCA ) and ( SourceSite = 'BCS')  order by  creationversion asc, rowversion asc

select CategoryID,Type,CategoryName,LanguageID,Description,SourceSite,LastUpdated,rowversion,CONVERT(int, IsDeleted) from LU_Category_Local where ( creationversion > 0x0000000002668156 and creationversion <= 0x000000000266AFCA  or  creationversion <= 0x0000000002668156 and rowversion > 0x0000000002668156 and rowversion <= 0x000000000266AFCA ) and ( SourceSite = 'BCS')  order by  creationversion asc, rowversion asc

select ID,SoftwareID,CommonName,CommonVersion,CommonPublisher,CategoryID,FamilyID,Tag1ID,Tag2ID,Tag3ID,SoftwareCode,SoftwarePropertiesHash,Name,Version,Publisher,SourceSite,LastUpdated,rowversion,CONVERT(int, IsDeleted),LocaleID from v_LU_SoftwareIdentity_Repl where ( creationversion > 0x0000000002668156 and creationversion <= 0x000000000266AFCA  or  creationversion <= 0x0000000002668156 and rowversion > 0x0000000002668156 and rowversion <= 0x000000000266AFCA ) and ( SourceSite = 'BCS')  order by  creationversion asc, rowversion asc

select ID,SoftwareID,CommonName,CommonVersion,CommonPublisher,CategoryID,FamilyID,Tag1ID,Tag2ID,Tag3ID,SoftwareCode,SoftwarePropertiesHash,Name,Version,Publisher,SourceSite,LastUpdated,rowversion,CONVERT(int, IsDeleted),LocaleID from v_LU_SoftwareSignature_Repl where ( creationversion > 0x0000000002668156 and creationversion <= 0x000000000266AFCA  or  creationversion <= 0x0000000002668156 and rowversion > 0x0000000002668156 and rowversion <= 0x000000000266AFCA ) and ( SourceSite = 'BCS')  order by  creationversion asc, rowversion asc

select ID,SoftwareID,CommonName,CommonVersion,CommonPublisher,CategoryID,FamilyID,Tag1ID,Tag2ID,Tag3ID,SoftwareCode,SoftwarePropertiesHash,Name,Version,Publisher,SourceSite,LastUpdated,rowversion,CONVERT(int, IsDeleted),LocaleID from v_LU_SoftwareIdentity_Local_Repl where ( creationversion > 0x0000000002668156 and creationversion <= 0x000000000266AFCA  or  creationversion <= 0x0000000002668156 and rowversion > 0x0000000002668156 and rowversion <= 0x000000000266AFCA ) and ( SourceSite = 'BCS')  order by  creationversion asc, rowversion asc

select ID,Product,MinCPU,MinRAM,MinDiskSize,MinDiskFree,SourceSite,LastUpdated,rowversion,CONVERT(int, IsDeleted) from LU_HardwareRequirements where ( creationversion > 0x0000000002668156 and creationversion <= 0x000000000266AFCA  or  creationversion <= 0x0000000002668156 and rowversion > 0x0000000002668156 and rowversion <= 0x000000000266AFCA ) and ( SourceSite = 'BCS')  order by  creationversion asc, rowversion asc

select ID,Product,MinCPU,MinRAM,MinDiskSize,MinDiskFree,SourceSite,LastUpdated,rowversion,CONVERT(int, IsDeleted) from LU_HardwareRequirements_Local where ( creationversion > 0x0000000002668156 and creationversion <= 0x000000000266AFCA  or  creationversion <= 0x0000000002668156 and rowversion > 0x0000000002668156 and rowversion <= 0x000000000266AFCA ) and ( SourceSite = 'BCS')  order by  creationversion asc, rowversion asc

select ID,SoftwareCode,SoftwareHash,ProductCategory,LicenseType,SourceSite,LastUpdated,rowversion,CONVERT(int, IsDeleted) from LU_CAL_ProductList where ( creationversion > 0x0000000002668156 and creationversion <= 0x000000000266AFCA  or  creationversion <= 0x0000000002668156 and rowversion > 0x0000000002668156 and rowversion <= 0x000000000266AFCA ) and ( SourceSite = 'BCS')  order by  creationversion asc, rowversion asc

select ID,MPC,PartNumber,MLSFamilyName,MLSProductName,VersionCode,VersionSequence,LanguageName,ProductDistTypeName,LicenseTypeName,BatchID,SourceSite,LastUpdated,rowversion,CONVERT(int, IsDeleted) from LU_MSProd where ( creationversion > 0x0000000002668156 and creationversion <= 0x000000000266AFCA  or  creationversion <= 0x0000000002668156 and rowversion > 0x0000000002668156 and rowversion <= 0x000000000266AFCA ) and ( SourceSite = 'BCS')  order by  creationversion asc, rowversion asc

Now, we will get the objects which are in that range from each of these queries and will replicate the ones which are returned from these queries. We see messages similar to this in the AIKBMGR.LOG:

Total 1 objects
0 CPU, 1 Category, 0 Software, 0 HwReqs, 0 CalTrack, 0 MLS
0 Uploaded items
First Category change
Category [Id=100013, IsEdited=1, IsDeleted=0]
Last Category change
Category [Id=100013, IsEdited=1, IsDeleted=0]

This shows that the object which is being replicated is a category object with an ID 100013. Now to replicate these objects -

AIKbMgr: Adding 0 CPU items to replication blob
AIKbMgr: Adding 1 Category items to replication blob
AIKbMgr: Adding 0 Software items to replication blob
AIKbMgr: Adding 0 HardwareReqs items to replication blob
AIKbMgr: Adding 0 CalTrack items to replication blob
AIKbMgr: Adding 0 MLS items to replication blob
AIKbMgr: Adding rowversion 0x000000000266AFCA to replication blob
Successfully replicated SMS Object Asset Intelligence batched data{00ff83ae-6df4-408f-ad4d-4f98ce7c5be2}, ReplVarFileName:D:\PROGRAM FILES\MICROSOFT CONFIGURATION MANAGER\inboxes\AIKbMgr.box\10x5bpkx.TMP
Successfully replicated to all sites. Object - ID= Asset Intelligence batched data{00ff83ae-6df4-408f-ad4d-4f98ce7c5be2} - Name= Asset Intelligence batched data{00ff83ae-6df4-408f-ad4d-4f98ce7c5be2}

The object is now replicated.

The last part is to update the AI_Generic table with the new value for the last replicated object. The following is the SQL query which is run:

update AI_Generic set KeyValue='0x000000000266AFCA' where GroupID='Watermarks' and KeyName='Replication'

Although, the above explanation is more specific to the replication of custom labels, this will be the same for other AI object replications as well. The only difference would be that the tables being updated by the SMS provider would change depending on the object which needs to be updated.

More Information