Notification

Icon
Error

Virtual machines and their host

Posted: Tuesday, January 22, 2019 10:37:31 AM(UTC)
Esben.D

Esben.D

Member Administration Original PosterPosts: 1,873
4
Like
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
Code:
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
RayHeathTSS
#1RayHeathTSS Member Posts: 5  
posted: 2/22/2019 6:00:01 PM(UTC)
This only returned VMWare Guests and Hosts.

Can this be easily modified to also include HyperV?
Jack of All Trades
#2Jack of All Trades Member Posts: 2  
posted: 2/25/2019 1:56:40 PM(UTC)
I would also appreciate report for HyperV hosts.
ajokerst
#3ajokerst Member Posts: 6  
posted: 3/6/2019 7:57:44 PM(UTC)
I would also like the Hyper-V report
b.c
#4b.c Member Posts: 1  
posted: 3/28/2019 9:14:29 PM(UTC)
Add another that would like to see a Hyper-V report for hosts and associated guests.
klaus
#5klaus Member Posts: 14  
posted: 9/17/2019 8:44:57 AM(UTC)
Code:
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

Active Discussions

Lansweeper Report Needed of just DEFAULT GROUP assets
by  SHib11   Go to last post Go to first unread
Last post: Today at 2:57:23 PM(UTC)
Lansweeper No longer shows up-to-date with new OS version
by  ICT Support   Go to last post Go to first unread
Last post: Today at 11:29:10 AM(UTC)
Lansweeper Do not show the computers that have a specific user
by  akuc  
Go to last post Go to first unread
Last post: Today at 10:21:45 AM(UTC)
Lansweeper adding KB numbers
by  Esben.D   Go to last post Go to first unread
Last post: Today at 8:51:54 AM(UTC)
Lansweeper Default 'warranty' Report Adjustments or Clones
by  Cripple.Zero  
Go to last post Go to first unread
Last post: Yesterday at 7:22:07 PM(UTC)
Lansweeper Password Never Expires
by  CoolDood   Go to last post Go to first unread
Last post: 10/11/2019 12:34:14 PM(UTC)
Lansweeper Average ticket time until closed 7 days, 30 days
by  JasonDawson  
Go to last post Go to first unread
Last post: 10/10/2019 4:20:03 PM(UTC)