1

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?

Thanks!

d3Xt3r
  • 121
  • 4

2 Answers2

2

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

SolidSid
  • 151
  • 4
  • 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 02 '19 at 22:34
1

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'
d3Xt3r
  • 121
  • 4