Inventory NDI files fail to import and are saved in BadLogs folder on the inventory server if SoftwareFile_MT.SoftwareFileID values in the inventory database exceed 2,147,483,647

[#1]

Symptoms

Inventory NDI fail to be imported, and are saved to "C:\ProgramData\Flexera Software\Incoming\Inventories\BadLogs\failure" on the inventory server.

Attempting to run mgsimport manually to import an inventory file outputs a message like the following:

MachingName at 20180319T103702 (Full).ndi.gz:
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.

Cause

When the max range for the INT value used in the SoftwareFile_MT table for the SoftwareFileID is reached the inventories will go into a bad logs folder and not processed into the inventory database.

Steps To Reproduce

• The INT value for SoftwareFileID in the table SoftwareFile needs to be reached, the value is 2,147,483,647, the table can contain less records.
• Place a sample ndi file into the Incoming\inventories folder
• Run the following command to manually import inventory files:

  mgsimport.exe -t inventories -e

• The error above will be seen on the command line and the inventory file will go into the bad logs folder.

Resolution

This issue has been resolved with the release of FlexNet Manager Suite 2020 R1.

Workaround

The following steps can be used to workaround the issue:

• Backup the current Inventory Manager database
• Ensure there are no current imports running
• Run the following query on the inventory database:
  

  DBCC CHECKIDENT('[dbo].[SoftwareFile_MT]', RESEED, -2147483648)​

• Attempt to import a new inventory file from the command line using the steps above, this should completed

Additional Information

The cause of the issue could be a large amount of file evidence imported into the system, the following query can be used to locate file paths that contain a large amount of files. If these are shared drives for example and exclusion rule can be setup to stop the scanning of these directories as they are not used for compliance reasons.

SELECT SUBSTRING(FileName,1,(CHARINDEX('/',FileName,2))) AS RootPath, count(FileName) AS NoOfRecords
FROM ImportedFileEvidence
WHERE FileName LIKE '/%'
    AND [FileName] NOT IN (
        SELECT [Filename]
        FROM FileEvidence fe
            INNER JOIN SoftwareTitleFileEvidence stfe ON stfe.FileEvidenceID = fe.FileEvidenceID
    )
GROUP BY SUBSTRING(FileName,1,(CHARINDEX('/',FileName,2)))
ORDER BY count(FileName) DESC

SELECT SUBSTRING(FilePath,1,(CHARINDEX('\',FilePath,4))) AS RootPath, count(FilePath) AS NoOfRecords
FROM ImportedFileEvidence
WHERE FilePath LIKE '_:\%'
    AND FilePath NOT LIKE '%Program Files%'
    AND [FileName] NOT IN (
        SELECT [Filename]
        FROM FileEvidence fe
            INNER JOIN SoftwareTitleFileEvidence stfe ON stfe.FileEvidenceID = fe.FileEvidenceID
    )
GROUP BY SUBSTRING(FilePath,1,(CHARINDEX('\',FilePath,4)))
ORDER BY count(FilePath) DESC