Is there a way to view vm Hosts on a vCenter?

[kevin_christens]

We use ESXQuery standalone to bring vCenter inventory into FlexNet and want to see a relationship of the vCenters to the ESX hosts configured for those vCenters.   VirtualMachineInfo provides a view of VM guests and the VM hosts they are currently on but we cannot seem to find a way to see which vCenter those VM hosts are managed by.

Is there a query that would allow us to display a vCenter and find all the Hosts on that vCenter?

[kclausen · Accepted solution]

@kevin_christens 

Yes, you can see this relationship on the VMware Inventory view.

[kclausen · Accepted solution]

@kevin_christens 

Yes, you can see this relationship on the VMware Inventory view.

[kevin_christens]

Thank you.  I see that report in the GUI but I'd like to be able to automatically extract that information on daily basis and can't seem to find a view or table that contains that information that I can query with SQL.

[kclausen · Accepted solution]

The View you want to use in the Compliance database is Grid_VmWareInstallations

Here is an example query:

SELECT
v1.VCenterServerName as 'vCenter',
v1.InstalledDeviceName as 'HostName',
v1.VSphereOrVCenterApplicationName as 'vSphere Application',
v2.VSphereOrVCenterApplicationName as 'vCenter Application'
FROM Grid_VmWareInstallations v1 JOIN
Grid_VmWareInstallations v2 on v2.InstalledDeviceName=v1.VCenterServerName
where v1.VCenterServerName <> ''
ORDER BY v1.VCenterServerName,v1.InstalledDeviceName

[kevin_christens]

No rows returned.  I find that all the grid_% views don't have data.

select count(*) from Grid_VmWareInstallations
0

[kclausen]

Try running the query connected to the Database as the FNMS Service Account

[kevin_christens]

The query does work when running as the Service Account.  Is there a reason it does not return values if not using the Service Account?

By the way. That view is an impressive SQL statement.

[ChrisG]

Some database views (particularly views that are used internally when retrieving data to be displayed in the web interface) will only return data based on the current user's access rights as configured in FlexNet Manager Suite. For such views you will need to ensure you connect to the SQL Server instance using a user account that is configured as a member of appropriate role(s) - or otherwise take steps to impersonate such a user.

[nrousseau1]

Hello @kevin_christens ,

vCenter are a little bit hidden in ITAM/FlexNet Manager.

The VMWare Inventory Report has a vCenter column.

Technically, if the Clusters & Hosts were collected by the vCenter inventory connector, the vCenter IP address will be the left part of the "ExternalName" of the cluster (for instance): 25.200.26.129|Cluster01.Then, we create vCenter discovered devices that contains the version of vCenter...

The Oracle Optimization Reports all contain the vCenter/Cluster/Host structure along with vCenter version.

More to come...

Best regards

Nicolas