How can I repopulate the Inv_Subnet table?

Products

• Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Problem
The Inv_Subnet table is what the Site Maintenance page uses to populate the sites with subnets.

There have been cases where there are known subnets that exist in the environment but do not exist in the Inv_Subnet table. This causes the problem first with Site Maintenance but also with the GetPackageInfo request. If the subnet does not exist in that table then the site maintenance doesn't work correctly for these missing subnets.

How can the Inv_Subnet table be repopulated then?

Environment

Any environment using site maintenance where subnet information is vital.

Cause
The cause of the problem is that there is most likely an existing resource in the Item table associated with the particular missing subnets. It isn't known how the association might have been broken, but if the resource item exists in the Item table then the scheduled task that rebuilds the Inv_Subnet table will skip that item, and consequently the Inv_Subnet table continues to have missing subnets.

Resolution

The solution is to remove the subnet resources from the Item table then run the Package Refresh scheduled task. It is this scheduled task that regenerates the Inv_Subnet information.

The items should not be deleted directly from the Item table through a SQL statement but rather a report or collection should be created that displays the items in question, then delete the items through the report. That way any associations with those items can also be cleaned up correctly. One cause for this situation may have been some type of deletion through SQL and the resource associations were lost.

 

The following SQL can be used to copy the items into a temporary table in case they need to be recovered for some reason.

-- Select the subnet reources items into a temp table in case there is
-- the need to put them back.
select * into #tmp_Resources from Item where ClassGuid = '9B9FF898-44D5-482E-8B9E-3F9997BD880E'

 

-- Uncomment the following SQL to remover the temp table once the
-- information has been validated.
-- drop table #tmp_Resources

Then the following SQL can be used to create a report or collection

select * from Item where ClassGuid = '9B9FF898-44D5-482E-8B9E-3F9997BD880E'

 

The guid {9B9FF898-44D5-482E-8B9E-3F9997BD880E} is the class guid for the subnet resource so the above query will display all subnets in the Item table. Part of the difficulty with regenerating the subnets artificially is that if any exist in the Item table then the Package Refresh task will ignore repopulating the Inv_Subnet table. Because of this it is necessary to delete all the subnets in the Item table. The repercussion of this is that all the subnet assignments in the Site Management will be removed so it becomes necessary to reselect the subnets for the sites. So before doing this it would be a good idea to make note of the subnets in the sites so they can be easily replaced.

 

Once the SQL has been written appropriately then the items in the report can be deleted and then the Package Refresh scheduled task should be run. This should then regenerate the Inv_Subnet table.