SQL Query

[RamyaRajendranK]

Hi All,

Can someone help me with a query to map the VM Host for list of Virtual Machine ?  for some reason many of the VM's are not mapped to a Host , we need to update one by one which is time consuming.

[erwin_lindemann]

Hi Ramya,

Basically, you have to set the [ComplianceComputer].[HostID] value for the virtual machine to the [ComplianceComputerID] value of the device hosting the virtual machine.

Assuming that you have a table holding the unique names of the VMs and of the device hosting each VM, the SQL would be:

/* Temporary table holding unique names for VMs and for the devices hosting these VMs  */
IF OBJECT_ID('tempdb..#VirtualMachinesWithHosts') IS NOT NULL   
DROP TABLE #VirtualMachinesWithHosts

CREATE TABLE #VirtualMachinesWithHosts
(
	    VirtualMachineName NVARCHAR(256)   PRIMARY KEY
	  , HostName           NVARCHAR(256)
)

/* Populate table #VirtualMachinesWithHosts with your own data */
-- INSERT INTO #VirtualMachinesWithHosts(VirtualMachineName, HostName) 
-- VALUES ('VM1', 'HostForVM1')

UPDATE vm 
SET    vm.HostID = host.ComplianceComputerID
FROM   [ComplianceComputer] vm
JOIN
	#VirtualMachinesWithHosts vmh   
     ON vmh.VirtualMachineName = vm.ComputerName
JOIN 
	[ComplianceComputer]      host  
     ON host.ComputerName = vmh.HostName

[mfranz]

Hi @erwin_lindemann,

A little correction:

1. "HostID" in "ComplianceComputer" is not the key to link Hosts and VMs. Per schema reference, it is a text field, no idea how it is actually used.
   
2. Your code snippet is on the right track, but the "HostID" needs to be "HostVirtualComputerID":
   

Best regards,

Markward

[dbeckner]

@RamyaRajendranK Before resorting to manually mapping these VMs to their host I would consider the following questions:

1. Are you receiving inventory from the virtualization platform?
2. If it is VMware are you confident you have targeted all vCenters and do the credentials have permissions propagated to all child objects?
3. If they are Hyper-V do you have the agent deployed to every host or do you have a SCVMM connection configured to each manager?
4. If it is cloud hosted do you have a connection to each tenant?

I would look at those questions first to ensure there is not a configuration error or missed host connection somewhere as that is normally the culprit.