query - all computers in this collection and any subcollections

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 - all computers in this collection and any subcollections

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 - all computers in this collection and any subcollections

Page: [1]

Message

<< Older Topic Newer Topic >>

query - all computers in this collection and any subcol... - 9/24/2008 5:57:46 PM

rpowell319

Posts: 8
Score: 0
Joined: 8/28/2008
Status: offline

Hello,

Was wondering if anyone knows how to build a query that basically returns "All computers that are contained within this collection and any subcollection". Essentially I want to add this query to a top level collection so that it populates with every computer that is in the collection structure. So I have a top level collection called "Root1", it has two subcollections called "Sub1" and "Sub2". Sub1 has several computers call them c1 and c2 and Sub2 has several different computers cal then c3 and c4. Want the Root1 collection to dynamically update its membership with all computers in sub1 and sub2 based upon a query so that it essentially contains c1, c2, c3, and c4.

Hope this makes sense. Has anyone done this and have an example query?

Thanks
Russ

Post #: 1

RE: query - all computers in this collection and any su... - 9/24/2008 6:45:04 PM

nickmo

Posts: 32
Score: 0
Joined: 8/25/2008
Status: offline

Setup your top level collection to have multiple queries. Design each query to return all resources records - then filter that query through the collection that you need to monitor. It's alot of manual work and you'll need to create a new query for any new sub collections, but it'll work.

Other than that, I'm not sure building such a query is possible because the only data you can access in the WQL is inventory information from clients. Though I'm sure something could be hacked. If you find a method for this, I'd be interested in hearing it.

_____________________________

nick

(in reply to rpowell319)

Post #: 2

RE: query - all computers in this collection and any su... - 9/26/2008 6:03:42 AM

nbarker

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

Russ

Try the following Membership query rule against the Root collection, changing the AA1000a1 and AA1000a2 for the CollectionID of your Sub1 and Sub2 collections (CollectionID can be easily seen on the Properties page of a collection).

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId in (select resourceid from sms_cm_res_coll_AA1000a1) or SMS_R_System.ResourceId in (select resourceid from sms_cm_res_coll_AA1000a2)

Kandle

(in reply to rpowell319)

Post #: 3

RE: query - all computers in this collection and any su... - 9/26/2008 1:20:29 PM

rpowell319

Posts: 8
Score: 0
Joined: 8/28/2008
Status: offline

Thanks, I will give it a shot

Russ

(in reply to nbarker)

Post #: 4

RE: query - all computers in this collection and any su... - 9/26/2008 1:44:02 PM

nickmo

Posts: 32
Score: 0
Joined: 8/25/2008
Status: offline

quote:

ORIGINAL: nbarker

Russ

Try the following Membership query rule against the Root collection, changing the AA1000a1 and AA1000a2 for the CollectionID of your Sub1 and Sub2 collections (CollectionID can be easily seen on the Properties page of a collection).

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId in (select resourceid from sms_cm_res_coll_AA1000a1) or SMS_R_System.ResourceId in (select resourceid from sms_cm_res_coll_AA1000a2)

Kandle

That is another solution, which I hadn't considered doing it like that. While it does work, it is still a query static data where you have to create a subselect for every [sub] collection. Again, one could as well create a query to return all resources and then filter that through the collection. Do you know of a method where someone could define a parent collection ID, but have it return results for all child [sub] collections?

_____________________________

nick

(in reply to nbarker)

Post #: 5

RE: query - all computers in this collection and any su... - 9/26/2008 1:49:42 PM

jnelson993

Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

Yes, that's very possible. It's not that pretty because of WQL language restriction, but I've got something...hold on a sec...

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to nickmo)

Post #: 6

RE: query - all computers in this collection and any su... - 9/26/2008 2:22:58 PM

jnelson993

Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

Crap...it's almost like there's been some changes in the way WQL is handled...now I'm getting WBEM_E_FAILED errors running something I knew worked for this...gimme a little time. My day job takes priority. I'm pretty sure we can do this...

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to jnelson993)

Post #: 7

RE: query - all computers in this collection and any su... - 9/26/2008 3:10:52 PM 1 votes

jnelson993

Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

OK, here you go. It's not pretty, like I said, because of the limitations of WQL. Essentially what you need to do is use SMS_CollectToSubCollect in a subquery to pull the level 1s and use another subquery for the level 2s, etc. This query finds machines in collections and subcollections up to 4 levels deep. Just follow the same pattern to add a 5th and 6th level if your collection hierarchy goes that far.
SELECT DISTINCT
collectionID,
resourceID
FROM
SMS_FullCollectionMembership
WHERE collectionID = '01006878'
OR collectionID IN (SELECT
                         subCollectionID
                       FROM
                         SMS_CollectToSubCollect
                       WHERE ParentCollectionID = '01006878')
OR collectionID IN (SELECT
                         subCollectionID
                       FROM
                         SMS_CollectToSubCollect
                       WHERE ParentCollectionID IN (SELECT
                                                       subCollectionID
                                                     FROM
                                                       SMS_CollectToSubCollect
                                                     WHERE ParentCollectionID = '01006878'))
OR collectionID IN (SELECT
                         subCollectionID
                       FROM
                         SMS_CollectToSubCollect
                       WHERE ParentCollectionID IN (SELECT
                                                       subCollectionID
                                                     FROM
                                                       SMS_CollectToSubCollect
                                                     WHERE ParentCollectionID IN (SELECT
                                                                                     subCollectionID
                                                                                   FROM
                                                                                     SMS_CollectToSubCollect
                                                                                   WHERE ParentCollectionID = '01006878')))

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to jnelson993)

Post #: 8

RE: query - all computers in this collection and any su... - 9/26/2008 3:13:01 PM

jnelson993

Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

Don't forget to change the collectionID in all the spots to the parent collectionID you want

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to jnelson993)

Post #: 9

RE: query - all computers in this collection and any su... - 10/9/2008 4:36:24 PM

rpowell319

Posts: 8
Score: 0
Joined: 8/28/2008
Status: offline

Hello all,

Wanted to say thank you for the assistance with this and sorry it took me so long to get back to this. I finally got my “Sub-Collection Member Rollup Query” working properly. In case others find it useful, I have attached it as a text file. Let me know if I need to zip it or have it in some other format for it to get through.

Essentially to make this work:
1)      create a new sms query with an object type of <unspecified>.
2)      Click the “Edit Query Statement…” button
3)      Paste the contents of the text file into the Query Statement edit window.
4)      Save the query with a name that is descriptive such as “Sub-Collection Member Rollup – 4 Deep”
5)      Go to the properties of the top level collection where you want to rollup sub-collection members
6)      Make a note of the “Collection ID” from the bottom of the collection properties general tab
7)      Go to the Membership rules tab and click on the query button to create a new membership rule base upon a query
8)      Name the rule appropriately and click the “Import Query Statement…” button
9)      Import the query you saved in step 4
10)   Click the “Edit Query Statement…” button
11)   Click the “Show Query Language…” button
12)   Replace all instances of the text “Enter the Top Level CollectionID # Here” with the collection id number of the top level collection from step 6
13)   Save the query / Membership rule
14)   Update and refresh the top level collection membership

This query will rollup all client system members from all sub-collections (up to 4 levels deep). If you need to go more than 4 levels, just modify the original query and add another sub-select following the same pattern.

Thanks again for the assistance. This is going to make my life much easier.

Russ

Attachment (1)

(in reply to jnelson993)

Post #: 10

Page: [1]

All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager >> query - all computers in this collection and any subcollections

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts

0.219