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