SQL Query to determine location of VirtualMachines

[Omar_Muntslag]

Hello,

I have a couple of ESX servers connected to different SAN's.

I created a sql query to get the following info from the VC2 database :

\- names of esx servers

\- lun's connected tot esx servers

\- vm's running on esx servers

\- drive letters, sizes and free space of vm's

Is there a way to determine on what lun a vm is running ?

Or can this info only be retrieved using VCOM ?

SQL Query at the moment :

SELECT TOP (100) PERCENT dbo.VPX_HOST.DNS_NAME AS ESX_NAME, dbo.VPX_VM.DNS_NAME AS VM_NAME, dbo.VPX_VM.IP_ADDRESS AS VM_IP,

dbo.VPX_VM.GUEST_STATE AS VM_STATE, dbo.VPX_GUEST_DISK.PATH AS VM_DRIVE, dbo.VPX_GUEST_DISK.CAPACITY AS VMDRIVE_SIZE,

dbo.VPX_GUEST_DISK.FREE_SPACE AS VM_DRIVE_FREE

FROM dbo.VPX_VM INNER JOIN

dbo.VPX_GUEST_DISK ON dbo.VPX_VM.ID = dbo.VPX_GUEST_DISK.VM_ID INNER JOIN

dbo.VPX_HOST ON dbo.VPX_VM.HOST_ID = dbo.VPX_HOST.ID

[chrisbuzby]

I have soem updated SQL queries note Virtual_Admin may be different for your environment

-- All Drives

SELECT top 100 percent

Virtual_Admin.VPX_ENTITY.NAME,

Virtual_Admin.VPX_VM.DNS_NAME,

Virtual_Admin.VPX_HOST.DNS_NAME AS ESX_NAME,

Virtual_Admin.VPX_GUEST_DISK.PATH,

round(CAST(Virtual_Admin.VPX_GUEST_DISK.CAPACITY as float) / 1073741824,0) as CAPACITY,

round(CAST(Virtual_Admin.VPX_GUEST_DISK.FREE_SPACE as float) / 1073741824,0) as FREE_SPACE

FROM Virtual_Admin.VPX_VM INNER JOIN

Virtual_Admin.VPX_GUEST_DISK ON Virtual_Admin.VPX_VM.ID = Virtual_Admin.VPX_GUEST_DISK.VM_ID INNER JOIN

Virtual_Admin.VPX_ENTITY ON Virtual_Admin.VPX_ENTITY.ID = Virtual_Admin.VPX_GUEST_DISK.VM_ID INNER JOIN

Virtual_Admin.VPX_HOST ON Virtual_Admin.VPX_VM.HOST_ID = Virtual_Admin.VPX_HOST.ID

-- All Combined with ESX host name with Guest OS and templates removed

SELECT TOP 100 PERCENT

Virtual_Admin.VPX_ENTITY.NAME AS VM_NAME,

max(Virtual_Admin.VPX_VM.DNS_NAME) AS VM_DNS_NAME,

max(Virtual_Admin.VPX_HOST.DNS_NAME) AS ESX_NAME,

max(Virtual_Admin.VPX_VM.GUEST_OS) AS GUEST_OS,

sum(round(CAST(Virtual_Admin.VPX_GUEST_DISK.CAPACITY as float) / 1073741824,0)) as COMBINED_DRIVE_SIZE,

sum(round(CAST(Virtual_Admin.VPX_GUEST_DISK.FREE_SPACE as float) / 1073741824,0)) as COMBINED_FREE_SPACE

FROM Virtual_Admin.VPX_VM INNER JOIN

Virtual_Admin.VPX_GUEST_DISK ON Virtual_Admin.VPX_VM.ID = Virtual_Admin.VPX_GUEST_DISK.VM_ID INNER JOIN

Virtual_Admin.VPX_ENTITY ON Virtual_Admin.VPX_ENTITY.ID = Virtual_Admin.VPX_GUEST_DISK.VM_ID INNER JOIN

Virtual_Admin.VPX_HOST ON Virtual_Admin.VPX_VM.HOST_ID = Virtual_Admin.VPX_HOST.ID

where Virtual_Admin.VPX_VM.IS_TEMPLATE = '0'

group by Virtual_Admin.VPX_ENTITY.NAME

[kjb007]

Are you looking specifically for a SQL query? If you were using poweshell vi toolkit, it would have been a simple script to generate which vm's are running on a particular datastore.

get-vm -datastore (get-datastore datastore1)

-KjB