Hyper-V

[Cooldood]

Does anyone have a report showing all VM and their hosts? I would like to see which VM is on which host.

[rader · Accepted solution]

Here's some that I put together for my needs.

Hyper-V OS

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblProcessor.Name As Processor,
  tblProcessor.NumberOfCores As Physical,
  tblProcessor.NumberOfLogicalProcessors As Logical
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblHyperVGuest On tblAssets.AssetID = tblHyperVGuest.AssetID
  Inner Join tblOperatingsystem On
      tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName

Hyper-V Hosts and VM's

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName As [Guest Computer Name],
  tblAssets.IPAddress As [Guest IP Address],
  tHost.AssetName As [Host Computer Name],
  tHost.IPAddress As [Host IP Address]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join TblHyperVGuestNetwork On
      tblAssets.Mac = TblHyperVGuestNetwork.MacAddress
  Inner Join tblHyperVGuest On tblHyperVGuest.hypervguestID =
      TblHyperVGuestNetwork.HyperVGuestID
  Left Join tblAssets tHost On tHost.AssetID = tblHyperVGuest.AssetID
Where tblAssetCustom.State = 1
Order By [Host Computer Name]

Hyper-V Hosts and Guests

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress As [Guest IP Address],
  tHost.AssetName As [Host Computer Name],
  tHost.IPAddress As [Host IP Address],
  tblHyperVGuest.Enabledstate
From tblAssets
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join TblHyperVGuestNetwork On
      tblAssets.Mac = TblHyperVGuestNetwork.MacAddress
  Inner Join tblHyperVGuest On tblHyperVGuest.hypervguestID =
      TblHyperVGuestNetwork.HyperVGuestID
  Left Join tblAssets tHost On tHost.AssetID = tblHyperVGuest.AssetID
Where tblAssetCustom.State = 1
Order By [Host Computer Name],
  tblAssets.AssetName

Hyper-V Guests and Assigned Hosts

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress As [Guest IP Address],
  tHost.AssetName As [Host Computer Name],
  tHost.IPAddress As [Host IP Address],
  tblHyperVGuest.Enabledstate
From tblAssets
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join TblHyperVGuestNetwork On
      tblAssets.Mac = TblHyperVGuestNetwork.MacAddress
  Inner Join tblHyperVGuest On tblHyperVGuest.hypervguestID =
      TblHyperVGuestNetwork.HyperVGuestID
  Left Join tblAssets tHost On tHost.AssetID = tblHyperVGuest.AssetID
Where tblAssetCustom.State = 1
Order By [Host Computer Name],
  tblAssets.AssetName

Chart: Hyper-V hosts and guests

Select Top 1000000 Coalesce(tblAssets.AssetName, 'Unknown') As Host,
  Count(tblAssets.AssetID) As [# of Guests]
From tblAssets
  Inner Join tblHyperVGuest On tblAssets.AssetID = tblHyperVGuest.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Group By tblAssets.AssetName
Order By tblAssets.AssetName

Hyper-V: VM Rebuild Parameters (This one is to list most of the Hyper-V particulars if you have to restore to another host server)

Select Distinct Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  Ceiling(tblComputersystem.TotalPhysicalMemory / 1024 / 1024 / 1024) As
  [Memory in GB],
  tblComputersystem.NumberOfProcessors As [# Phy Processors],
  tblComputersystem.NumberOfLogicalProcessors As [# Log Processors],
  tblassets.IPAddress,
  tsysAssetRelationTypes.Name As RelationType,
  ChildAsset.AssetName As hyperlink_name_ChildAsset,
  '/asset.aspx?AssetID=' + Cast(ChildAsset.AssetID As nvarchar(10)) As
  hyperlink_ChildAsset,
  tblAssetRelations.Comments As [Using Backup Method],
  tblAssetRelations.StartDate As [Start Date],
  Case
    When tblAssetRelations.EndDate < GetDate() Then '#dddddd'
    Else '#ffffff'
  End As backgroundcolor
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Inner Join tblComputersystem On tblassets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetRelations On
      tblassets.AssetID = tblAssetRelations.ChildAssetID
  Inner Join tsysAssetRelationTypes On tblAssetRelations.Type =
      tsysAssetRelationTypes.RelationTypeID
  Inner Join (Select tblAssets.AssetID,
      tblAssets.Domain,
      tblAssets.IPAddress,
      tblAssets.AssetName,
      tsysAssetTypes.AssetTypename,
      tsysAssetTypes.AssetTypeIcon10
    From tblAssets
      Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
          tblAssets.Assettype
      Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID)
  ChildAsset On tblAssetRelations.ParentAssetID = ChildAsset.AssetID
Where tsysAssetRelationTypes.Name Like 'Backed Up To' And
  tsysassettypes.AssetTypename = 'Windows' And tblassetcustom.State = 1
Order By tblassets.AssetName,
  [Start Date] Desc

One thing I can't find or it's not scanable is the Generation of the Hyper-V VM. This may have to be setup in the VM's custom fields to be reportable.

Here's a pic of my dashboard for these reports. Don't mind the upper-right chart, it's still under testing.

Modify these reports as needed but that should give you a good start.