cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Deleting un-inventoried VMs from ImportedVirtualMachine table

We have a requirement to delete all Virtual Machines from the ImportedVirtualMachine table which have never been inventoried by FlexNet agent. We have a script (see below) which will give us all the machines without an InventoryDate. We only currently have VMWare & HyperV devices. 

select ivm.ComputerName as ImportedComputerName, ivm.virtualmachinetype,cc.computername, vm.FriendlyName, cc.InventoryDate, vm.*
from ImportedVirtualMachine ivm left join VirtualMachine vm
on ivm.uuid = vm.uuid left join compliancecomputer cc
on vm.ComplianceComputerID = cc.ComplianceComputerID
where ivm.VirtualMachineType in ('VMType.VMWare','VMType.HyperV')
and cc.computername is null
order by inventorydate

Any time we delete anything from ImportedVM table it repopulates. We're unsure which underlying table(s) we should be deleting from to ensure the results from the above query are do not repopulate. 

We wish to execute this query on a regular basis to ensure we do not have a number of legacy/decomm'd machines within this table as we do currently. 

(5) Replies
BradAkers
By Level 5 Flexeran
Level 5 Flexeran

That is due to them still being present in the FNMS Inventory database. You would need to remove them from both the Compliance and Inventory databases. 

As @BradAkers has alluded do, deleting data from the Imported* tables in the compliance database normally won't do anything interesting if you don't also delete data from the underlying inventory source database: the next time an import is performed, data from the source database will simply be re-imported and cause the Imported* records to be re-created.

A pattern that you can use to delete computers in the FlexNet inventory database is as follows:

SELECT ComputerID
INTO #Computer
FROM dbo.Computer
WHERE <insert condition to identify computers to be deleted>

EXEC dbo.DeleteComputers
(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)
Hi Chris, We are aware that the active computers will be repopulated in the ImportedVirtualMachine table once we performe the delete and the API runs again, and that is what we want. We just wish to clear out decomm'd importedVMs so the table has accurate information. Hope that sheds a bit more light on what we are trying to achieve. Thanks for the query you provided which allows us to perform the delete, however I'm unsure this is going to work. The reasons is currently the query we are using to populate the machines we wish to delete is based entirely in the compliance DB, and also has a condition on ComplianceComputerID=null. Therefore the only identifier we have for these machines is the 'ImportedComputerName' column. However when I search for some of these computer names in the Inventory.Computer tbl, I cannot locate the machines. Our query results are attached.
Sacha: It is highly likely that the information for these "un-inventoried" VMs are coming from the VMware vCenter inventory within FNMS via the vCenter SDK. Therefore, even if you follow some the suggestions in this post about deleting them with a SQL Script, the next time your vCenter Inventory runs, they will be recreated within FlexNet Manager. Kirk

Kirk, 

As of 2017 R3 I want to say VMs without our full HW/SW scan data will no longer show in the FNMS WebUI. They will only exist in the DB with a status of Awaiting Inventory.