- Flexera Community
- :
- FlexNet Manager
- :
- FlexNet Manager Suite Known Issues
- :
- Known Issue: Usage date format described in inventory spreadsheet schema documentation doesn't match...
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
Known Issue: Usage date format described in inventory spreadsheet schema documentation doesn't match format accepted by import code (IOJ-2227030)
Known Issue: Usage date format described in inventory spreadsheet schema documentation doesn't match format accepted by import code (IOJ-2227030)
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:
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: FNMS-71642
Tip: If you have been impacted by this issue, please click the KUDOS button above. This helps to track the relative significance and importance of issues. Clicking on an option against "Was this article helpful?" also helps assess and improve the usefulness of content.