Known Issue: License reconciliation failure: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SoftwareLicenseGroupPointsConsumedData_GroupEx" (IOJ-2078225)

[#1]

Symptoms

License reconciliation fails with an error similar to the following appearing in the importer log:

2021-10-01 09:58:10,634 [INFO ]       Calculates and updates the group's counts and rolled-up values.
2021-10-01 09:58:16,743 [ERROR]       System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SoftwareLicenseGroupPointsConsumedData_GroupEx". The conflict occurred in database "FNMSCompliance", table "dbo.GroupSnapshot_MT".
The statement has been terminated.

Details

This error may occur if details of enterprise group (location, corporate unit or cost center) structures in the database have become inconsistent, such as when a parent group is deleted but details of child groups are not deleted. While this should not generally happen, it has been observed to occur from time to time, particularly in situations where direct manipulation of data in the database has occurred.

Troubleshooting

Troubleshooting this issue generally involves digging into details of enterprise group structures that are recorded in the database and looking for inconsistencies that can then be corrected.

Here is an example query that can be executed against the compliance database of a FlexNet Manager Suite On Premises system to identify unexpected references to child groups where a parent group has been deleted.

SELECT *
FROM dbo.GroupEx g
    CROSS APPLY dbo.GroupEx_GetParentGroups(g.GroupExID) AS pg
WHERE pg.GroupExID NOT IN (SELECT GroupExID FROM dbo.GroupEx)

And here are some example queries to identify records of various types which have been left with references groups that have been deleted:

-- Check group references from Asset records
SELECT 'Asset', AssetID, ShortDescription, BusinessUnitID
FROM dbo.Asset
WHERE BusinessUnitID NOT IN (SELECT GroupExID FROM dbo.GroupEx)

SELECT 'Asset', AssetID, ShortDescription, CostCenterID
FROM dbo.Asset
WHERE CostCenterID NOT IN (SELECT GroupExID FROM dbo.GroupEx)

SELECT 'Asset', AssetID, ShortDescription, LocationID
FROM dbo.Asset
WHERE LocationID NOT IN (SELECT GroupExID FROM dbo.GroupEx)


-- Check group references from Computer records
SELECT 'Computer', ComplianceComputerID, ComputerName, BusinessUnitID
FROM dbo.ComplianceComputer
WHERE BusinessUnitID NOT IN (SELECT GroupExID FROM dbo.GroupEx)

SELECT 'Computer', ComplianceComputerID, ComputerName, CostCenterID
FROM dbo.ComplianceComputer
WHERE CostCenterID NOT IN (SELECT GroupExID FROM dbo.GroupEx)

SELECT 'Computer', ComplianceComputerID, ComputerName, LocationID
FROM dbo.ComplianceComputer
WHERE LocationID NOT IN (SELECT GroupExID FROM dbo.GroupEx)


-- Check group references from User records
SELECT 'User', ComplianceUserID, UserName, BusinessUnitID
FROM dbo.ComplianceUser
WHERE BusinessUnitID NOT IN (SELECT GroupExID FROM dbo.GroupEx)

SELECT 'User', ComplianceUserID, UserName, CostCenterID
FROM dbo.ComplianceUser
WHERE CostCenterID NOT IN (SELECT GroupExID FROM dbo.GroupEx)

SELECT 'User', ComplianceUserID, UserName, LocationID
FROM dbo.ComplianceUser
WHERE LocationID NOT IN (SELECT GroupExID FROM dbo.GroupEx)


-- Check group references from SoftwareLicense records
SELECT 'SoftwareLicense', SoftwareLicenseID, Name, BusinessUnitID
FROM dbo.SoftwareLicense
WHERE BusinessUnitID NOT IN (SELECT GroupExID FROM dbo.GroupEx)

SELECT 'SoftwareLicense', SoftwareLicenseID, Name, CostCenterID
FROM dbo.SoftwareLicense
WHERE CostCenterID NOT IN (SELECT GroupExID FROM dbo.GroupEx)

SELECT 'SoftwareLicense', SoftwareLicenseID, Name, LocationID
FROM dbo.SoftwareLicense
WHERE LocationID NOT IN (SELECT GroupExID FROM dbo.GroupEx)


-- Check group references from SoftwareLicenseScoping records
SELECT 'SoftwareLicenseScoping', SoftwareLicenseID, GroupExID
FROM dbo.SoftwareLicenseScoping
WHERE GroupExID NOT IN (SELECT GroupExID FROM dbo.GroupEx)

If the above queries return any records then that suggests group details stored in the database are inconsistent and may need remediation.

Note: The above queries refer to the main record types used in the license reconciliation process. However there are references to groups in many other database tables which are not covered by these examples.

Assistance from Flexera Support will be required to execute queries such as those shown above when working with Flexera One ITAM.

Fix status

This issue has been fixed in the following FlexNet Manager Suite releases: 2022 R2.5 / May 2023 (Cloud), 2023 R1 (On Premises)

Other information

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

Master issue ID: IOJ-2078225

Also known as: ITAM-412

[mfranz]

I had this error, but the code above did not help. I can only assume that some GroupEx was linked to an item not mentioned above. I removed the GroupEx elements without parents using this code:

DELETE
FROM GroupEx_T
WHERE ParentGroupExID NOT IN (
	SELECT GroupExID
	FROM GroupEx
	)

[mfranz]

Important update: You may have to run this also for LocationIDs, CostCenterIDs and maybe eben CategoryIDs, if somehow the GroupEx structure got compromised. Also there are 57 individual fields in the ComplianceDB referring to the GroupExID, so there might be more issues then the code above will reveal.

I forwarded the additional script and a list of GroupExIDs FKs to support, so maybe someone can incorporate this into this article. Thanks.