SCCM report computers member of which collection

 

There are cases,where a computer might be added to different collection and it would be difficult to find out the computer is member of which collections.Try this report to list all the collection the computer member of.

select a.CollectionId, b.Name from dbo.v_R_System r
join dbo.v_FullCollectionMembership a on R.ResourceID = a.ResourceID
join dbo.v_Collection b on b.CollectionID = a.CollectionID
Where R.Name0 =@machine

Prompt for machine:

select Name0 from v_R_System

If you want to try for user member of What collection ? below is the SQL report.

select
a.CollectionId,
b.Name
from
dbo.v_R_User R
join dbo.v_FullCollectionMembership a on R.ResourceID = a.ResourceID
join dbo.v_Collection b on b.CollectionID = a.CollectionID
Where
R.User_Name0 =@UserName

Prompt for User:

select User_Name0 from v_R_User

7 Responses to "SCCM report computers member of which collection"

    1. yes,you can do that as well..just as many prompts you want in the report like where R.Name0 =@machine1 or R.Name0 =@machine2 or R.Name0 =@machine3
      and create as many prompts what you used in Report.that should pull all the collections for the respective machines but that would be like mess.if one collection will be part of 30 collections and for 2 pcs,you get 60 rows ,so you total see pcs*number of collections in report that will be lenghty report instead of looking for one that we usually pick.

      Reply

Leave a Reply