Creating ConfigMgr Collections containing systems with Computer Associations

Wow, that title is a mouthful eh?

Essentially this blog post is for those of you who want to automatically advertise task sequences based on SCCM computer associations. Here's two examples:

For a collection of systems based on the source resource in the computer association use the following:
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 inner join SMS_StateMigration on SMS_StateMigration.SourceClientResourceID = SMS_R_System.ResourceID

For a collection of systems based on the target resource in the computer assocation you can use this (note that the state store path cannot be null with this query):

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 inner join SMS_StateMigration on SMS_StateMigration.RestoreClientResourceID = SMS_R_System.ResourceID where SMS_StateMigration.StorePath not null

So there you go!