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?
Aug 10, 2022 11:15 AM
Yes, you can see this relationship on the VMware Inventory view.
Aug 10, 2022 01:01 PM
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
Aug 10, 2022 02:20 PM
Yes, you can see this relationship on the VMware Inventory view.
Aug 10, 2022 01:01 PM
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.
Aug 10, 2022 02:02 PM
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
Aug 10, 2022 02:20 PM
No rows returned. I find that all the grid_% views don't have data.
select count(*) from Grid_VmWareInstallations
0
Aug 10, 2022 02:25 PM
Try running the query connected to the Database as the FNMS Service Account
Aug 10, 2022 02:29 PM
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.
Aug 11, 2022 08:25 AM
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.
Aug 11, 2022 10:52 PM
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
Aug 11, 2022 09:25 AM - edited Aug 12, 2022 07:49 AM