Deleting old inventory records

[marius]

Does anyone know what condition should be met to trigger deletion of old inventory records from FNMS platform? Looking for best practice how to remove devices that previously reported inventory (FNMS agent), but now are removed from the network. We have old inventory records that have link to retired asset, and inventory records without assets. Thanks, Marius.

[ChrisG · Accepted solution]

The only built-in process I can think of that deletes inventory gathered by the FlexNet agent is the Active Directory import process: when a computer object is deleted or disabled in Active Directory, the associated inventory will be deleted from the inventory database (and subsequently the inventory device in the compliance database will also be removed on the next inventory import operation). Inventory associated with computers that are not in any Active Directory domain that is imported into FlexNet will never be deleted--it will just age gracefully in place.

If you are willing to go beyond built-in deletion processes and are using FlexNet on-premises (not cloud), the following SQL script is something that you may want to consider running on a regular basis. This script seeks to delete details about computers that have not been heard from for 90 days or more. 

-- Delete computer inventory records where no data has been received for at least a set number of days.
-- Run this script on the FlexNet inventory database.
-- The script works with FlexNet releases at least between 2017 R2 and 2019 R1. It is likely to work with at least some future releases.

DECLARE @OldestDataDate DATE
SET @OldestDataDate = DATEADD(d, -90, GETDATE())

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET DEADLOCK_PRIORITY LOW

DECLARE @TenantID INT
DECLARE @TenantName NVARCHAR(256)

DECLARE c CURSOR FOR SELECT TenantID, TenantName FROM dbo.Tenant
OPEN c

FETCH NEXT FROM c INTO @TenantID, @TenantName
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT N'Deleting inactive computers in tenant ' + @TenantName
	EXEC dbo.SetTenantID @TenantID

	WHILE 1 = 1
	BEGIN
		IF OBJECT_ID('tempdb..#Computer') IS NOT NULL
			DROP TABLE #Computer

		SELECT TOP 100 c.ComputerID -- Delete in small batches to avoid locking too much data for too long
		INTO #Computer
		FROM dbo.Computer c
			LEFT OUTER JOIN dbo.ComputerResourceData AS crd ON crd.ComputerUID = c.ComputerUID
		WHERE
			(	-- Computer is not in Active Directory
				c.GUID IS NULL
				-- Or some data has been received about the computer
				OR crd.ComputerResourceID IS NOT NULL
				OR EXISTS(SELECT 1 FROM dbo.InventoryReport inr WHERE inr.ComputerID = c.ComputerID)
				OR EXISTS(SELECT 1 FROM dbo.Installation i WHERE i.ComputerID = c.ComputerID AND i.OrganizationID = c.ComputerOUID)
				OR EXISTS(SELECT 1 FROM dbo.ComputerUsage cu WHERE cu.ComputerID = c.ComputerID)
			)
			-- But the data is old
			AND (crd.ComputerUID IS NULL OR NOT(crd.LastUpdated >= @OldestDataDate))
			AND NOT EXISTS(
				SELECT 1
				FROM dbo.InventoryReport inr
				WHERE inr.ComputerID = c.ComputerID
					AND (
						HWDate >= @OldestDataDate
						OR SWDate >= @OldestDataDate
						OR FilesDate >= @OldestDataDate
						OR ServicesDate >= @OldestDataDate
						OR VMwareServicesDate >= @OldestDataDate
						OR OVMMDate >= @OldestDataDate
						OR AccessDate >= @OldestDataDate
					)
			)
			AND NOT EXISTS(
				SELECT 1
				FROM dbo.ServiceProvider sp
				WHERE sp.ComputerID = c.ComputerID
					AND LastInventoryDate >= @OldestDataDate
			)
			AND NOT EXISTS(
				SELECT 1
				FROM dbo.Installation i
				WHERE i.ComputerID = c.ComputerID
					AND i.OrganizationID = c.ComputerOUID
					AND Received >= @OldestDataDate
			)
			AND NOT EXISTS(
				SELECT 1
				FROM dbo.ComputerUsage cu
				WHERE cu.ComputerID = c.ComputerID
					AND LastReported >= @OldestDataDate
			)

		DECLARE @Count INT
		SET @Count = @@ROWCOUNT

		IF @Count = 0
			BREAK

		PRINT 'Deleting ' + CAST(@Count AS VARCHAR) + ' computers'

		EXEC dbo.DeleteComputers
	END

	PRINT 'No more computers found to delete'

	FETCH NEXT FROM c INTO @TenantID, @TenantName
END

CLOSE c
DEALLOCATE c

If you are using FlexNet release  2017 R1 or earlier, try the following version of the script:

-- Delete computer inventory records where no data has been received for at least a set number of days.
-- Run this script on the FlexNet inventory database.
-- The script works with FlexNet releases at least between 2015 R1 and 2017 R1. It may work with some prior releases, but will not work correct with newer releases.

DECLARE @OldestDataDate DATE
SET @OldestDataDate = DATEADD(d, -90, GETDATE())

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET DEADLOCK_PRIORITY LOW

DECLARE @TenantID INT
DECLARE @TenantName NVARCHAR(256)

DECLARE c CURSOR FOR SELECT TenantID, TenantName FROM dbo.Tenant
OPEN c

FETCH NEXT FROM c INTO @TenantID, @TenantName
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT N'Deleting inactive computers in tenant ' + @TenantName
	EXEC dbo.SetTenantID @TenantID

	WHILE 1 = 1
	BEGIN
		IF OBJECT_ID('tempdb..#Computer') IS NOT NULL
			DROP TABLE #Computer

		SELECT TOP 100 c.ComputerID -- Delete in small batches to avoid locking too much data for too long
		INTO #Computer
		FROM dbo.Computer c
		WHERE
			(	-- Computer is not in Active Directory
				c.GUID IS NULL
				-- Or some data has been received about the computer
				OR EXISTS(SELECT 1 FROM dbo.InventoryReport inr WHERE inr.ComputerID = c.ComputerID)
				OR EXISTS(SELECT 1 FROM dbo.Installation i WHERE i.ComputerID = c.ComputerID AND i.OrganizationID = c.ComputerOUID)
				OR EXISTS(SELECT 1 FROM dbo.ComputerUsage cu WHERE cu.ComputerID = c.ComputerID)
			)
			-- But the data is old
			AND NOT EXISTS(
				SELECT 1
				FROM dbo.InventoryReport inr
				WHERE inr.ComputerID = c.ComputerID
					AND (
						HWDate >= @OldestDataDate
						OR SWDate >= @OldestDataDate
						OR FilesDate >= @OldestDataDate
						OR ServicesDate >= @OldestDataDate
						OR VMwareServicesDate >= @OldestDataDate
						OR OVMMDate >= @OldestDataDate
						OR AccessDate >= @OldestDataDate
					)
			)
			AND NOT EXISTS(
				SELECT 1
				FROM dbo.ServiceProvider sp
				WHERE sp.ComputerID = c.ComputerID
					AND LastInventoryDate >= @OldestDataDate
			)
			AND NOT EXISTS(
				SELECT 1
				FROM dbo.Installation i
				WHERE i.ComputerID = c.ComputerID
					AND i.OrganizationID = c.ComputerOUID
					AND Received >= @OldestDataDate
			)
			AND NOT EXISTS(
				SELECT 1
				FROM dbo.ComputerUsage cu
				WHERE cu.ComputerID = c.ComputerID
					AND LastReported >= @OldestDataDate
			)

		DECLARE @Count INT
		SET @Count = @@ROWCOUNT

		IF @Count = 0
			BREAK

		PRINT 'Deleting ' + CAST(@Count AS VARCHAR) + ' computers'

		EXEC dbo.DeleteComputers
	END

	PRINT 'No more computers found to delete'

	FETCH NEXT FROM c INTO @TenantID, @TenantName
END

CLOSE c
DEALLOCATE c

[Andrei_ABB]

Hi Chris,

Thank you for replying. Yes, I was referring to the script posted in 2019, and I now see it uses DeleteComputers. We are using a similar script, just with less conditions (we only check for hwdate < DATEADD(d, -90, GETDATE()) ) and as you suggested in batches of 100. I have one more question though, If I want to only delete inventory coming from agents but not from SCCM (because SCCM will get re created at the next import, and I prefer to keep old SCCM devices ignored for now) would I be correct in selecting only inventory where agentid is null? Is there aby better field in the Inventory DB I can use to filer out a certain source?

Regards,
Andrei

[ChrisG]

The "inventory" database that contains the DeleteComputer(s) stored procedures and the data being discussed in this thread only contains inventory gathered from the FlexNet agent. Data imported from other systems such as SCCM is not stored in this database - that data only appears in the "compliance" database. So if you delete records from the inventory database then you can be confident you are not deleting inventory that has been received from any other source.

[Andrei_ABB]

Got it. Thank you for responding.

[aditikumarrout]

Is there any API provided by Flexera for deleting the out of date devices completely ? so that the licenses associated with these devices will be released.

[kclausen]

@aditikumarrout - If you change the status of an Inventory Device from "Active" to "Ignored", the software applications on that device will not consume against any software licenses.  

[aditikumarrout]

@kclausen, Thanks for the information.

We also tried with this option but the device will still be appeared everywhere i.e. in All Inventory and Ignored Inventory.

Actually we have the list retired machine details and only need to remove / delete these machine details from FNMS in such a way that the licenses associated with these machines will be released properly.

Is there any proper way i.e. any API or stored procedure provided by Flexera to achieve this ?

[kclausen]

@aditikumarrout - If you look at all of the comments on this post, there are some comments that contain the raw SQL scripts that can be run if you wish to physically delete inventory devices from FlexNet Manager On-Premises.

[aditikumarrout]

@kclausen, I have gone through the SQL scripts and seems like DeleteComputer stored procedure is being used to delete the devices.

Wanted to confirm that, running the DeleteComputer stored procedure directly to delete the single device for test wouldn't have any adverse effect on the FNMS database ?

Thank you.

[ChrisG]

I can't think of any obviously reason why the DeleteComputer stored procedure to delete data for a computer record for testing would have adverse affects.

In case you didn't pick it up from other comments, this is a stored procedure in the inventory database (not the compliance database). It will delete data about the computer gathered by the inventory agent. If you have a different inventory source (such as SCCM) then this stored procedure won't help you. You should look to delete the computer record from the source database in that case.

[aditikumarrout]

We have run the stored procedure DeleteComputer of InventoryManager database for some devices, those which were available in out of date inventory page and then did reconciliation, we found that these devices were not removed from this page.

 We would like to understand that which processes/procedures gets executed once we click the “Delete” button on out of date inventory page of FNMS.

Thank you.

[JohnSorensenDK]

@aditikumarrout 

This is an extensive community thread with a lot of different information about cleaning up inventory from various databases and sources.

If you ask specifically about deleting inventory devices from the out of data inventory page from within the UI it will delete the devices from the compliance database only (AFAIK). I.e. the devices may reappear after the nightly inventory import and reconcile if they're still in either of the inventory sources as described by others in this thread.

Thanks,

[sasikumar_r]

Hi All  @kclausen  @ChrisG ,

As per FNMS 2017R2 Release Notes, Page No:7 It has been mentioned that deleting the device from All Inventory will delete it from All discovered and Inventory DB as well. Below is the URL & Attached is the screenshot for reference. By anychance it got changed on the later versions?

We are on 2019R2 muti tenant environment, but it is not working like it is mentioned. it is just deleting inventory from compliance DB. No other Inventory source, inventory is from Agent only.

https://docs.flexera.com/fnms2017r2/onpremises/FlexNet%20Manager%20Suite%20Release%20Notes%202017%20R2%20(English).pdf

https://download.flexnetoperations.com/439214/1169/747/11855747/FlexNet%20Manager%20Suite%20Release%20Notes%202017%20R2%20(English).pdf?ftpRequestID=7663748577&server=download.flexnetoperations.com&dtm=DTM20200402120241ODA3ODQxMTA5&authparam=1585854161_0db60e8342e7d2bb012af6157f0b8aa4&ext=.pdf

Thank you
Sasi

[JohnSorensenDK]

@sasikumar_r 

I would recommend you open a support case to track it, if you believe that you've found a 'bug'.

Thanks,

[ChrisG · Accepted solution]

Some of the discussion in this thread has now been obsoleted by the new functionality discussed in the following post: Auto-cleanup of Obsolete FlexNet Inventory.

[kiransuthar]

Does this script will work in FNMS 2023 R2 version ? 

In case we don't want to delete those computer is it possible we just mark them as Ignored ? 

Can you please share the script we can run to change status from active to ignore for greater than 90 days not inventoried 

[dhoagland1]

With 2023 R2 you can automatically delete Flexera Inventory over 90 days with a setting if that is desired. Please review the following documentation on how to do so:
IT Asset Management Online Help - IT Asset Management Settings: Inventory Tab (flexera.com)

It is also discussed in the post linked by Chris G above.

If you want to simply mark them as Ignored, you can do that via a Business Adapter.