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

[mfranz]

Hi Marius,

Depending on your data and processes, there are multiple things to consider.

• Just delete inventories (and maybe assets) based on their inventory date. E.g. 90 days after the last report. Problem wiht this approach: If a device does not report anymore, it does not necessarily mean it is actually removed. It could also mean you could have a firewall issue or something else.
• Rely on an external source, like a CMDB. Ideally, this data would be based on comprehensible (device removal) processes.
• Or you could even combine data sources and only remove inventory when multiple requirements are met, e.g. inevntory age and CMDB status.

I would then remove the devices from the Inventory database using an existing stored procedure (ComputerRemoveBatch). The Compliance Import should then take care of removing them from the Compliance database as well.

Best regards,

Markward

[marius]

I know that FNMS have background jobs to clean old inventory records. Is it possible to get description of conditions triggering such deletions? I would prefer to leave clean-up for platform if it is possible. 

[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

[mrichardson]

@ChrisG  is correct, there is no automated solution for removing old devices apart from:

1. For FNMS inventory once Active Directory no longer has the device it will be removed
2. For other inventory sources, once it's gone from that source it will be removed.

If the device is inventoried by multiple sources, it needs removing from all of them.

There is an open enhancement: "FNMS-4384:  FNMS does not have an option to retire / delete obsolete inventoried computers from IM database" which would allow configuration of deleting after X days.

Based on the current number of customers linked, it is not expected to be added to the product in 2019.  This could change if more customers request this.

[RobertH]

The Computers table and the DeleteComputers stored procedure are not resources in my 2018 R1 deployment.  Did you create a view and a stored procedure to support this script?

[mrichardson]

@RobertH ,

The table and stored procedure does exist in 2018 R1 under the Inventory database rather than the Compliance database.

Can you check which database you're looking at and make sure it's the Inventory DB?

Hope this helps,
Matt

[RobertH]

Thank you.  That was the problem.  I was looking at the Compliance database instead of the Inventory database.

[venus_m_concel]

Hi,

I also have same query on how to delete old inventory records for VMs  with inventory source "FlexNet Manager Suite". We are using FlexNet cloud based license.

We have few VMs already decom and this is still appearing in "All Inventory".

Currently, we set the status to "Ignored" so that it won't be counted while calculating license compliance.

But for best practice, by removing flexnet agent in VM, resolve the issue? and won't display the decom VM in "All Inventory".

Hope to hear from you soon.

Thanks,

[ChrisG]

@venus_m_concel - for devices managed in FlexNet Cloud that have had inventory collected by the FlexNet inventory agent, options to remove a record (rather than simply giving it an "Ignored" status) are:

1. If the computer was in an Active Directory domain from which data is being imported into FlexNet, then the inventory for the computer should be removed from FlexNet when the computer in Active Directory is disabled or deleted.
2. Manually delete the discovered device record (All Discovered Devices page) then the inventory device record (All Inventory page) for the computer. (*)

(*) My understanding of how to manually delete records from FlexNet may be out of date. The process I've described certainly used  to reflect what needed to be done, and should still be sufficient: delete both the discovered device and inventory device record. I think I may have heard that this changed at some point and only one of these records (probably the inventory device record) needs to be deleted - so try it and see. The thing to check to see if it has worked would be whether the inventory device record re-appears after the next inventory import.

[venus_m_concel]

Hi ChrisG,

For our devices scanned via SCCM, we don't have problem in "All Inventory". Once, we deleted the computer in AD, ePO and SCCM, it won't display anymore in "All Inventory'.

For our Server and VMs only, which we installed FlexNet Agent to capture the inventory. It won't automatically delete in "All Inventory" when we decom the server, therefore, still consuming license when we check license compliance.

So in your suggestion below, we have to do the following instead of changing the status to "ignored":

1) Manually delete the discovered device record (All Discovered Devices page) then the inventory device record (All Inventory page)

Hope to hear from you soon.

Thanks,

[ChrisG]

@venus_m_concel - Yes, you have understood it right.

[marius]

Hi, @venus_m_concel ,

at least in our environment it is not possible to delete servers inventoried by FNMS agent. If we delete servers from ‘all discovered devices’ and ‘all inventory’ views, these get recreated after next inventory import / reconciliation. So we are planning to delete records manually from inventory database in order to get them removed from compliance database.

Marius. 

[mfranz]

Hi Marius,

You could add a trigger to the ComplianceComputer table to delete the data from the inventory database as well. Just be aware that an additional trigger might block the FNMS DB migration scripts and needs to be removed prior to a migration.

Best regards,

Markward

[rodris46]

I seem to recall there was a KB article on this, where is it? I can't find it out here.

So Deleting old inventory records can not be deleted in the Flexera FNMS 'All Inventory' screen and the Flexera FNMS 'All Discovered Devices'? We have a bunch of old servers in the 'All Inventory' FNMS screen that have old 'last inventory date' and we know that they have been decommissioned or retired.

Will they come back? 

[marius]

Hi,

If inventory device was created from FNMS agent inventory and you will delete records from FNMS 'All Inventory' screen and the FNMS 'All Discovered Devices' it will reappear after next inventory import. It is because device will stay in FNMS Inventory database.

I was successfully using this tool to delete old ignored inventory in FNMS. Tool is based on script retrieved through community, but I have added some additional controls to delete only ignored inventory that are not scanned for 90 days. You can find SQL code in attached script.

This script is also creating a view in FNMS reporting that I am using to delete inventory. But you can delete records directly in database.

Marius

[mrichardson]

Hi Marius,

When you delete a device in All Inventory and All Discovered Devices it actually does remove it from the Inventory database so in theory would remove it after the next import.

The issue is that Inventory record matching isn't as extensive as FNMS matching and so it was common to see duplicated records which FNMS would merge together and then removing in the UI does not remove all records on the first pass.

This should be improved going forward due to some of the improvements introduced in 2019 R2 related to the AgentID but in the interim, after deleting the record a couple of times to clear all old Inventory records it should stay deleted.

All of the above assumes that the device is no longer reporting inventory and where applicable is no longer in AD.

[DAWN]

I have a nuance here that one of you may be able to help with.

We've successfully deleted the old inventory with @ChrisG 's script and have that scheduled now. BUT - we have a few lingering computers as it appears when they were decommissioned they still had Oracle Inventory. So we have a few computers still coming in with Oracle Inventory.

The "Delete Oracle Inventory over 90s" option is checked in the settings.

Does someone have a quick script handy that will allow us to successfully drop those old instances and either add it to the script or schedule it separately.

Thanks!

[mrichardson]

Hi Dawn,

Has the Oracle inventory stopped being updated?  The Oracle instance records in Inventory database live in the ServiceProvider table which in turn has a ComputerID that would allow us to link back to the devices you're trying to remove.

If the LastInventoryDate field in dbo.ServiceProvider continues to be updated then this suggests inventory is recent and that is why it shouldn't be deleted.

If it should be removed you can simply delete the ServiceProvider records first before removing the Computer records and that should remove the Oracle instances.

[Andrei_ABB]

Hi Chris, 

I know I'm late to the party, but I found 2 different way of deleting devices and was wondering: is there any reason why we would run the scripts described by you instead of just the standard "DeleteComputer", "DeleteComputers" or "DeleteComputerByIds". Is this because at the time of writing this post, these procedures were not yet created by Flexera? Also, is there a method you would recommend over the other? (scripts described by you and the procedures I just mentioned). I feel this could help others as well as they read through old posts and find multiple ways of doing the same thing. 

Regards,

Andrei

[ChrisG]

@Andrei_ABB - are you referring to the script posted on May 7, 2019 and shown in the solution accepted on this thread? That script does in fact use the DeleteComputers stored procedure to delete computer records, so I think it aligns with the approach you're thinking of.

A primary purpose of the script as given is to identify which computer records to delete, and then call the stored procedure to delete them. If you have another way to identify records you want to delete then you can call DeleteComputer(s) to delete the records you want to remove instead of using the queries in the script.

If you are deleting a large number of records then you may find a technique like illustrated in the script to delete the records in batches (say 100 records at a time) will cause less operational impact than trying to delete them all at once.