Hyper-V report with CPU information

[Garyr]

I am looking for a report that shows the configuration of the Hyper-V guests on a host. I am looking to report the: OS version, Memory, CPU's, Disk space. I have looked at the included reports and none of them show the CPU/cores assigned to the guest.

[Mister_Nobody]

Did you test my report?
https://community.lansweeper.com/t5/reports-analytics/hyper-v-guest-inventory/m-p/67059

[Garyr]

Yes but your report does not show CPU/vProcossors assigned to the guest. It only shows the CPU information for the host.

[Mister_Nobody]

You can add tblassets.processor to report

[Garyr]

That just adds the host processor information again

[Mister_Nobody]

No. It is guest CPU info.

You can do live migration of  a VM on the host with another CPU version, then rescan host and VM, then check CPU.

[rader]

Learn something new everyday. Even for us greybeards.

Great report btw.

[rader]

Perhaps I'm missing something, but generally the host processor is the guest processor, so either way it would be the same. Now if you limit the number of cores assigned to the guest, I could see wanting that on the report.

[Garyr]

Yes, we limit how many cores are assigned to the guests.

[Mister_Nobody · Accepted solution]

I have rewritten CPU-part: 

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,
  host_proc.Name As host_CPU,
  tblAssets.processor As guest_CPU,
  guest_proc.numberofcores as vCPU_cores,
  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 As host_proc On tblAssets1.AssetID = host_proc.AssetID
  Left Join tblProcessor As guest_proc On tblHyperVGuest.GuestAssetId =
      guest_proc.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