Known Issue: "Create license allocations from ImportedSoftwareLicenseAllocations" import step may fail with "Violation of UNIQUE KEY constraint 'UQ_SoftwareLicenseAllocation'" error after importing Office 365 data if a user is linked to multiple imported users (IOJ-1818616)

[#1]

Symptoms

After importing data from Office 365, the import “writers” process fails with logging similar to the following:

2018-08-31 00:33:35,559 [INFO ]       Failed to execute Writer 'Create license allocations from ImportedSoftwareLicenseAllocations' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\LicenseCreateUpdate.xml
Error: Violation of UNIQUE KEY constraint 'UQ_SoftwareLicenseAllocation'. Cannot insert duplicate key in object 'dbo.SoftwareLicenseAllocation_MT'. The duplicate key value is (293, <NULL>, 8391, <NULL>, <NULL>, <NULL>, <NULL>).
The statement has been terminated.
Added 0 new user allocation(s) from the source database connections.

Cause

This issue can be caused by the query logic used in the “Create license allocations from ImportedSoftwareLicenseAllocations” procedure being unable to handle a situation where a user record is linked to multiple imported user details.

Mitigation

1. Make a backup copy of the following file on the batch server: C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\LicenseCreateUpdate.xml.
2. Edit the LicenseCreateUpdate.xml file in a text editor, and locate the “Create license allocations from ImportedSoftwareLicenseAllocations” writer in that file.
3. Add the “DISTINCT” keyword to the line “SELECT sl.SoftwareLicenseID” as shown towards the end of the following extract:

   <Writer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
   		Name="Create license allocations from ImportedSoftwareLicenseAllocations" 
   		Order="1180"
   		Retries="1">
   		<![CDATA[
   			INSERT INTO dbo.SoftwareLicenseAllocation
   			(
   				SoftwareLicenseID,
   				ComplianceUserID,
   				NumberAllocated,
   				SoftwareLicenseAllocationStatusID
   			)
   			SELECT	DISTINCT sl.SoftwareLicenseID, 
   					cu.ComplianceUserID,
   					1,
   					3 -- Permanent
   			FROM dbo.ImportedSoftwareLicenseAllocation isla

Additional notes

This particular issue has been fixed in FlexNet Manager Suite releases noted in the “Fix status” section. There may be other root causes that result in similar symptoms affecting more recent FlexNet Manager Suite releases. See the following article for details: Known Issue: "Create license allocations from ImportedSoftwareLicenseAllocations" import step may fail with error: Violation of UNIQUE KEY constraint 'UQ_SoftwareLicenseAllocation'. Cannot insert duplicate key in object 'dbo.SoftwareLicenseAllocation_MT'. (IOK-931590)

Fix status

This issue has been fixed in the following FlexNet Manager Suite releases: 2017 R2 (On Premises), 2017 R2 / Aug 2017 (Cloud)

Other information

Affected components: Inventory import (read/write/export)

Master issue ID: IOJ-1818616

Also known as: FNMS-44380