Deleting old records from all inventory from 2020 R2 On Premises

[bottsrm]

I have 37,000 records that were discovered from the Flexnet Inventory Connection that are much older than 90 days. They are ignored but would like to delete the records. What is the best way to remove the records. I have tried deleting them from the WEBUI from all inventory and from discovery but they are still there. I have also deleted all the records that were in out of date inventory.  

[dhoagland1]

In 2020 R2 you'll need to delete the inventory directly in the FNMS Inventory database. You can find the procedure outlined by Chris Grinton in this thread: https://community.flexera.com/t5/FlexNet-Manager-Forum/Deleting-old-inventory-records/td-p/99395

In the most recent release there is a setting to auto delete inventory older than 60, 90 or 180 days so if you're planning an upgrade soon you can choose that option instead.

[bottsrm]

I could not find the procedure in 2020 R2. Called dbo.deletecomputers.

[dhoagland1]

Make sure you're in the FNMSInventory database rather than the FNMSCompliance database. You're removing the actual data provided by the agent which is stored separately from what is displayed in the UI. Think of it as being similar to removing an outdated machine from SCCM, that removal would flow through to FNMS during the import. We're removing the data from the actual source here (which is why it can keep coming back otherwise).

[bottsrm]

I did verify I was in the FNMSInventory database on Premises. I even ran a script to search for stored procedures and it returned nothing. Is this procedure in 2020 R2?

[dhoagland1]

It's been in every version of FNMS I've used (9.01 through the latest).

In SSMS, open Programmability -> Stored Procedures. From there you can start typing "dbo.deletecomputers" and it should jump you down.

You can also search with the following query: SELECT * FROM sys.objects WHERE type = 'P' AND name = 'DeleteComputers'

[FarrukhNaz]

In 2022 R1 and higher versions inventory clean up is automated but for the older versions you can delete old inventory via SQL script. Here is the Inventory Computer and History deletion scripts

[bfaller]

You have to delete them both from all inventory and from all discovered devices.

Regards.

[JJacildo · Accepted solution]

Hello @bottsrm you can run this script instead to delete Obsolete Inventory within <N> number of days. Just don't forget to set the number of days within the SET @ObsoleteInventoryPeriodInDays = <Days>  where <Days> can be any value from 30, 60, or 90 days.

DECLARE	@ObsoleteInventoryPeriodInDays INT = NULL

SET @ObsoleteInventoryPeriodInDays = <Days>
	
DECLARE @staleDate datetime

SET @staleDate = dateadd(day,-@ObsoleteInventoryPeriodInDays,DATEDIFF(Day, 0, GetDate()))
	
DECLARE @BatchSize INT = 10
CREATE table #Computer (ComputerID bigint)
		
WHILE 1 = 1
BEGIN
		
	INSERT INTO #Computer
	SELECT TOP (@BatchSize)
		c.ComputerID 
	FROM dbo.Computer c 
		INNER JOIN dbo.InventoryReport ir on c.ComputerID = ir.ComputerID
	WHERE HWDate < @staleDate
		
	BEGIN TRANSACTION
	
	IF EXISTS(SELECT 1 from #Computer )
		exec DeleteComputers
		
	COMMIT TRANSACTION
		
	DELETE FROM #Computer
	IF @@ROWCOUNT < @BatchSize BREAK
		
END
	
DROP TABLE #Computer

I hope that helps.

[jjoaquin]

Hi JJacildo,

Thanks for providing this SQL command which I believe to help me more in my current state. However, it appears that it will only allow me to run this once. I tried running on a different day but it gives me an error saying that the object #Computer already exists.

Any thoughts on how I can pass through this?

Thanks,

Wacky

[alexander_frit]

@jjoaquin 

You have to make sure that the command "DROP TABLE #Computer" was executed at the end.
But, maybe, someone else had also created a #Computer table and is working currently with it or hasn't executed the DROP TABLE command at the end.

[jjoaquin]

I actually run that last one line alone (DROP TABLE #Computer) and that do the trick...thanks mate.