bottsrm asked a question.
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.
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/s/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.
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).
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?
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'
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
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 = NULLSET @ObsoleteInventoryPeriodInDays = <Days>DECLARE @staleDate datetimeSET @staleDate = dateadd(day,-@ObsoleteInventoryPeriodInDays,DATEDIFF(Day, 0, GetDate()))DECLARE @BatchSize INT = 10CREATE table Computer (ComputerID bigint)WHILE 1 = 1BEGININSERT INTO ComputerSELECT TOP (@BatchSize)c.ComputerID FROM dbo.Computer c INNER JOIN dbo.InventoryReport ir on c.ComputerID = ir.ComputerIDWHERE HWDate < @staleDateBEGIN TRANSACTIONIF EXISTS(SELECT 1 from Computer )exec DeleteComputersCOMMIT TRANSACTIONDELETE FROM ComputerIF @@ROWCOUNT < @BatchSize BREAKENDDROP TABLE Computer
I hope that helps.
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
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.
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.
I hope that helps.