This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Improving database cleanup after importing file evidence in FlexNet Manager Suite

Summary

This issue affects on-premises implementations of FlexNet Manager Suite up to and including 2018 R1.

Symptoms

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


Cause

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.


Resolution

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


Workaround

Choose one of the following workarounds:

  • Schedule your inventory imports to run overnight, when operators are unlikely to be making adjustments.
  • Upgrade to the 2018 R2 release of FlexNet Manager Suite (or a later version).
  • Apply the code changes described below.

Common Prerequisites

If you choose to apply the following changes to your ManageSoft reader, you need:

  • A flat text editor (or XML editor) of your choice
  • Basic understanding of XML
  • Basic understanding of your installation of FlexNet Manager Suite
  • Basic understanding of how importer procedures work:
    • Importer procedures run in blocks loaded from XML files. They run sequentially on the same source type.
    • In the reader XML files, each block start with a <Reader ?> tag and ends with a </Reader> tag.

Instructions

  1. Log in as an administrator to your FlexNet Manager Suite batch server. (In smaller implementations, choose the server that fulfils this function, such as your processing server or your application server.)
  2. Navigate to the folder containing your Managesoft readers import procedures. By default, they are located in C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Reader\ManageSoft
  3. Open the 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.
    Tip: Make a backup copy to preserve the original in case you need to recover from your changes.
    1. In the 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).
    2. Ensure that exactly one Reader element is selected, and remove that entire element.
    3. Replace that block with the code block under the "ImportedInstalledFileEvidence Fix" heading in the "Additional Information" section below. Notice that you removed a single 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.)
  5. Carefully check your change(s), and when satisfied, save the modified ManageSoft reader file with its original name in its original location.
  6. Your changes are exercised at the next full import (normally associated with the license compliance calculations overnight). You may either:
    1. Wait until tomorrow morning to check your results, or
    2. Navigate in the web interface to License Compliance > Reconcile, select Update inventory for reconciliation, and click Reconcile. See the online help for more details. (These controls are available only to operators in an Administrator role.)

Related Documents

Known issue FNMS-55210 ?File evidence is not removed after it is no longer imported from the inventory source?


Additional Information

Code blocks

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

Labels:
Was this article helpful? Yes No
No ratings
0 Kudos
Version history
Last update:
‎Dec 13, 2018 06:04 PM
Updated by: