Method to generate a list of managed machines via SQL

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:
If you are unable to generate a list of managed machines from the vRA UI or from Cloud Client, this KB article provides a method to generate the list directly from the IaaS SQL database.

Environment

VMware vRealize Automation 7.x

Resolution

1. Download 'vmw_get_infrastructure_tab_machines.sql' attached to this KB below
2. Log into SQL management studio and connect to the IaaS database.
3. Open 'vmw_get_infrastructure_tab_machines.sql' and run to create the stored procedure 'vmware_utility_vRA_get_infrastructure_tab_managed_machines'.
4. Execute the stored procedure to produce the data seen in the infrastructure/managed machines tab.

This stored procedure has 10 optional parameters that can be passed to filter the resultset to the desired machines and to order that resultset as desired. The resultset can be filtered by any combination of the 7 columns in the resultset. Data is filtered based on the beginning of the string.

Available filters:

@Name         
@Status       
@PlatformType 
@Owner        
@Blueprint    
@Group        
@Reservation  
@order1       
@order2       
@order3       

Examples:
This will return all managed machines ordered by the machine "Name"

EXEC [dbo].[vmware_utility_vRA_get_infrastructure_tab_managed_machines]
 

This will return all managed machines that begin with the letter "S" in the "Name" and will be ordered by the machine "Name"

EXEC [dbo].[vmware_utility_vRA_get_infrastructure_tab_managed_machines
@Name = 'S'

This will return all managed machines of platform type "vSphere (vCenter)" that begin with the letter "S" in the "Name" and will be ordered by the machine "Name"

EXEC [dbo].[vmware_utility_vRA_get_infrastructure_tab_managed_machines]
@Name = 'S',
@PlatformType = 'vSphere (vCenter)'

This will return all managed machines of platform type "vSphere (vCenter)" that begin with the letter "S" in the "Name" and will be ordered by "Reservation, Blueprint, Name"

EXEC [dbo].[vmware_utility_vRA_get_infrastructure_tab_managed_machines]
@Name = 'S',
@PlatformType = 'vSphere (vCenter)',
@order1 = 7, --first orderby column is column 7 (Reservation)
@order2 = 5, --second orderby column is column 5 (Blueprint)
@order3 = 1 --third orderby column is column 1 (Name)

This will return all managed machines of platform type "vSphere (vCenter)" that begin with the letter "S" in the "Name" and will be ordered by "Blueprint"

EXEC [dbo].[vmware_utility_vRA_get_infrastructure_tab_managed_machines]
@Name = 'S',
@PlatformType = 'vSphere (vCenter)',
@order1 = 5 --first orderby column is column 5 (Blueprint)

Additional Information

See the following documentation article for further information on use the above information to update managed machines using Bulk Import:  Bulk Import, Update, or Migrate Virtual Machines ​​​​​​​