SCCM – WQL Query for Devices with Configuration Manager Client Installed and no System Center Endpoint Protection Installed

We have had a report that one of our WQL queries has not been working correctly, this is the one for devices that have Configuration Manager client installed but do not have System Center Endpoint Protection installed on them. The original post is here: WQL Query for Devices without Endpoint Installed So, we have looked into this and found the fault, we have now updated the original post, but in case you want quick access to it, below is the updated and confirmed working 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_COMPUTER_SYSTEM
       on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId
    where SMS_G_System_COMPUTER_SYSTEM.Name in
       (select distinct
        SMS_G_System_COMPUTER_SYSTEM.Name
    from  SMS_R_System
        inner join SMS_G_System_COMPUTER_SYSTEM
    on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId
        inner join SMS_G_System_ADD_REMOVE_PROGRAMS
    on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
        where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "System Center 2012 Endpoint Protection" )
    and SMS_R_System.Client = 1

This query can now be used to create a collection then you can deploy System Center Endpoint Protection to this collection to ensure all devices within the collection then get Endpoint installed. To find out how to add System Center Endpoint Protection as an application to your Configuration Manager so that you can deploy it to a collection, then please see the post below: How to add System Center Endpoint Protection as an Application in Configuration Manager

More Queries

Our full range of SQL and WQL Collection queries are available here.

Feedback

If you have any questions or feedback about this post, or if you would like us to create any queries for you, please go ahead and leave us a message below in the comments section and we will get back to you as quick as we can.