Hyper-V Guest Inventory

[Mister_Nobody]

LS has too small information about Hyper-V virtual machines.

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

Select Distinct Top 1000000 tblAssets.assetid,
  tblAssets.AssetName,
  tblAssets.ipaddress,
  tblAssets.mac,
  tblHyperVGuest.Name,
  Case tblHyperVGuest.Enabledstate
    When 2 Then 'Enabled'
    When 3 Then 'Disabled'
    When 32768 Then 'Paused'
    When 32769 Then 'Suspended'
    When 32770 Then 'Starting'
    When 32771 Then 'Snapshotting'
    When 32773 Then 'Saving'
    When 32774 Then 'Stopping'
    When 32776 Then 'Pausing'
    When 32777 Then 'Resuming'
    Else 'Unknown state'
  End As Enabledstate,
  Case tblHyperVGuest.Healthstate
    When 5 Then 'OK'
    When 20 Then 'Major Failure'
    When 25 Then 'Critical Failure'
    Else 'Unknown state'
  End As Healthstate,
  tblHyperVGuest.Lastchanged,
  tblAssets1.Assetname As hostname,
  tblProcessor.Name As Processor,
  tblAssets1.oscode As host_OSCode,
  tblOperatingsystem1.Caption As Host_OS,
  Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.osrelease, 'Not Scanned')
  As OS,
  tblAssets.Userdomain,
  tblAssets.Username,
  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,
  tblHyperVGuest.Memory As VM_Memory,
  tblAssets.Memory,
  Ceiling(tblOperatingsystem.TotalVisibleMemorySize / 1024) As OSMemory,
  Coalesce((Select Sum(Cast(Cast(tblLinuxHardDisks.Size As BigInt) /
      1024 As bigint)) 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 bigint)) 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
From tblHyperVGuest
  Inner Join tblAssets As tblAssets1 On tblAssets1.AssetID =
      tblHyperVGuest.AssetID
  Left Join tblAssets On tblHyperVGuest.GuestAssetId = tblAssets.AssetID
  Left Join tblBaseBoard On tblAssets.AssetID = tblBaseBoard.AssetID
  Left Join tblProcessor On tblAssets1.AssetID = tblProcessor.AssetID
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
  Left Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Left Join tblOperatingsystem tblOperatingsystem1 On tblAssets1.AssetID =
      tblOperatingsystem1.AssetID
  Left Join tblLinuxBios On tblAssets.AssetID = tblLinuxBios.AssetID
  Left Join tblLinuxBaseBoard On tblAssets.AssetID = tblLinuxBaseBoard.AssetID

Features:

• Linux and Windows guest assets in same report
• Guest and Host OSes
• VM state text description
• Host CPU
• Virtual BIOS Info for Windows and Linux Guests
• 3 methods to show VM OS memory
• List of Linux partitions and Windows disk and their summary size
• non-standard USB-devices (filter for English and Russian device names)

[Mister_Nobody]

You can use code from

https://community.lansweeper.com/t5/forum/vmware-guest-inventory/td-p/67060

[Obi_1_Cinobi]

Hello there!

Awesome job! Keep sharing these!