Obsolete Oracle instances may not be automatically cleaned if duplicate record exists in Flexera Inventory database

[#1]

Summary

This article describes a known issue that obsolete oracle instances may not be automatically cleaned in FNMSInvenotry database.

Symptoms

ServiceProviderApplicationUserOracle table keeps growing and the size doesn't shrink with the database maintenance task.

Cause

The ServiceProviderDeleteStalePerTenant procedure which is run during the IM data maintenance task doesn't cater for devices where duplicate inventory records have been created. Since this procedure relies on an updated HWDate or ServicesDate in the InventoryReport table, if a new computer record is created these will not be updated and the instance will never be deleted. The devices often end up being merged together by the matchers during the reconcile. This can result in incorrect Oracle option usage and also leftover instances which have actually been uninstalled.

Resolution

This issue is fixed in FNMS 2019 R1.

[rodris46]

1. How do we know or where do we check to see IF we have devices where duplicate inventory records have been created???? Please be specific.

2. Where would see devices being merged together?

3. Where would the incorrect Oracle Option usage be displayed for devices that have actually been uninstalled?

4. Since we are on Flexera FNMS 2018 R2, what do we do to fix this problem in case we are experiencing this problem?

[shirase]

Hi @rodris46 

1) If you see duplicate records for a ComputerID from one of the following queries, then you are hitting this.

select *
FROM ServiceProvider AS sp
INNER JOIN InventoryReport AS ir
ON sp.ComputerID = ir.ComputerID
INNER JOIN ServiceProviderType AS spt
ON sp.ServiceProviderTypeID = spt.ServiceProviderTypeID
WHERE ( sp.ServiceProviderTypeID IN (2,3) OR spt.Type = 'EnterpriseBusinessServer' )
AND sp.ParentServiceProviderID IS NOT NULL

select *
FROM ServiceProvider AS sp
INNER JOIN InventoryReport AS ir
ON sp.ComputerID = ir.ComputerID
INNER JOIN ServiceProviderType AS spt
ON sp.ServiceProviderTypeID = spt.ServiceProviderTypeID
WHERE ( sp.ServiceProviderTypeID IN (2,3) OR spt.Type = 'EnterpriseBusinessServer' )
AND sp.ParentServiceProviderID IS NULL
AND NOT EXISTS
(SELECT DISTINCT sp2.ParentServiceProviderID
FROM ServiceProvider AS sp2
WHERE sp2.ParentServiceProviderID IS NOT NULL
AND sp2.ParentServiceProviderID = sp.ServiceProviderID)

2) FNMS shows inventory properties which are "merged" on the web UI. So, even newer inventory doesn't report a specific application, FNMS web UI shows the application if old inventory from the same machine reports the application.

3) Please navigate to "Oracle Instances" page on the FNMS web UI and you will see links to "Oracle Instance Properties" page for each instance. There is the "Options" tab on the page.

4) The fix is only available for FNMS 2019 R1 or later.   A workaround is to delete computers with duplicate inventory records with DeleteComputer stored procedure in the FNMS inventory database.  (e.g DeleteComputer <ComputerID>)

Regards,

Soya