Notification

Icon
Error

Virtual Guest machines report- Add guest OS

Posted: Wednesday, January 6, 2021 10:17:48 PM(UTC)
SteelOG

SteelOG

Member Original PosterPosts: 12
0
Like
I am using the Virtual Guest Machines report copied from https://www.lansweeper.c...virtual-guest-machines/ I would like to add a field for the guest operating system if it is known but everything I've tried results in duplicate entries. Can anyone provide guidance? Thanks!

Select Top 1000000 ha.AssetID,
tblVmwareGuest.Name As Guest,
ha.AssetName As Host,
h.Version,
tblVmwareGuest.Memory,
tblVmwareGuest.IsRunning As State,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblVmwareGuest.lastchanged,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As Type,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Inner Join tblVmwareInfo h On tblVmwareGuest.HostID = h.VmwareID
Inner Join tblAssets ha On ha.AssetID = h.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Left Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Order By Host,
Guest
Andy.S
#1Andy.S Member Posts: 57  
posted: 1/11/2021 4:19:34 PM(UTC)
Hi,

Is this what your after :

Code:
Select Top 1000000 ha.AssetID,
  tblVmwareGuest.Name As Guest,
  tsysOS.OSname,
  tblAssetCustom.Model,
  ha.AssetName As Host,
  h.Version,
  tblVmwareGuest.Memory,
  tblVmwareGuest.IsRunning As State,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblVmwareGuest.lastchanged,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As Type,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
  Inner Join tblVmwareInfo h On tblVmwareGuest.HostID = h.VmwareID
  Inner Join tblAssets ha On ha.AssetID = h.AssetID
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
  Left Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
  Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Order By Host,
  Guest
SteelOG
#2SteelOG Member Original PosterPosts: 12  
posted: 1/11/2021 7:52:34 PM(UTC)
Sorta, only this removes all of the machines with no known OS. I would like to keep all of the guest machines shown in the original, just fill in the OS if it's known.
Andy.S
#3Andy.S Member Posts: 57  
posted: 1/12/2021 9:22:54 AM(UTC)
OK if you change the Inner Join To a Left Join that should fix it as below

Code:
Select Top 1000000 ha.AssetID,
  tblVmwareGuest.Name As Guest,
  tsysOS.OSname,
  tblAssetCustom.Model,
  ha.AssetName As Host,
  h.Version,
  tblVmwareGuest.Memory,
  tblVmwareGuest.IsRunning As State,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblVmwareGuest.lastchanged,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As Type,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
  Inner Join tblVmwareInfo h On tblVmwareGuest.HostID = h.VmwareID
  Inner Join tblAssets ha On ha.AssetID = h.AssetID
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
  Left Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
  Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Order By Host,
  Guest

Active Discussions

Report Center Shares on Windows computers and their share permissions
by  RC62N   Go to last post Go to first unread
Last post: Yesterday at 4:51:50 PM(UTC)
Lansweeper Asset management/replacement
by  DeviantTomato  
Go to last post Go to first unread
Last post: 3/1/2021 9:01:02 AM(UTC)
Lansweeper Turn off graphical report builder
by  Tenacious  
Go to last post Go to first unread
Last post: 2/23/2021 7:53:03 PM(UTC)
Report Center Reference software list
by  DeviantTomato  
Go to last post Go to first unread
Last post: 2/23/2021 1:16:48 PM(UTC)
Lansweeper Report Filtering Uptime
by  CyberCitizen   Go to last post Go to first unread
Last post: 2/22/2021 12:28:22 AM(UTC)
Lansweeper Adobe Report for CVE-2021-21017
by  baycrest  
Go to last post Go to first unread
Last post: 2/10/2021 2:27:57 PM(UTC)