MECM - Combined queries

Vivian Carol 31 Reputation points
2022-05-25T14:46:10.04+00:00

I am trying to create a collection with a query that grabs machines with certain Office products, but only active machines. I created this using the Criteria tab, but it still gives me lots of inactive machines.

What am I doing wrong?

select distinct 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_OFFICE_PRODUCTINFO on SMS_G_System_OFFICE_PRODUCTINFO.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OFFICE_PRODUCTINFO.Channel like "Semi-Annual%" or SMS_G_System_OFFICE_PRODUCTINFO.Channel like "Current Channel%" and SMS_G_System_CH_ClientSummary.ClientActiveStatus = 1

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
35,971 questions
Microsoft Configuration Manager

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amandayou-MSFT 11,046 Reputation points
    2022-05-26T05:53:15.193+00:00

    Hi @Vivian Carol ,

    To prevent the inactive machine in the result, we could create the collection query for active clients, and then from this collection, and query that grabs machines with certain Office products.

    We could put it in the query directly.

    205670-526.png
    The query is : 

    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_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId   
WHERE   
   SMS_G_System_CH_ClientSummary.ClientActiveStatus = 1

    And here is the article about creating collection based on Installed Application:
    https://www.anoopcnair.com/sccm-collection-based-on-installed-application/
    Note: Non-Microsoft link, just for the reference.

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  2. Vivian Carol 31 Reputation points
    2022-05-26T11:18:19.46+00:00

    Yes, that was one of the options. I have one for inactive machines.

    Thanks.