License reconciliation failure: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SoftwareLicenseGroupPointsConsumedData_GroupEx"

Description

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 releases: 2022 R2.5 / May 2023 (Cloud), 2023 R1 (On Premises).

Was this helpful?

Support Hub

Product

Flexera One

Deployment type

Cloud

Fixed in

2022 R2.5 / May 2023 (Cloud),2023 R1 (On Premises)

Visibility

Guest

URL Name

license-reconciliation-failure-the-insert-statement-conflicted-with-the-foreign-key-constraint-fk-softwarelicensegrouppointsconsumeddata-groupex-IOJ-2078225

Are you sure you want to cancel the case creation?

Case closed successfully

Action performed successfully

Upload Attachment

Maximum file size allowed is 3 MB.

File type not supported.

Supported file types:

Documents (.txt, .doc, .docx, .pdf), Images (.jpg, .png), Comma Separated Files (.csv) Speadsheets (.xlsx, .xls)

Are you sure you want to close this case

Get Hotline support

Products	Region	Phone Numbers
FlexNet Operations FlexNet Embedded FlexNet Publisher FlexNet Connect FlexNet Code Insight InstallAnywhere InstallShield	North America *	+1 630-332-2513 (toll) +1 877-279-2853 (toll-free in North America)
	Europe *	+44 1925 944367 (toll) +44 800 047 8642 (toll-free in Europe)
	Japan *	+81 3-4540-5335 (select option 2)
	Australia *	+61 3 9895 2177 +61 1800 560 603 (toll-free in Australia)
Usage Intelligence (formerly Revulytics) Compliance Intelligence	Please use the Case Portal to submit your support ticket or reach out to your Revenera contact.

Symptoms

Details

Troubleshooting

Related Articles

Please wait...

Case id: 00001065