SCCM report lists virtual servers and physical servers – per collection

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

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

Add a Comment

Your email address will not be published. Required fields are marked *