‎May 07, 2019 02:18 AM
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
‎May 07, 2019 06:20 AM - edited ‎May 28, 2019 04:32 AM
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.
‎Jan 24, 2022 12:01 AM
Hi Marius,
Depending on your data and processes, there are multiple things to consider.
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
‎May 07, 2019 02:38 AM
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.
‎May 07, 2019 05:40 AM
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
‎May 07, 2019 06:20 AM - edited ‎May 28, 2019 04:32 AM
@ChrisG is correct, there is no automated solution for removing old devices apart from:
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.
‎May 08, 2019 12:08 PM
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?
‎Jul 26, 2019 08:33 AM
@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
‎Jul 26, 2019 08:41 AM
Thank you. That was the problem. I was looking at the Compliance database instead of the Inventory database.
‎Jul 26, 2019 08:51 AM
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,
‎Aug 03, 2019 09:57 AM
@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:
(*) 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.
‎Aug 06, 2019 11:13 PM
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,
‎Aug 06, 2019 11:26 PM
‎Aug 07, 2019 12:10 AM
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.
‎Aug 08, 2019 11:38 PM
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
‎Aug 12, 2019 09:30 AM
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?
‎Nov 12, 2019 12:46 PM
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
‎Nov 12, 2019 11:17 PM
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.
‎Nov 15, 2019 05:09 AM
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!
‎Jan 16, 2020 09:25 AM
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.
‎Jan 16, 2020 10:17 AM
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
‎Apr 20, 2021 04:03 AM
@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.
‎Apr 20, 2021 04:15 AM