Virtual machines and their host

[Esben_D]

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

[TheTechguy]

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]

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]

Add another that would like to see a Hyper-V report for hosts and associated guests.

[ajokerst]

I would also like the Hyper-V report

[it9]

I would also appreciate report for HyperV hosts.

[RayHeathTSS]

This only returned VMWare Guests and Hosts.

Can this be easily modified to also include HyperV?