Make a Dynamic Group for Hyper-V Hosts

[markharry]

Is there a way to make a Dynamic Group that will show me all my Hyper-V host servers ?

[markharry]

Thanks Daniel for your help.

[Daniel_B · Accepted solution]

The following report lists all Hyper-V host computers:

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Lastseen,
  tblOperatingsystem.Caption As OS
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
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName

In case you would like to list the host computer of your guest machines, you need to use tables tblHyperVGuestNetwork, tblHyperVGuest and a second instance of tblAssets:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tHost.AssetName As [Hyper-V host]
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 [Hyper-V host]

[markharry]

Sorry, but that just seems to list all the Hyper-V guest machines, not the Host

[Daniel_B]

If you can't filter your Hyper-V hosts based on their machine name, model, AD OU or other criteria, this won't be possible in a dynamic group. However, you can easily create a report which lists all Hyper-V hosts.

• Select Reports\Create report
  
• in the list of tables at the right side, find tblHyperVGuest and doubleclick it
  
• change the SQL query at the bottom. Change "Select ..." to "Select Distinct ..."
  
• save the report under a new name and run it. It should list your Hyper-V hosts