Unable to delete computers that are partially present in the database

Products

• IT Management Suite

Issue/Introduction

You noticed that you had computer records that were from machines that no longer existed in your network. However, you can't delete them because those are not appearing in the SMP Console.

When searching the database (you used the reference query from  KB 171823 "Find all tables that contain a specific GUID"), these machines only existed in few of our tables. Tables like:

CollectionMembership

Inv_Client_Task_Resources

InvHistAllRowsDeleted

NSInternal_ItemInstalled

ResourceFolder

ResourceUpdateSummary

RM_ResourceVirtual_Machine

String

TaskTargetDeviceCache

You want to make sure that these machines can be deleted properly.

Environment

ITMS 8.x

Cause

These machine resources were previously created during a Resource import that didn't have everything needed to have the proper reference in the database.

Resolution

There are some tables references that are needed to be properly flagged while trying to delete a computer resource. There are usually different ways to address a computer deletion but since we don't have some of the basic references needed to properly delete them, the following steps should help you to add those references:

1. Write down the name of the machines that you want to delete but don't appear in the SMP Console
2. Run the following query (it should add those GUID machines back to primary tables that will allow us to delete it properly):
   
   declare @t as table (guid uniqueidentifier)
   insert into @t
   select c.Guid
   from vRM_Computer_Item c
   left join ItemClass ic on ic.Guid = c.Guid
   where ic.Guid is null
   
   insert into ItemClass
   select t.guid, c.ClassGuid from @t t
   join vRM_Computer_Item c on c.Guid = t.guid
   left join ItemClass ic on t.guid = ic.Guid
   where ic.Guid is null
   
   insert into ItemNSSource
   select t.guid, 1
   from @t t
   left join ItemNSSource ns on ns.ItemGuid = t.guid
   where ns.ItemGuid is null
   
   insert into ItemResourceType
   select t.guid, c.ResourceTypeGuid
   from @t t
   join vRM_Computer_Item c on c.Guid = t.Guid
   left join ItemResourceType irt on irt.Guid = t.guid
   where irt.Guid is null
   
   insert into ScopeMembership
   select  case
   when c.ResourceTypeGuid = '493435F7-3B17-4C4C-B07F-C23E7AB7781F' then '91c68fcb-1822-e793-b59c-2684e99a64cd' --Computer Resource Type
   when c.ResourceTypeGuid = '2C3CB3BB-FEE9-48DF-804F-90856198B600' then '8de86a8a-f026-be3e-b9b8-0f07d7d5080b' --Virtual Machine Resource Type
   end, t.Guid, GETDATE ()
   from @t t
   join vrm_computer_Item c on c.Guid = t.guid
   left join ScopeMembership sm on sm.ResourceGuid = t.guid
   where sm.ResourceGuid is null
   
    
3. Go to the SMP Console and find the machine now listed under Computers. Select the machine and do right-click > Delete.