- Download 'vmw_get_infrastructure_tab_machines.sql' attached to this KB below
- Log into SQL management studio and connect to the IaaS database.
- Open 'vmw_get_infrastructure_tab_machines.sql' and run to create the stored procedure 'vmware_utility_vRA_get_infrastructure_tab_managed_machines'.
- 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)