One field that shows VM or Hyper-V host

[DLorien]

Hey everyone,

I'm not an expert in SQL, but so far, I haven't run into anything in Lansweeper that I can't handle. Except for this. I'm trying to create a single report which shows something like this:

Assetname       OS               Platform
Server1         Windows 2003     esxihost1.contoso.com
Server2         Win 2k15         hyper-vhost1.contoso.com
Server3         Win 2008 R2      Physical
Server4         Windows 3.11     esxihost2.contoso.com

I messed around using IF THEN and CASE statements with no luck. Is there any way to accomplish this?

This is what I have so far:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname As OS,
tHost.AssetName
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join TblHyperVGuestNetwork
On tblAssets.Mac = TblHyperVGuestNetwork.MacAddress
Inner Join tblHyperVGuest On tblHyperVGuest.hypervguestID =
TblHyperVGuestNetwork.HyperVGuestID
Left Join tblAssets tHost On tHost.AssetID = tblHyperVGuest.AssetID
Inner Join tblVmwareGuestNetwork
On tblAssets.Mac = tblVmwareGuestNetwork.MacAddress
Inner Join tblVmwareGuest On tblVmwareGuest.GuestID =
tblVmwareGuestNetwork.GuestID And tblVmwareGuest.AssetID = tHost.AssetID
Order By tblAssets.AssetName

[ToMonkey]

I'm using this report (Thanks) but in some cases I get duplicate server entries and have a hard time working out why.

Do you have any idea, the first time it appears the VM host is correct but for the second entry it is not.

[Susan_A · Accepted solution]

VMware and Hyper-V records are stored in separate database tables. SQL displays separate fields as separate columns. Merging fields within the report output requires fiddling and is not something we usually provide instructions for. As you only require two fields to be merged however, you could keep it simple and use the Coalesce function as shown below. This is a standard SQL function, not specific to Lansweeper.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tsysOS.OSname As OS,
  Coalesce(SubQuery1.VMwareHost, SubQuery2.HyperVhost) As Host
From tblAssets
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join (Select tblAssets.AssetID,
    tblAssets1.AssetName As VMwareHost
  From tblAssets
    Inner Join tblAssetMacAddress On tblAssetMacAddress.AssetID =
      tblAssets.AssetID
    Inner Join tblVmwareGuestNetwork On tblVmwareGuestNetwork.MacAddress =
      tblAssetMacAddress.Mac
    Inner Join tblVmwareGuest On tblVmwareGuest.GuestID =
      tblVmwareGuestNetwork.GuestID
    Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
      tblVmwareGuest.AssetID) SubQuery1 On SubQuery1.AssetID = tblAssets.AssetID
  Left Join (Select Top 1000000 tblAssets.AssetID,
    tblAssets1.AssetName As HyperVhost
  From tblAssets
    Inner Join tblAssetMacAddress
      On tblAssets.AssetID = tblAssetMacAddress.AssetID
    Inner Join TblHyperVGuestNetwork On TblHyperVGuestNetwork.MacAddress =
      tblAssetMacAddress.Mac
    Inner Join tblHyperVGuest On TblHyperVGuestNetwork.HyperVGuestID =
      tblHyperVGuest.hypervguestID
    Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
      tblHyperVGuest.AssetID) SubQuery2 On SubQuery2.AssetID = tblAssets.AssetID
Order By tblAssets.AssetName