When I answered a Technet forum question I thought I would post this report here as well, the report lists all physical and virtual servers in a collection:
To implement it you need to create a Prompt called @varcollection
here is the SQL query:
select v_R_SYSTEM.Name0 as 'Virtual Servers', @varcollection as Collection
from v_R_SYSTEM inner join V_GS_COMPUTER_SYSTEM on V_GS_COMPUTER_SYSTEM.ResourceID =v_R_SYSTEM.ResourceId inner join V_GS_OPERATING_SYSTEM on V_GS_OPERATING_SYSTEM.ResourceId = v_R_SYSTEM.ResourceId
join v_FullCollectionMembership FCM on FCM.ResourceID =V_GS_COMPUTER_SYSTEM.ResourceID
where V_GS_COMPUTER_SYSTEM.Model0 like '%Virtual%' and V_GS_OPERATING_SYSTEM.Caption0 like '%Server%' and
FCM.CollectionID = @varcollection and v_R_System.Client0= 1
Order by v_R_SYSTEM.Name0
select v_R_SYSTEM.Name0 as 'Physical Servers', @varcollection as Collection
from v_R_SYSTEM inner join V_GS_COMPUTER_SYSTEM on V_GS_COMPUTER_SYSTEM.ResourceID =v_R_SYSTEM.ResourceId inner join V_GS_OPERATING_SYSTEM on V_GS_OPERATING_SYSTEM.ResourceId = v_R_SYSTEM.ResourceId
join v_FullCollectionMembership FCM on FCM.ResourceID =V_GS_COMPUTER_SYSTEM.ResourceID
where V_GS_COMPUTER_SYSTEM.Model0 not like '%Virtual%' and V_GS_OPERATING_SYSTEM.Caption0 like '%Server%' and
FCM.CollectionID = @varcollection and v_R_System.Client0= 1
Order by v_R_SYSTEM.Name0
-------------------------------------------
Here is the query for the Collection prompt:
begin
if (@__filterwildcard = '')
select distinct CollectionID, Name from v_Collection order by Name
else
select distinct CollectionID, Name from v_Collection
WHERE CollectionID like @__filterwildcard
order by Name
end