VMware Guest Inventory

[Mister_Nobody]

LS has a lot of information about VMware virtual machines but too less reports.

And I create report which combine data from virtualize Linux and Windows assets.

VMware vCenter scan is not required.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  SubString(tblAssets.Description, 1, 25) As description,
  tblVmwareGuest.Name As VMName,
  tblAssets.userdomain,
  tblAssets.username,
  Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.osrelease,
  tblVmwareGuest.guestfullname) As OS,
  tblAssets.IPAddress,
  tblVmwareGuest.NumVirtualDisks,
  tblVmwareGuest.NumEthernetCards,
  tblVmwareGuest.MacAddress,
  tblVmwareInfo.HostName,
  tblVmwareInfo.version As ESXi_Version,
  tblVmwareGuest.CpuCount As VMCoreCount,
  tblVmwareGuest.Memory As VMVemory,
  Coalesce(tblBaseBoard.version, tblLinuxBaseBoard.version) As MB_Version,
  tblBIOS.caption As BIOS_Win_Caption,
  Coalesce(tblBIOS.releasedate, tblLinuxBios.releasedate) As BiosDate,
  Coalesce(tblBIOS.Version, tblLinuxBios.version) As Bios_Version,
  tblAssetCustom.Serialnumber,
  Cast(tblVmwareGuest.UnsharedStorage As bigint) / 1024 / 1024 As VMUsedSize,
  Coalesce((Select Sum(Cast(Cast(tblLinuxHardDisks.Size As BigInt) /
      1024 As Numeric)) From tblLinuxHardDisks
    Where tblLinuxHardDisks.AssetID = tblAssets.AssetID And
      (tblLinuxHardDisks.filesystem Like '/dev/s%' Or
        tblLinuxHardDisks.filesystem Like '/dev/mapper/%')), '0') +
  Coalesce((Select Sum(Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 /
      1024 As Numeric)) From tblDiskdrives
    Where tblDiskdrives.AssetID = tblAssets.AssetID), '0') As HDDSize,
  Coalesce(Stuff((Select ', ' + IsNull(tblFloppy.Model, ' ') + '(' +
      IsNull(Cast(Ceiling(tblFloppy.Size / 1024 / 1024 / 1024) As varchar(30)),
      '') + 'GB)' From tblFloppy
    Where tblAssets.AssetID = tblFloppy.AssetID And tblFloppy.Size > 0
    For Xml Path('')), 1, 2, ''), ' ') + Coalesce(Stuff((Select ', ' +
      IsNull(tblLinuxHardDisks.Filesystem, ' ') + '(' +
      IsNull(Cast((Cast(tblLinuxHardDisks.Size As bigint) / 1024 /
      1024) As varchar(30)), '') + ' GB)' From tblLinuxHardDisks
    Where tblAssets.AssetID = tblLinuxHardDisks.AssetID And
      (tblLinuxHardDisks.filesystem Like '/dev/s%' Or
        tblLinuxHardDisks.filesystem Like '/dev/mapper/%') And
      Cast(tblLinuxHardDisks.size As bigint) > 0 For Xml Path('')), 1,
  2, ''), ' ') As HDDs,
  Stuff((Select ', ' + IsNull(tblUSBDevices.Name, ' ') From tblUSBDevices
    Where tblAssets.AssetID = tblUSBDevices.AssetID And
      tblUSBDevices.Name Not Like N'%Стандарт%' And
      tblUSBDevices.Name Not Like '%HID%' And tblUSBDevices.Name Not Like
      '%hub%' And tblUSBDevices.Name Not Like N'%концентратор%' And
      tblUSBDevices.Name Not Like N'%совместим%' And
      tblUSBDevices.Name Not Like N'%устройств%' And
      tblUSBDevices.Name Not Like '%generic%' And tblUSBDevices.Name Not Like
      N'%мышь%' And tblUSBDevices.Name Not Like N'%клавиатура%' And
      tblUSBDevices.Name Not Like '%bluetooth%' And
      tblUSBDevices.Name Not Like '%headset%' And tblUSBDevices.Name Not Like
      '%printing support%' And tblUSBDevices.Name Not Like N'%поддержка%' And
      tblUSBDevices.Name Not Like '%mouse%' And tblUSBDevices.Name Not
      Like '%keyboard%' And tblUSBDevices.Name Not Like '%USB Composite Device%'
      And tblUSBDevices.Name Not Like '%USB Pointing Device%' And
      tblUSBDevices.Name Not Like '%USB Input Device%' And
      tblUSBDevices.Name Not Like '%USB Human Interface Device%' And
      tblUSBDevices.Name Not Like '%IEEE 1284.4 compatible printer%' And
      tblUSBDevices.Name Not Like '%fax%' And tblUSBDevices.Name Not Like
      '%EWS%' And tblUSBDevices.Name Not Like '%DOT4%' And
      tblUSBDevices.Name Not Like '%LEDM%' And tblUSBDevices.Name Not Like
      N'%фильтр%' And tblUSBDevices.Name Not Like '%windows%' And
      tblUSBDevices.Name Not Like N'%дисковый%' And
      tblUSBDevices.Name Not Like '%storage%' And tblUSBDevices.Name <> '' And
      tblUSBDevices.Name Not Like '%unknown%' And tblUSBDevices.Name Not Like
      '%smart%card%' And tblUSBDevices.Name Not Like '%HP Universal Printing%'
      And tblUSBDevices.Name Not Like '%wia driver%' For Xml Path('')), 1, 2,
  '') USBs,
  Case tblVmwareGuest.ToolsRunningStatus
    When 1 Then 'Executing scripts'
    When 2 Then 'Not running'
    When 3 Then 'Running'
  End As ToolsRunningStatus,
  tblVmwareGuest.ToolsVersion,
  Case tblVmwareGuest.ToolsVersionStatus
    When 1 Then 'Current'
    When 2 Then 'Out of date'
    When 3 Then 'Not installed'
    When 4 Then 'Unmanaged'
  End As ToolsVersionStatus,
  tblVmwareGuest.BootTime,
  tblVmwareGuest.Version,
  Case
    When (tblVmwareGuest.Version In ('vmx-20') And
      tblVmwareInfo.version Like '%ESXi 8%') Then 'Current'
    When (tblVmwareGuest.Version In ('vmx-17', 'vmx-18', 'vmx-19') And
      tblVmwareInfo.version Like '%ESXi 7%') Then 'Current'
    When (tblVmwareGuest.Version In ('vmx-14', 'vmx-15') And
      tblVmwareInfo.version Like '%ESXi 6.7%') Then 'Current'
    Else 'Out of support'
  End As VHW_UP,
  tblAssets.Firstseen,
  tblVmwareGuest.lastchanged
From tblVmwareGuest
  Inner Join tblVmwareInfo On tblVmwareGuest.HostID = tblVmwareInfo.VmwareID
  Left Join tblAssets On tblAssets.AssetID = tblVmwareGuest.AssetID
  Left Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Left Join tblBaseBoard On tblAssets.AssetID = tblBaseBoard.AssetID
  Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
  Left Join tblLinuxBios On tblAssets.AssetID = tblLinuxBios.AssetID
  Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tblLinuxBaseBoard On tblAssets.AssetID = tblLinuxBaseBoard.AssetID
Where Not (tblVmwareGuest.Name Like 'cp-parent%') And
  Not (tblVmwareGuest.MacAddress Is Null) And tblVmwareGuest.isrunning =
  'poweredon'
Order By tblAssets.AssetName

Features:

• Linux and Windows guest assets in same report
• Guest (also for non-scanned assets) and Host OSes
• VM tools state text description
• Check appropriate vHW versions for esxi 6.7, 7.0, 8.0
• Host CPU, VM vCPU count
• Virtual BIOS Info
• VM memory
• List of VM storage usage, Linux partitions and Windows disks and summary size
• Number of NICs, VMDKs
• non-standard USB-devices (filter for English and Russian device names)

*Only PoweredOn VMs is showing. You have to change filter to show all.

[Mister_Nobody]

You can use code from

https://community.lansweeper.com/t5/forum/hyper-v-guest-inventory/td-p/67059

[Mister_Nobody]

Some fixes for data corruption from Linux Scanning (Disks Size, Bios Date format)

+filter for VDI in F[arm]№P[ool]№ format

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  SubString(tblAssets.Description, 1, 25) As description,
  tblVmwareGuest.Name As VMName,
  tblAssets.userdomain,
  tblAssets.username,
  Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.osrelease,
  tblVmwareGuest.guestfullname) As OS,
  tblAssets.IPAddress,
  tblVmwareGuest.NumVirtualDisks,
  tblVmwareGuest.NumEthernetCards,
  tblVmwareGuest.MacAddress,
  tblVmwareInfo.HostName,
  tblVmwareInfo.version As ESXi_Version,
  tblVmwareGuest.CpuCount As VMCoreCount,
  tblVmwareGuest.Memory As VMVemory,
  tblBIOS.caption As BIOS_Win_Caption,
  Coalesce(Convert(datetime,tblLinuxBios.releasedate,101), tblBIOS.releasedate)
  As BiosDate,
  Coalesce(tblBIOS.Version, tblLinuxBios.version) As Bios_Version,
  tblAssetCustom.Serialnumber,
  Cast(tblVmwareGuest.UnsharedStorage As bigint) / 1024 / 1024 As VMUsedSize,
  Coalesce((Select Sum(Cast(Cast(Case IsNumeric(tblLinuxHardDisks.Size)
        When 1 Then tblLinuxHardDisks.Size
        Else '0'
      End As BigInt) / 1024 As Numeric)) From tblLinuxHardDisks
    Where tblLinuxHardDisks.AssetID = tblAssets.AssetID And
      (tblLinuxHardDisks.filesystem Like '/dev/s%' Or
        tblLinuxHardDisks.filesystem Like '/dev/mapper/%')), '0') +
  Coalesce((Select Sum(Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 /
      1024 As Numeric)) From tblDiskdrives
    Where tblDiskdrives.AssetID = tblAssets.AssetID), '0') As HDDSize,
  Coalesce(Stuff((Select ', ' + IsNull(tblFloppy.Model, ' ') + '(' +
      IsNull(Cast(Ceiling(tblFloppy.Size / 1024 / 1024 / 1024) As varchar(30)),
      '') + 'GB)' From tblFloppy
    Where tblAssets.AssetID = tblFloppy.AssetID And tblFloppy.Size > 0
    For Xml Path('')), 1, 2, ''), ' ') + Coalesce(Stuff((Select ', ' +
      IsNull(tblLinuxHardDisks.Filesystem, ' ') + '(' + IsNull(Cast((Cast(Case
        IsNumeric(tblLinuxHardDisks.Size)
        When 1 Then tblLinuxHardDisks.Size
        Else '0'
      End As bigint) / 1024 / 1024) As varchar(30)), '') + ' GB)'
    From tblLinuxHardDisks
    Where tblAssets.AssetID = tblLinuxHardDisks.AssetID And
      (tblLinuxHardDisks.filesystem Like '/dev/s%' Or
        tblLinuxHardDisks.filesystem Like '/dev/mapper/%') And
      Cast(Case IsNumeric(tblLinuxHardDisks.Size)
        When 1 Then tblLinuxHardDisks.Size
        Else '0'
      End As bigint) > 0 For Xml Path('')), 1, 2, ''), ' ') As HDDs,
  Stuff((Select ', ' + IsNull(tblUSBDevices.Name, ' ') From tblUSBDevices
    Where tblAssets.AssetID = tblUSBDevices.AssetID And
      tblUSBDevices.Name Not Like N'%Стандарт%' And
      tblUSBDevices.Name Not Like '%HID%' And tblUSBDevices.Name Not Like
      '%hub%' And tblUSBDevices.Name Not Like N'%концентратор%' And
      tblUSBDevices.Name Not Like N'%совместим%' And
      tblUSBDevices.Name Not Like N'%устройств%' And
      tblUSBDevices.Name Not Like '%generic%' And tblUSBDevices.Name Not Like
      N'%мышь%' And tblUSBDevices.Name Not Like N'%клавиатура%' And
      tblUSBDevices.Name Not Like '%bluetooth%' And
      tblUSBDevices.Name Not Like '%headset%' And tblUSBDevices.Name Not Like
      '%printing support%' And tblUSBDevices.Name Not Like N'%поддержка%' And
      tblUSBDevices.Name Not Like '%mouse%' And tblUSBDevices.Name Not
      Like '%keyboard%' And tblUSBDevices.Name Not Like '%USB Composite Device%'
      And tblUSBDevices.Name Not Like '%USB Pointing Device%' And
      tblUSBDevices.Name Not Like '%USB Input Device%' And
      tblUSBDevices.Name Not Like '%USB Human Interface Device%' And
      tblUSBDevices.Name Not Like '%IEEE 1284.4 compatible printer%' And
      tblUSBDevices.Name Not Like '%fax%' And tblUSBDevices.Name Not Like
      '%EWS%' And tblUSBDevices.Name Not Like '%DOT4%' And
      tblUSBDevices.Name Not Like '%LEDM%' And tblUSBDevices.Name Not Like
      N'%фильтр%' And tblUSBDevices.Name Not Like '%windows%' And
      tblUSBDevices.Name Not Like N'%дисковый%' And
      tblUSBDevices.Name Not Like '%storage%' And tblUSBDevices.Name <> '' And
      tblUSBDevices.Name Not Like '%unknown%' And tblUSBDevices.Name Not Like
      '%smart%card%' And tblUSBDevices.Name Not Like '%HP Universal Printing%'
      And tblUSBDevices.Name Not Like '%wia driver%' For Xml Path('')), 1, 2,
  '') USBs,
  Case tblVmwareGuest.ToolsRunningStatus
    When 1 Then 'Executing scripts'
    When 2 Then 'Not running'
    When 3 Then 'Running'
  End As ToolsRunningStatus,
  tblVmwareGuest.ToolsVersion,
  Case tblVmwareGuest.ToolsVersionStatus
    When 1 Then 'Current'
    When 2 Then 'Out of date'
    When 3 Then 'Not installed'
    When 4 Then 'Unmanaged'
  End As ToolsVersionStatus,
  tblVmwareGuest.BootTime,
  tblVmwareGuest.Version,
  Case
    When (tblVmwareGuest.Version In ('vmx-20') And
      tblVmwareInfo.version Like '%ESXi 8%') Then 'Current'
    When (tblVmwareGuest.Version In ('vmx-17', 'vmx-18', 'vmx-19') And
      tblVmwareInfo.version Like '%ESXi 7%') Then 'Current'
    When (tblVmwareGuest.Version In ('vmx-14', 'vmx-15') And
      tblVmwareInfo.version Like '%ESXi 6.7%') Then 'Current'
    Else 'Out of support'
  End As VHW_UP,
  tblAssets.Firstseen,
  tblVmwareGuest.lastchanged
From tblVmwareGuest
  Inner Join tblVmwareInfo On tblVmwareGuest.HostID = tblVmwareInfo.VmwareID
  Left Join tblAssets On tblAssets.AssetID = tblVmwareGuest.AssetID
  Left Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Left Join tblBaseBoard On tblAssets.AssetID = tblBaseBoard.AssetID
  Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
  Left Join tblLinuxBios On tblAssets.AssetID = tblLinuxBios.AssetID
  Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where Not (tblVmwareGuest.Name Like 'f[0-9]p[0-9]%' Or
  tblVmwareGuest.Name Like 'cp-parent%') And Not (tblVmwareGuest.MacAddress Is
  Null) And tblVmwareGuest.isrunning = 'poweredon'

[Obi_1_Cinobi]

Hello there!

Thanks so much for sharing! Good stuff!