Query returning collection membership (Full Version)

All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager



Message

Grimski -> Query returning collection membership (5/1/2008 4:24:08 PM)

Can anyone help me with an SQL Query? I am a novice in SQL and SCCM!

I would like to create a query that returns the collection name if the computer is a member of the collection. I have 20 collections and the computer will be a member of one of them and I am interested in the query returning the name of the collection the computer is a member of.

Or maybe there is another way / tool to easily see this information?



skissinger -> RE: Query returning collection membership (5/1/2008 9:09:53 PM)

I don't have a console in front of me; but you'd leverage v_fullcollectionmembership to get at that data.



mseely -> RE: Query returning collection membership (5/4/2008 4:44:55 AM)

V_FullCollectionMembership has the name of the system, but not the name of the Collection.
V_Collection has the name of the Collection, but not the name of the System

So, neither view by itself will give us what we are looking for.
But - both Views have the CollectionID of the collection ... 
so, by joining the 2 views using CollectionID, we get:

Select col.name
from v_fullcollectionmembership fcm
join v_collection col
on fcm.collectionid=col.collectionid
where fcm.name = 'ComputerName'

which will return the collection names that 'ComputerName' is part of.




phaustein -> RE: Query returning collection membership (5/4/2008 10:00:34 PM)

If you replace the 'ComputerName' value with @variable and create new prompts entry by clicking on the "prompts" button of the reports query window, you should be able to turn the above query into a dymanic one.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.296875