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 Comments
OK. Thanks. will check it now and see how it goes.
Hi eswar,
Is it possible to pass multiple machines to check if they are part of how many collection.
Thanks mate.
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.
Hello,
I try running this query in my test LAB but it is not showing any information. It is showing blank report.
is the computer member of any collections ? this is very simple report without any complications.
Hi,
Can you execute those commands from Custom Queries on SCCM 2012?
Many Thanks
You can.Use Report Builder to create custom reports using the same quires.