vRealize Automation 7.x Data Collection Stuck "In Progress" for Compute Resource

February 5, 2020

Reading time: 3 minutes

Over the past several years of operating multiple vRealize Automation 7.x deployments, I’ve come across a situation where the data collection status for the Inventory or State data stays stuck at “In Progress” for a particular vSphere cluster. Usually, this occurs because a vSphere Agent executing the job was stopped mid-process. I initially searched online for a solution and could not find anything that helped. This left me to digging through the vRealize Automation 7.x SQL Server database to determine what might be causing the issue. Since I ran into this issue again today, I figured I’d put together a quick post providing my resolution.

vRealize Automation 7.6 - Compute Resource Data Collection Status - In Progress

Before Proceeding…

My solution is likely not supported by VMware as it involves modifying the vRA SQL Server database. Please make sure you create a database backup before attempting this process. While these changes only affect the status of data collection as reflected in vRA, any error made could cause additional issues.

Find the vRA “EntityID” for your vSphere Cluster

The first item that you need to accomplish is to find “EntityID” for the vSphere Cluster that is stuck at the “In Progress” status. To achieve this, connect to your vRA database using SQL Server Management Studio and execute the following command to return the list of vSphere Clusters (replace “[vRA]” with your database’s name):

SELECT [HostID] ,[HostName] ,[HostDNSName] FROM [vRA].[dbo].[Host] WHERE [ClusterHostID] IS NULL AND [IsCluster]=1 AND [IsVRMManaged]=1

You will receive a result similar to the following screenshot. Copy the “HostID” value for the vSphere cluster where data collection is stuck “In Progress”. In this case, the value for my cluster is “FD1B7CE9-0425-4701-A862-3D1D53301E27”.

vRealize Automation 7.6 - SQL Query for Cluster EntityIDs

Correct the Data Collection Status

Now that you have the “EntityID” of your vSphere Cluster in the vRA SQL Server database, your next step is to update the data collection status. Updating the data collection status in the database allows you to start the data collection jobs again manually. To accomplish this, using the SQL Server Management Studio, execute the following SQL query after replacing “[vRA]” with the name of your database, and “FD1B7CE9-0425-4701-A862-3D1D53301E27” with the HostID value you queried for your cluster in the previous step.

UPDATE [vRA].[dbo].[DataCollectionStatus] SET LastCollectedStatus=0, CollectionStartTime=NULL, LastCollectedTime=GETDATE() WHERE IsDisabled=0 AND EntityID=‘FD1B7CE9-0425-4701-A862-3D1D53301E27’

After executing the query against your vRA database, go back into the vRealize Automation web interface, and check the Data Collection status for your vSphere cluster Compute Resource. It should now show a status of “Failed” for each data collection task. Additionally, the REQUEST NOW buttons should be enabled and available for you to click again, allowing you to kick off the data collection tasks as you normally would.