wolf_vick
Occasional contributor

Example for custom queries needed

Jump to solution

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 

0 Kudos
1 Solution

Accepted Solutions
mfranz
Shining star

Re: Example for custom queries needed

Jump to 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

Softline Group is Europe's leading independent expert in Software Asset Management.

View solution in original post

2 Replies
mfranz
Shining star

Re: Example for custom queries needed

Jump to 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

Softline Group is Europe's leading independent expert in Software Asset Management.

View solution in original post

wolf_vick
Occasional contributor

Re: Example for custom queries needed

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