cancel
Showing results for 
Search instead for 
Did you mean: 

Improving database cleanup after importing file evidence in FlexNet Manager Suite

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 from the ImportedFileEvidence table when a matching record is no longer used in the ImportedInstalledFileEvidence table may fail, leaving an 'orphaned' record in the ImportedFileEvidence table (an orphaned record is one that is no longer matched by one in the ImportedInstalledFileEvidence table). These failures are only visible through database inspection. If significant numbers of these accumulate over time, database performance can be degraded.

Cause

The ManageSoft reader is responsible for this cleanup, but sometimes fails at this task.

Resolution

The fix described in this knowledge article has 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.

Records in the ImportedFileEvidence table for which there is no longer a matching record in the ImportedInstalledFileEvidence table should be deleted. A suitable 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.)

The changed code means that the next run of the ManageSoft reader takes longer as it deletes orphaned ImportedFileEvidence records that should have been previously removed. Thereafter, database performance for file evidence import is improved, as there is a smaller set of data to manipulate.


Workaround

Choose one of the following workarounds:

  • 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.
  4. In the Complete.xml file, carefully place your cursor immediately after the last Reader element, and insert the code block provided under the "ImportedFileEvidence Fix" heading in the "Additional Information" section below. This should now be the last element block in the file. Make sure that the "Order=" tag has a value higher than the previous Reader block.
  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.)

Additional Information

ImportedFileEvidence Fix

<!-- FNMS 55210 -->
<Reader xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
	xsi:type="ExecuteOnTarget"
	Name="DeleteFromImportedFileEvidence"
	Order="30"
	Retries="1">
	<![CDATA[	
				
		IF EXISTS (SELECT 'X'
			FROM dbo.ComplianceTenantSetting cts
			WHERE cts.SettingNameID = 195 AND cts.SettingValue = '1' ) 
		BEGIN
				
			-- Creating a temp table for all the ExternalFileId's to be deleted to prevent locking
			IF OBJECT_ID('tempdb..#ToDeleteExternalFileIDs') IS NOT NULL
				DROP TABLE #ToDeleteExternalFileIDs
			CREATE TABLE #ToDeleteExternalFileIDs 
			( 
				ExternalFileId bigint
			)
					
			CREATE CLUSTERED INDEX IX_#ToDeleteExternalFileIDs ON #ToDeleteExternalFileIDs(ExternalFileId)

			INSERT INTO #ToDeleteExternalFileIDs (ExternalFileId)
			SELECT ife.ExternalFileID
			FROM dbo.ImportedFileEvidence AS ife
			WHERE ife.ComplianceConnectionID = @ComplianceConnectionId 
				AND NOT EXISTS (
					SELECT 'x'
					FROM dbo.ImportedInstalledFileEvidence AS iife
					WHERE iife.ExternalFileID = ife.ExternalFileID
						AND iife.ComplianceConnectionID = @ComplianceConnectionID
				) AND NOT EXISTS (
					SELECT 'x'
					FROM dbo.ImportedInstalledFileEvidenceUsage AS iifeu
					WHERE iifeu.ExternalFileID = ife.ExternalFileID
						AND iifeu.ComplianceConnectionID = @ComplianceConnectionID
				)

			PRINT 'Identified ' + CAST(@@ROWCOUNT AS VARCHAR) + ' ImportedFileEvidence records to be deleted'
			-- End of temp table creation

			DECLARE @DeletedImportedFileEvidenceCount bigint
			DECLARE @BatchSize int
			DECLARE @Rows int
			DECLARE @MinExternalFileID bigint

			IF OBJECT_ID('tempdb..#DeletedIDs') IS NOT NULL
				DROP TABLE #DeletedIDs

			CREATE TABLE #DeletedIDs
			(
				ExternalFileID bigint
			)
			
			CREATE CLUSTERED INDEX IX_#DeletedIDs ON #DeletedIDs(ExternalFileId)

			SELECT TOP 1 @MinExternalFileID = ExternalFileID FROM #ToDeleteExternalFileIDs ORDER BY ExternalFileID

			SET @Rows = 1
			Set @DeletedImportedFileEvidenceCount = 0
			SET @BatchSize = ISNULL((SELECT Value FROM dbo.DatabaseConfiguration WHERE Property = 'FileEvidenceReaderBatchSize'), 1000000);

			WHILE @Rows > 0 
			BEGIN
				WITH ToBeDeleted AS (
					SELECT TOP(@BatchSize) ife.*
					FROM dbo.ImportedFileEvidence AS ife
					WHERE ife.ExternalFileID >= @MinExternalFileID
						AND ife.ComplianceConnectionID = @ComplianceConnectionId
						AND ife.ExternalFileID IN (
							SELECT td.ExternalFileId
							FROM #ToDeleteExternalFileIDs AS td
							WHERE td.ExternalFileID >= @MinExternalFileID
						)
				)
				DELETE FROM ToBeDeleted
				OUTPUT deleted.ExternalFileID INTO #DeletedIDs

				SET @Rows = @@ROWCOUNT
				SET @DeletedImportedFileEvidenceCount = @DeletedImportedFileEvidenceCount + @Rows

				SELECT TOP 1 @MinExternalFileID = ExternalFileID FROM #DeletedIDs ORDER BY ExternalFileID DESC

				TRUNCATE TABLE #DeletedIDs
			END

			DROP TABLE #DeletedIDs
			DROP TABLE #ToDeleteExternalFileIDs

			PRINT N'Removed ' + CONVERT(NVARCHAR(1000), @DeletedImportedFileEvidenceCount) + N' ImportedFileEvidence records in ImportedFileEvidence staging table'
					
		END

	]]>	
</Reader>

Related Documents

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