cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
markharry
Engaged Sweeper III
Is there a way to make a Dynamic Group that will show me all my Hyper-V host servers ?
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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]

View solution in original post

4 REPLIES 4
markharry
Engaged Sweeper III
Thanks Daniel for your help.
Daniel_B
Lansweeper Alumni
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
Engaged Sweeper III
Sorry, but that just seems to list all the Hyper-V guest machines, not the Host
Daniel_B
Lansweeper Alumni
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