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
Options
Andy.S
#1Andy.S Member Posts: 51  
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
0
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.
0
Andy.S
#3Andy.S Member Posts: 51  
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

0

Active Discussions

Lansweeper Rest API for Scanning
by  Brian Smith   Go to last post Go to first unread
Last post: Today at 7:35:27 PM(UTC)
Lansweeper Language translation not working anymore
by  Florian   Go to last post Go to first unread
Last post: Today at 6:03:19 PM(UTC)
Lansweeper Use current user for scanning credentials?
by  pryan67   Go to last post Go to first unread
Last post: Today at 6:01:33 PM(UTC)
Lansweeper Bluetooth Info
by  FrankSc   Go to last post Go to first unread
Last post: Today at 5:42:59 PM(UTC)
Lansweeper Question about Scanning Assets and Password Encryption
by  FrankSc   Go to last post Go to first unread
Last post: Today at 5:23:05 PM(UTC)
Lansweeper How to enable HTTPS in ver 6.0.150.60
by  Duncan.Miles   Go to last post Go to first unread
Last post: Today at 1:11:19 PM(UTC)
Lansweeper INFO DateTimeService time refresh
by  miharix   Go to last post Go to first unread
Last post: Yesterday at 3:22:29 PM(UTC)
Lansweeper MS Edge Chromium LanSweeper Extension development
by  Slim D   Go to last post Go to first unread
Last post: 1/15/2021 11:39:29 AM(UTC)