→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
DLorien
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
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

View solution in original post

2 REPLIES 2
ToMonkey
Engaged Sweeper II
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
Lansweeper Alumni
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