Create a collection for co-managed computers

In this article, we will simply create a dynamic collection to group all the co-managed computers. This collection is built with a WQL query. As a reminder, co-managed machines are managed with both SCCM and Intune. In the same way, we can create a collection for the computers that are not co-managed. This collection could be useful for troubleshooting.

Create a dynamic collection of co-managed computers

First, go to Assets and Compliance > Device Collections.
Click Create Device Collection.
Provide a name and comment if needed.

Create a collection for co-managed computers : wizard

As always, don’t forget that you should not use the default collections. In Limiting collection, choose a collection other than All systems.
Click on Next.
Click on Add Rule > Query Rule.

Specify a name and then click on Edit Query Statement.
In the new window, click on Show Query Language. Enter the following query:

select *  from  SMS_R_System inner join SMS_Client_ComanagementState on SMS_Client_ComanagementState.ResourceId = SMS_R_System.ResourceId where SMS_Client_ComanagementState.ComgmtPolicyPresent = 1 AND SMS_Client_ComanagementState.MDMEnrolled = 1 AND SMS_Client_ComanagementState.MDMProvisioned = 1

Click OK to close the query windows.
Then click Next until the collection creation wizard is complete.
Once the collection is created, it should populate.

To group items that are not comanaged, you will use the following command:

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_Client_ComanagementState on SMS_Client_ComanagementState.ResourceID = SMS_R_System.ResourceId where SMS_Client_ComanagementState.MDMProvisioned = 0

Search for workstations co-managed via SQL Server

If you want to go through SQL Server, here is the query to use:

SELECT
    COMP.Name0 AS 'Name',
    CASE
        WHEN CMGT.MDMEnrolled = '0' THEN 'NO'
        WHEN CMGT.MDMEnrolled = '1' THEN 'YES'
        END AS 'MDMEnrolled',
    CASE
        WHEN CMGT.ComgmtPolicyPresent = '0' THEN 'NO'
        WHEN CMGT.ComgmtPolicyPresent = '1' THEN 'YES'
        END AS 'ComgmtPolicyPresent',
    CASE
        WHEN CMGT.HybridAADJoined = '0' THEN 'NO'
        WHEN CMGT.HybridAADJoined = '1' THEN 'YES'
        END AS 'HybridAADJoined'
FROM
    v_R_System COMP LEFT JOIN
    v_ClientCoManagementState CMGT on CMGT.ResourceID = COMP.ResourceID
WHERE
    CMGT.MDMProvisioned = '1'