Collection query based on a machine being a member of another collection

Earlier today I was trying to create a collection that showed all machines that were in another collection.

This has to be done using a sub select query and the first one I used brought back all machines that were in a collection, based on that collection ID:

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 ResourceID in
(select ResourceID
from SMS_FullCollectionMembership
where CollectionID = “NOV00062”)

Unfortunately this only partly solved the clients problem, as he wanted to bring back all machines that were part of a collection where the collection name started with “Lab:”. This meant adding a join to the query, as the SMS_FullCollectionMembership table doesn’t include the collection name, only the collection ID:

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_FullCollectionMembership  
JOIN SMS_Collection on SMS_FullCollectionMembership.CollectionID = SMS_Collection.CollectionID
where SMS_Collection.name LIKE “Lab:%”)

Problem solved 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: