cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Esben_D
Lansweeper Employee
Lansweeper Employee
The report below will show all virtual machines and their hosts.


The report will only list assets that meet all of the following criteria:
  • You are running Lansweeper 7
  • The asset state is set to active.
  • Your ESXi hosts are directly scanned or via vCenter scanning
Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblVmwareGuest.Ipv4Address,
tblAssets1.AssetName As Host,
tblAssets1.IPAddress As HostIP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysAssetTypes.AssetTypeIcon10 As icon
From tblAssets
Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblVmwareInfo On tblVmwareGuest.HostID = tblVmwareInfo.VmwareID
Left Join tblAssets tblAssets1 On tblAssets1.AssetID = tblVmwareInfo.AssetID
Where tblState.Statename = 'Active'
Order By tblVmwareGuest.Ipv4Address,
tblAssets.AssetName
6 REPLIES 6
TheTechguy
Engaged Sweeper
When I try to save this I get, Invalid "SELECT" statement. Unexpected token "OUTER" at line 18, pos 3. Any ideas how to fix this? This seems like a good report for my Hyper-V clusters.
klaus
Engaged Sweeper III
Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblVmwareGuest.Ipv4Address,
case when tblAssets1.AssetName is not null then tblAssets1.AssetName else hvasset.assetname end As Host,
case when tblAssets1.IPAddress is not null then tblAssets1.IPAddress else hvasset.IPAddress end As HostIP,
case when tblassets1.assetname is not null then 'VMware' else 'HyperV' end as Type,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysAssetTypes.AssetTypeIcon10 As icon
From tblAssets
left outer Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblVmwareInfo On tblVmwareGuest.HostID = tblVmwareInfo.VmwareID
Left Join tblAssets tblAssets1 On tblAssets1.AssetID = tblVmwareInfo.AssetID
OUTER apply
(SELECT TOP 1 *
FROM tblhypervguest js
WHERE js.Name = tblassets.AssetName
ORDER BY js.Lastchanged DESC) js
left outer join tblassets AS hvasset ON hvasset.AssetID = js.AssetID
Where tblState.Statename = 'Active' and ( hvasset.assetid is not null or tblAssets1.assetid is not null)
Order By tblVmwareGuest.Ipv4Address,
tblAssets.AssetName
b_c
Engaged Sweeper
Add another that would like to see a Hyper-V report for hosts and associated guests.
ajokerst
Engaged Sweeper II
I would also like the Hyper-V report
it9
Engaged Sweeper II
I would also appreciate report for HyperV hosts.
RayHeathTSS
Engaged Sweeper II
This only returned VMWare Guests and Hosts.

Can this be easily modified to also include HyperV?