cancel
Showing results for 
Search instead for 
Did you mean: 
marius
Active participant

Deleting old inventory records

Jump to solution
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.
0 Kudos
1 Solution

Accepted Solutions
Community Manager ChrisG Community Manager
Community Manager

Re: Deleting old inventory records

Jump to 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

 

(Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply answers a question you have raised, please click "ACCEPT AS SOLUTION".)
4 Replies
mfranz
Consultant

Re: Deleting old inventory records

Jump to solution

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
Active participant

Re: Deleting old inventory records

Jump to solution

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. 

 

0 Kudos
Community Manager ChrisG Community Manager
Community Manager

Re: Deleting old inventory records

Jump to 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

 

(Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply answers a question you have raised, please click "ACCEPT AS SOLUTION".)
Highlighted
Flexera mrichardson
Flexera

Re: Deleting old inventory records

Jump to solution

@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.

(Anything expressed here is my own view and not necessarily that of my employer, Flexera)