This issue affects on-premises implementations of FlexNet Manager Suite up to and including 2018 R1.
Cleanup after imports of file evidence to the compliance database fails when operators are simultaneously making adjustments to inventory through the web interface of FlexNet Manager Suite. These transitory failures are not normally visible to you, and are normally repaired the next time that the delete can run unimpeded. If this issue occurs, the DeleteFromImportedInstalledFileEvidence
step reports the failure in C:\ProgramData\Flexera Software\Compliance\Logging\ComplianceReader\importer-[logDate]-[processID].log
on your batch server (or, in smaller implementations, the server hosting that functionality).
The ManageSoft reader is responsible for deleting records from the ImportedInstalledFileEvidence
database table after they have been imported into the compliance database. For speed, this process was using a bulk delete from the database; but table locking for manual data entry could cause this bulk delete to fail.
This fix has already been applied to the Flexera cloud instances of FlexNet Manager Suite. It is also included for on-premises customers in the 2018 R2 release of FlexNet Manager Suite. Our strong recommendation is that you upgrade to that release so that the fully-tested fixes are applied for you.
A code change to switch from the original bulk delete to a batch delete avoids the table locking problem, at the cost of slightly reduced database performance. This change is included in the on-premises 2018 R2 release of FlexNet Manager Suite. If you choose to upgrade to that release, you need take no further action. (If you are using a cloud-based instance of FlexNet Manager Suite, the change is already in production.)
If you choose to apply the following changes to your ManageSoft reader, you need:
<Reader ?>
tag and ends with a </Reader>
tag.C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Reader\ManageSoft
FileEvidence.xml
and Complete.xml
files in your text editor. In the following edits, be careful not to accidentally delete an adjacent opening or closing tag (<Reader ?>
or </Reader>
) from another element.FileEvidence.xml
file, search for the Reader
element with the attribute Name="DeleteFromImportedInstalledFileEvidence"
. Normally, this also has an attribute Order="370"
(if your version has a different value for this order attribute, note your number for reuse).Reader
element is selected, and remove that entire element.Reader
element, but replaced it with two Reader
elements. (If your replacement block is not sitting between elements with Order="360"
and Order="380"
, restore the value you noted earlier in the first, and give the second element an Order
incremented by 1.)Known issue FNMS-55210 ?File evidence is not removed after it is no longer imported from the inventory source?
ImportedInstalledFileEvidence Fix
<!--Remove any records from ImportedInstalledFileEvidence where we no longer have a record of the File Evidence being installed. We have to do a is null and match comparison on the SoftwareFilePathID as an installation may not have a File Path. Note that two delete queries are used for performance optimization of the NULL-OR condition in NOT EXISTS.--> <Reader xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="ExecuteOnTarget" Name="DeleteFromImportedInstalledFileEvidence - From DeleteComputers" Order="370" Retries="1"> <![CDATA[ -- Create temp table to store to be delete ImportedInstalledFileEvidenceRecords IF OBJECT_ID('tempdb..#ToDeleteImportedInstalledFileEvidence') IS NOT NULL DROP TABLE #ToDeleteImportedInstalledFileEvidence CREATE TABLE #ToDeleteImportedInstalledFileEvidence ( ComplianceConnectionID int, ExternalID bigint, ExternalFileID bigint, ExternalFilePathID bigint ) CREATE CLUSTERED INDEX IX_#ToDeleteImportedInstalledFileEvidence ON #ToDeleteImportedInstalledFileEvidence(ExternalFileID) -- End of temp table creation -- Insertion of to be deleted records to temp table DECLARE @ToBeDeletedImportedInstalledFileEvidenceCount as bigint = 0 INSERT INTO #ToDeleteImportedInstalledFileEvidence SELECT * FROM dbo.ImportedInstalledFileEvidence WHERE ComplianceConnectionID = @ComplianceConnectionID AND EXISTS ( SELECT 1 FROM #DeletedComputer AS relevant WHERE relevant.ExternalID = ImportedInstalledFileEvidence.ExternalID ) SET @ToBeDeletedImportedInstalledFileEvidenceCount = @ToBeDeletedImportedInstalledFileEvidenceCount + @@ROWCOUNT PRINT 'Identified ' + CAST(@ToBeDeletedImportedInstalledFileEvidenceCount AS VARCHAR) + ' ImportedInstalledFileEvidence records to be deleted' -- End of insertion of to be deleted records to temp table -- Start of of deleting DECLARE @DeletedImportedInstalledFileEvidenceCount bigint DECLARE @BatchSize int DECLARE @Rows int DECLARE @MinExternalFileID bigint IF OBJECT_ID('tempdb..#DeletedExternalFileIDs') IS NOT NULL DROP TABLE #DeletedExternalFileIDs CREATE TABLE #DeletedExternalFileIDs ( ExternalFileID bigint ) CREATE CLUSTERED INDEX IX_#DeletedExternalFileIDs ON #DeletedExternalFileIDs(ExternalFileId) SELECT TOP 1 @MinExternalFileID = ExternalFileID FROM #ToDeleteImportedInstalledFileEvidence ORDER BY ExternalFileID SET @Rows = 1 Set @DeletedImportedInstalledFileEvidenceCount = 0 SET @BatchSize = ISNULL((SELECT Value FROM dbo.DatabaseConfiguration WHERE Property = 'FileEvidenceReaderBatchSize'), 1000000); WHILE @Rows > 0 BEGIN WITH ToBeDeleted AS ( SELECT TOP(@BatchSize) iife.* FROM dbo.ImportedInstalledFileEvidence as iife WHERE iife.ExternalFileID >= @MinExternalFileID AND EXISTS ( SELECT 'x' FROM #ToDeleteImportedInstalledFileEvidence as td WHERE iife.ExternalFileID >= @MinExternalFileID AND td.ExternalFileID = iife.ExternalFileID AND td.ExternalFilePathID = iife.ExternalFilePathID AND td.ExternalID = iife.ExternalID AND td.ComplianceConnectionID = iife.ComplianceConnectionID ) ORDER BY iife.ExternalFileID ) DELETE FROM ToBeDeleted OUTPUT deleted.ExternalFileID INTO #DeletedExternalFileIDs SET @Rows = @@ROWCOUNT SET @DeletedImportedInstalledFileEvidenceCount = @DeletedImportedInstalledFileEvidenceCount + @Rows SELECT TOP 1 @MinExternalFileID = ExternalFileID FROM #DeletedExternalFileIDs ORDER BY ExternalFileID DESC TRUNCATE TABLE #DeletedExternalFileIDs END DROP TABLE #DeletedExternalFileIDs DROP TABLE #ToDeleteImportedInstalledFileEvidence PRINT N'Removed ' + CONVERT(NVARCHAR(1000), @DeletedImportedInstalledFileEvidenceCount) + N' ImportedInstalledFileEvidence records in ImportedInstalledFileEvidence staging table' ]]> </Reader> <Reader xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="ExecuteOnTarget" Name="DeleteFromImportedInstalledFileEvidence - From Deleted File Evidence" Order="371" Retries="1"> <![CDATA[ -- Create temp table to store to be delete ImportedInstalledFileEvidenceRecords IF OBJECT_ID('tempdb..#ToDeleteImportedInstalledFileEvidence') IS NOT NULL DROP TABLE #ToDeleteImportedInstalledFileEvidence CREATE TABLE #ToDeleteImportedInstalledFileEvidence ( ComplianceConnectionID int, ExternalID bigint, ExternalFileID bigint, ExternalFilePathID bigint ) CREATE CLUSTERED INDEX IX_#ToDeleteImportedInstalledFileEvidence ON #ToDeleteImportedInstalledFileEvidence(ExternalFileID) -- End of temp table creation -- Insertion of to be deleted records to temp table DECLARE @ToBeDeletedImportedInstalledFileEvidenceCount as bigint = 0 INSERT INTO #ToDeleteImportedInstalledFileEvidence SELECT ife.* FROM dbo.ImportedInstalledFileEvidence AS ife WHERE ife.ComplianceConnectionID = @ComplianceConnectionID AND EXISTS ( SELECT 1 FROM #ManageSoftComputers AS relevant WHERE relevant.ExternalID = ife.ExternalID ) AND NOT EXISTS ( SELECT 'x' FROM #SoftwareFile AS sf INNER JOIN #ManageSoftFiles AS mf ON mf.TempID = sf.TempID WHERE sf.ComputerID = ife.ExternalID AND mf.ExternalFileID = ife.ExternalFileID AND (sf.SoftwareFilePathID = ife.ExternalFilePathID OR (sf.SoftwareFilePathID IS NULL AND ife.ExternalFilePathID IS NULL)) ) SET @ToBeDeletedImportedInstalledFileEvidenceCount = @ToBeDeletedImportedInstalledFileEvidenceCount + @@ROWCOUNT PRINT 'Identified ' + CAST(@ToBeDeletedImportedInstalledFileEvidenceCount AS VARCHAR) + ' ImportedInstalledFileEvidence records to be deleted' -- End of insertion of to be deleted records to temp table -- Start of of deleting DECLARE @DeletedImportedInstalledFileEvidenceCount bigint DECLARE @BatchSize int Declare @Rows int DECLARE @MinExternalFileID bigint IF OBJECT_ID('tempdb..#DeletedExternalFileIDs') IS NOT NULL DROP TABLE #DeletedExternalFileIDs CREATE TABLE #DeletedExternalFileIDs ( ExternalFileID bigint ) CREATE CLUSTERED INDEX IX_#DeletedExternalFileIDs ON #DeletedExternalFileIDs(ExternalFileId) SELECT TOP 1 @MinExternalFileID = ExternalFileID FROM #ToDeleteImportedInstalledFileEvidence ORDER BY ExternalFileID SET @Rows = 1 Set @DeletedImportedInstalledFileEvidenceCount = 0 SET @BatchSize = ISNULL((SELECT Value FROM dbo.DatabaseConfiguration WHERE Property = 'FileEvidenceReaderBatchSize'), 1000000); WHILE @Rows > 0 BEGIN WITH ToBeDeleted AS ( SELECT TOP(@BatchSize) iife.* FROM dbo.ImportedInstalledFileEvidence as iife WHERE iife.ExternalFileID >= @MinExternalFileID AND EXISTS ( SELECT 'x' FROM #ToDeleteImportedInstalledFileEvidence as td WHERE iife.ExternalFileID >= @MinExternalFileID AND td.ExternalFileID = iife.ExternalFileID AND td.ExternalFilePathID = iife.ExternalFilePathID AND td.ExternalID = iife.ExternalID AND td.ComplianceConnectionID = iife.ComplianceConnectionID ) ORDER BY iife.ExternalFileID ) DELETE FROM ToBeDeleted OUTPUT deleted.ExternalFileID INTO #DeletedExternalFileIDs SET @Rows = @@ROWCOUNT SET @DeletedImportedInstalledFileEvidenceCount = @DeletedImportedInstalledFileEvidenceCount + @Rows SELECT TOP 1 @MinExternalFileID = ExternalFileID FROM #DeletedExternalFileIDs ORDER BY ExternalFileID DESC TRUNCATE TABLE #DeletedExternalFileIDs END DROP TABLE #DeletedExternalFileIDs DROP TABLE #ToDeleteImportedInstalledFileEvidence PRINT N'Removed ' + CONVERT(NVARCHAR(1000), @DeletedImportedInstalledFileEvidenceCount) + N' ImportedInstalledFileEvidence records in ImportedInstalledFileEvidence staging table' ]]> </Reader>
Dec 13, 2018 06:04 PM