0_marius asked a question.
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
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.
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 DATESET @OldestDataDate = DATEADD(d, -90, GETDATE())SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSET DEADLOCK_PRIORITY LOWDECLARE @TenantID INTDECLARE @TenantName NVARCHAR(256)DECLARE c CURSOR FOR SELECT TenantID, TenantName FROM dbo.TenantOPEN cFETCH NEXT FROM c INTO @TenantID, @TenantNameWHILE @@FETCH_STATUS = 0BEGINPRINT N'Deleting inactive computers in tenant ' + @TenantNameEXEC dbo.SetTenantID @TenantIDWHILE 1 = 1BEGINIF OBJECT_ID('tempdb..Computer') IS NOT NULLDROP TABLE ComputerSELECT TOP 100 c.ComputerID -- Delete in small batches to avoid locking too much data for too longINTO ComputerFROM dbo.Computer cLEFT OUTER JOIN dbo.ComputerResourceData AS crd ON crd.ComputerUID = c.ComputerUIDWHERE(-- Computer is not in Active Directoryc.GUID IS NULL-- Or some data has been received about the computerOR crd.ComputerResourceID IS NOT NULLOR 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 oldAND (crd.ComputerUID IS NULL OR NOT(crd.LastUpdated >= @OldestDataDate))AND NOT EXISTS(SELECT 1FROM dbo.InventoryReport inrWHERE inr.ComputerID = c.ComputerIDAND (HWDate >= @OldestDataDateOR SWDate >= @OldestDataDateOR FilesDate >= @OldestDataDateOR ServicesDate >= @OldestDataDateOR VMwareServicesDate >= @OldestDataDateOR OVMMDate >= @OldestDataDateOR AccessDate >= @OldestDataDate))AND NOT EXISTS(SELECT 1FROM dbo.ServiceProvider spWHERE sp.ComputerID = c.ComputerIDAND LastInventoryDate >= @OldestDataDate)AND NOT EXISTS(SELECT 1FROM dbo.Installation iWHERE i.ComputerID = c.ComputerIDAND i.OrganizationID = c.ComputerOUIDAND Received >= @OldestDataDate)AND NOT EXISTS(SELECT 1FROM dbo.ComputerUsage cuWHERE cu.ComputerID = c.ComputerIDAND LastReported >= @OldestDataDate)DECLARE @Count INTSET @Count = @@ROWCOUNTIF @Count = 0BREAKPRINT 'Deleting ' + CAST(@Count AS VARCHAR) + ' computers'EXEC dbo.DeleteComputersENDPRINT 'No more computers found to delete'FETCH NEXT FROM c INTO @TenantID, @TenantNameENDCLOSE cDEALLOCATE cIf 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 DATESET @OldestDataDate = DATEADD(d, -90, GETDATE())SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSET DEADLOCK_PRIORITY LOWDECLARE @TenantID INTDECLARE @TenantName NVARCHAR(256)DECLARE c CURSOR FOR SELECT TenantID, TenantName FROM dbo.TenantOPEN cFETCH NEXT FROM c INTO @TenantID, @TenantNameWHILE @@FETCH_STATUS = 0BEGINPRINT N'Deleting inactive computers in tenant ' + @TenantNameEXEC dbo.SetTenantID @TenantIDWHILE 1 = 1BEGINIF OBJECT_ID('tempdb..Computer') IS NOT NULLDROP TABLE ComputerSELECT TOP 100 c.ComputerID -- Delete in small batches to avoid locking too much data for too longINTO ComputerFROM dbo.Computer cWHERE(-- Computer is not in Active Directoryc.GUID IS NULL-- Or some data has been received about the computerOR 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 oldAND NOT EXISTS(SELECT 1FROM dbo.InventoryReport inrWHERE inr.ComputerID = c.ComputerIDAND (HWDate >= @OldestDataDateOR SWDate >= @OldestDataDateOR FilesDate >= @OldestDataDateOR ServicesDate >= @OldestDataDateOR VMwareServicesDate >= @OldestDataDateOR OVMMDate >= @OldestDataDateOR AccessDate >= @OldestDataDate))AND NOT EXISTS(SELECT 1FROM dbo.ServiceProvider spWHERE sp.ComputerID = c.ComputerIDAND LastInventoryDate >= @OldestDataDate)AND NOT EXISTS(SELECT 1FROM dbo.Installation iWHERE i.ComputerID = c.ComputerIDAND i.OrganizationID = c.ComputerOUIDAND Received >= @OldestDataDate)AND NOT EXISTS(SELECT 1FROM dbo.ComputerUsage cuWHERE cu.ComputerID = c.ComputerIDAND LastReported >= @OldestDataDate)DECLARE @Count INTSET @Count = @@ROWCOUNTIF @Count = 0BREAKPRINT 'Deleting ' + CAST(@Count AS VARCHAR) + ' computers'EXEC dbo.DeleteComputersENDPRINT 'No more computers found to delete'FETCH NEXT FROM c INTO @TenantID, @TenantNameENDCLOSE cDEALLOCATE c
@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.
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?
@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
Thank you. That was the problem. I was looking at the Compliance database instead of the Inventory database.
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,
@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.
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,
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 DATESET @OldestDataDate = DATEADD(d, -90, GETDATE())SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSET DEADLOCK_PRIORITY LOWDECLARE @TenantID INTDECLARE @TenantName NVARCHAR(256)DECLARE c CURSOR FOR SELECT TenantID, TenantName FROM dbo.TenantOPEN cFETCH NEXT FROM c INTO @TenantID, @TenantNameWHILE @@FETCH_STATUS = 0BEGINPRINT N'Deleting inactive computers in tenant ' + @TenantNameEXEC dbo.SetTenantID @TenantIDWHILE 1 = 1BEGINIF OBJECT_ID('tempdb..Computer') IS NOT NULLDROP TABLE ComputerSELECT TOP 100 c.ComputerID -- Delete in small batches to avoid locking too much data for too longINTO ComputerFROM dbo.Computer cLEFT OUTER JOIN dbo.ComputerResourceData AS crd ON crd.ComputerUID = c.ComputerUIDWHERE(-- Computer is not in Active Directoryc.GUID IS NULL-- Or some data has been received about the computerOR crd.ComputerResourceID IS NOT NULLOR 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 oldAND (crd.ComputerUID IS NULL OR NOT(crd.LastUpdated >= @OldestDataDate))AND NOT EXISTS(SELECT 1FROM dbo.InventoryReport inrWHERE inr.ComputerID = c.ComputerIDAND (HWDate >= @OldestDataDateOR SWDate >= @OldestDataDateOR FilesDate >= @OldestDataDateOR ServicesDate >= @OldestDataDateOR VMwareServicesDate >= @OldestDataDateOR OVMMDate >= @OldestDataDateOR AccessDate >= @OldestDataDate))AND NOT EXISTS(SELECT 1FROM dbo.ServiceProvider spWHERE sp.ComputerID = c.ComputerIDAND LastInventoryDate >= @OldestDataDate)AND NOT EXISTS(SELECT 1FROM dbo.Installation iWHERE i.ComputerID = c.ComputerIDAND i.OrganizationID = c.ComputerOUIDAND Received >= @OldestDataDate)AND NOT EXISTS(SELECT 1FROM dbo.ComputerUsage cuWHERE cu.ComputerID = c.ComputerIDAND LastReported >= @OldestDataDate)DECLARE @Count INTSET @Count = @@ROWCOUNTIF @Count = 0BREAKPRINT 'Deleting ' + CAST(@Count AS VARCHAR) + ' computers'EXEC dbo.DeleteComputersENDPRINT 'No more computers found to delete'FETCH NEXT FROM c INTO @TenantID, @TenantNameENDCLOSE cDEALLOCATE cIf 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 DATESET @OldestDataDate = DATEADD(d, -90, GETDATE())SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSET DEADLOCK_PRIORITY LOWDECLARE @TenantID INTDECLARE @TenantName NVARCHAR(256)DECLARE c CURSOR FOR SELECT TenantID, TenantName FROM dbo.TenantOPEN cFETCH NEXT FROM c INTO @TenantID, @TenantNameWHILE @@FETCH_STATUS = 0BEGINPRINT N'Deleting inactive computers in tenant ' + @TenantNameEXEC dbo.SetTenantID @TenantIDWHILE 1 = 1BEGINIF OBJECT_ID('tempdb..Computer') IS NOT NULLDROP TABLE ComputerSELECT TOP 100 c.ComputerID -- Delete in small batches to avoid locking too much data for too longINTO ComputerFROM dbo.Computer cWHERE(-- Computer is not in Active Directoryc.GUID IS NULL-- Or some data has been received about the computerOR 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 oldAND NOT EXISTS(SELECT 1FROM dbo.InventoryReport inrWHERE inr.ComputerID = c.ComputerIDAND (HWDate >= @OldestDataDateOR SWDate >= @OldestDataDateOR FilesDate >= @OldestDataDateOR ServicesDate >= @OldestDataDateOR VMwareServicesDate >= @OldestDataDateOR OVMMDate >= @OldestDataDateOR AccessDate >= @OldestDataDate))AND NOT EXISTS(SELECT 1FROM dbo.ServiceProvider spWHERE sp.ComputerID = c.ComputerIDAND LastInventoryDate >= @OldestDataDate)AND NOT EXISTS(SELECT 1FROM dbo.Installation iWHERE i.ComputerID = c.ComputerIDAND i.OrganizationID = c.ComputerOUIDAND Received >= @OldestDataDate)AND NOT EXISTS(SELECT 1FROM dbo.ComputerUsage cuWHERE cu.ComputerID = c.ComputerIDAND LastReported >= @OldestDataDate)DECLARE @Count INTSET @Count = @@ROWCOUNTIF @Count = 0BREAKPRINT 'Deleting ' + CAST(@Count AS VARCHAR) + ' computers'EXEC dbo.DeleteComputersENDPRINT 'No more computers found to delete'FETCH NEXT FROM c INTO @TenantID, @TenantNameENDCLOSE cDEALLOCATE c