I have the following WQL 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_G_System_EndpointProtectionStatus on SMS_G_System_EndpointProtectionStatus.ResourceID = SMS_R_System.ResourceId where SMS_G_System_EndpointProtectionStatus.SignatureOlderThan7Days = 1

and I'm able to run it just fine by using

Invoke-CMWmiQuery -Query $WQL -Option Lazy

But is there a way I can limit this to to run against a particular device collection, like you can do in the SCCM console?



You could create an SCCM Query, then run it against a Device Collection. https://docs.microsoft.com/en-us/sccm/core/servers/manage/create-queries

  • Thanks, but that's NOT what I'm after. I'm looking for a way to do this within PowerShell using the Invoke-CMWmiQuery cmdlet. – d3Xt3r Jun 2 at 22:34

I found the answer on Reddit, thanks to u/Johnny_Script:

In order to limit your results to one collection, you need to add a join SMS_FullCollectionMembership on SMS_FullCollectionMembership.ResourceID = SMS_R_System.ResourceID into your query so that you have access to the collection data. Then you need to add AND SMS_FullCollectionMembership.CollectionID = 'ABC0001' to the end of the query to limit to a specific collection. Update the ABC0001 with the CollectionID of one of your CollectionIDs. The final WQL statement should look like this:

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_G_System_EndpointProtectionStatus on SMS_G_System_EndpointProtectionStatus.ResourceID = SMS_R_System.ResourceId JOIN SMS_FullCollectionMembership on SMS_FullCollectionMembership.ResourceID = SMS_R_System.ResourceID 
WHERE SMS_G_System_EndpointProtectionStatus.SignatureOlderThan7Days = 1 and SMS_FullCollectionMembership.CollectionID = 'ABC0001'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.