Provisioning request failure for vRealize Automation plugin within vRealize Orchestrator: "Unable to execute 'fetchRelation' for type"

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:

• A timezone change has occurred causing an offset in time
• When browsing the Inventory for the vRealize Automation IaaS plugin in Orchestrator, you see an error similar to:
  
  Exception: Unable to execute 'fetchRelation' for type : virtualFolder_VirtualMachines : java.lang.IllegalArgumentException: Cannot parse "<YYYY-MM-DD>T<time>": Illegal instant due to time zone offset transition (America/New_York). </time>

• The same messages are visible in the Orchestrator server.log file

Environment

VMware vRealize Automation 7.x
VMware vRealize Orchestrator 7.x
VMware vRealize Automation 6.2.x

Cause

The issue is caused by time offset during Daylight Saving Time, when data is being collected from the IaaS database.

Resolution

There is no resolution for this issue, see the available workaround below.

Workaround:
This issue effects vRealize Automation 6.x and 7.x

1. Download and execute the attached SQL Script against the vRealize Automation IaaS SQL Server database:

iKB_fix_gap_dates.sql

2. Copy to the hosting SQL Server instance
3. Create a Full Database Backup
4. Open the .sql file within SQL Server Management Studio
   • Description of script logic and contained functions:
     • The script will adjust any date/times that fall in DST ranges that cause the exception. There are two date/times that will NOT be adjusted. If the issue is being caused by LastPowerOffDate or LastPowerOnDate, the script will point that out and indicate that a power cycle/data collection will be needed to update the data.
     • UNCOMMENT the "select @correct_bad_dates = 1" line when you are ready to correct the bad dates by replacing them with the replacement value.

Note: Remove the "--" from in front of the "select @correct_bad_dates = 1"

2. If requested by the SQL script, Start an Inventory data collection:
   • To start an Inventory data collection:
     • Login to the vRealize Automation portal as a Fabric Administrator.
     • Click the Infrastructure tab.
     • Open Compute Resources.
     • For the affected compute resource, start data collection by hovering over the name and choosing Data Collection.
     • For Inventory, click Request now.

Additional Information