Known Issue: Usage date format described in inventory spreadsheet schema documentation doesn't match format accepted by import code (IOJ-2227030)

[#1]

Summary

The documented supported format of date values related to usage data used in inventory import spreadsheets does not match the actual formats supported by the import process.

Details

The following pages in the Inventory Spreadsheet Templates > Compliance.InventoryReader.Logic Tables section of the FlexNet Manager Suite Schema Reference documentation indicates that values in the StartDate and LastUsedDate columns of installer evidence and file evidence spreadsheets used for inventory data imports must use the format yyyyMMdd:

• ConsolidatedInstallerEvidence Template
• ConsolidatedFileEvidence Template

However current versions of FlexNet Manager Suite/Flexera One ITAM only support dates being specified in these fields in the following formats (using either "/" or "-" as separator characters):

• yyyy/MM/dd
• dd/MM/yyyy

Additional information

If date values are provided in inventory spreadsheet data in invalid formats, the inventory import process may fail with errors like the following:

2021-10-19 00:39:25,642 [INFO ]     InsertComputerEvidenceFromInstallerEvidenceUsage
2021-10-19 00:39:26,174 [INFO ]       Failed to execute Writer 'InsertComputerEvidenceFromInstallerEvidenceUsage' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\Usage.xml, at step line 45
Error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

or

2021-10-19 00:39:25,642 [INFO ]     InsertComputerEvidenceFromFileEvidenceUsage
2021-10-19 00:39:26,174 [INFO ]       Failed to execute Writer 'InsertComputerEvidenceFromFileEvidenceUsage' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\Usage.xml, at step line 76
Error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

The following queries can be executed against an FlexNet Manager Suite On-premises compliance database to identify problematic data:

; WITH u AS (
    SELECT
        StartDate,
        StartDateConverted =
            CASE
                WHEN CHARINDEX('/', StartDate) = 5 OR CHARINDEX('-', StartDate) = 5
                    THEN TRY_CONVERT(datetime, StartDate, 111)
                WHEN CHARINDEX('/', StartDate) = 3 OR CHARINDEX('-', StartDate) = 3
                    THEN TRY_CONVERT(datetime, StartDate, 103)
            END
    FROM dbo.ImportedInstalledInstallerEvidenceUsage
    WHERE CHARINDEX('/', StartDate) IN (3, 5) OR CHARINDEX('-', StartDate) IN (3, 5)
)
SELECT StartDate
FROM u
WHERE StartDateConverted IS NULL


; WITH u AS (
    SELECT
        LastUsedDate,
        LastUsedDateConverted =
            CASE
                WHEN CHARINDEX('/', LastUsedDate) = 5 OR CHARINDEX('-', LastUsedDate) = 5
                    THEN TRY_CONVERT(datetime, LastUsedDate, 111)
                WHEN CHARINDEX('/', LastUsedDate) = 3 OR CHARINDEX('-', LastUsedDate) = 3
                    THEN TRY_CONVERT(datetime, LastUsedDate, 103)
            END
    FROM dbo.ImportedInstalledInstallerEvidenceUsage
    WHERE CHARINDEX('/', LastUsedDate) IN (3, 5) OR CHARINDEX('-', LastUsedDate) IN (3, 5)
)
SELECT LastUsedDate
FROM u
WHERE LastUsedDateConverted IS NULL

You may need to work with Flexera Support to identify problematic data that has been uploaded to Flexera One ITAM.

Additional information

Also see the following related issue: InsertComputerEvidenceFrom*EvidenceUsage import writer steps may fail with error due to usage date values in invalid format: "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value" (IOJ-2122692)

Fix status

This issue is still open but not currently scheduled to be addressed.

Other information

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

Master issue ID: IOJ-2227030

Also known as: ITAM-1389