Example for custom queries needed

[wolf_vick]

Hi team,

I am looking for a way for updating host information of virtual machines through Business Adapter Studio. Unfortunately there is no direct property for host. Hence I assume "Custom Query" can be used to update this information. As I am not familiar with custom queries I am looking for an example. Can anyone share the same with me?

Kind Regards,

Wolf-Dietrich 

[mfranz · Accepted solution]

Hi,

Here is some pseudo-code:

UPDATE vm
SET vm.HostComplianceComputerID = host.ComplianceComputerID
FROM ComplianceComputer cc
JOIN VirtualMachine vm
	ON cc.ComplianceComputerID = vm.ComplianceComputerID
JOIN Mapping m
	ON cc.ComputerName = m.VMName
JOIN ComplianceComputer host
	ON m.HostName = host.ComputerName

Some assumptions and details:

• You have some kind of mapping table, with a VM to Host relationship
• This could be based on an Excel or CSV file
• VMs and Hosts are identified by their names (VMName & HostName)
• The actual relation is stored in the VirtualMachine table, ComplianceComputerID refers to the VM inventory, HostComplianceComputerID refers to the host inventory
• You might need to check if the hosts are already ComplianceComputerTypeID 2 and VMs are already set to ComplianceComputerTypeID 3, if they're not set correctly, the results might not show up in the WebUI. Types can easily be changed in the WebUI, or you could automate this using SQL as well.

Please be careful, maybe start testing this with only a few samples. Maybe do make a backup before.

Best regards,

Markward

[wolf_vick]

Hello Markward,
thanks for sharing this example.Now I know how to proceed.
KInd Regards,
Wolf