Query returning collection membership

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

Query returning collection membership

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager >> Query returning collection membership

Page: [1]

Message

<< Older Topic Newer Topic >>

Query returning collection membership - 5/1/2008 4:24:08 PM

Grimski

Posts: 1
Score: 0
Joined: 5/1/2008
Status: offline

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?

Post #: 1

RE: Query returning collection membership - 5/1/2008 9:09:53 PM

skissinger

Posts: 1577
Score: 94
Joined: 9/13/2001
From: Sherry Kissinger
Status: offline

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

_____________________________

mofmaster@smsexpert.com (version 2007) | http://www.smsexpert.com | http://www.sccmexpert.com
Microsoft MVP - SMS

Inventory Manager 2007 is here!

(in reply to Grimski)

Post #: 2

RE: Query returning collection membership - 5/4/2008 4:44:55 AM

mseely

Posts: 6
Score: 0
Joined: 5/4/2008
Status: offline

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.

(in reply to Grimski)

Post #: 3

RE: Query returning collection membership - 5/4/2008 10:00:34 PM

phaustein

Posts: 772
Score: 28
Joined: 3/21/2005
From: Washington, DC
Status: offline

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.

_____________________________

Hope this helps.
Paul

(in reply to mseely)

Post #: 4