‎12-08-2015 08:59 PM
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
Solved! Go to Solution.
‎12-17-2015 02:27 AM
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
‎06-08-2017 03:55 PM
‎12-17-2015 02:27 AM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now