Query with no result MECM/SCCM collection

Question

Query with no result MECM/SCCM collection

matteu31 532

Hello,

Sorry I don't find the MECM categorie to create this topic.

I'm using systemcenterdudes operationnal query to create some basics collection.

He created 3 to identify W10 supported / support expired soon / support expired. The query seems to not be W10 related so he limit the collection to an other collection with W10 devices only.

It works fine for W10 and I have correct results.

I tried to used the exact same query but limit it to W11 devices only and it doesn't work as expected.

I have different W11 version with some expired, some soon expired but the query doesn't show them on the correct collection.

The query used is this one for expired soon :

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
 LEFT OUTER JOIN SMS_WindowsServicingStates ON SMS_WindowsServicingStates.Build = SMS_R_System.build01 AND SMS_WindowsServicingStates.branch = SMS_R_System.osbranch01 where SMS_WindowsServicingStates.State = '3'

This one for Expired :

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
 LEFT OUTER JOIN SMS_WindowsServicingStates ON SMS_WindowsServicingStates.Build = SMS_R_System.build01 AND SMS_WindowsServicingStates.branch = SMS_R_System.osbranch01 where SMS_WindowsServicingStates.State = '4'

Is there something wrong here ?

0 comments

2 answers

Your answer

Answer 1

Jason Nguyen Tran 25,120 Independent Advisor

Hi matteu31,

The root of the issue is that the System Center Dudes queries were originally designed around Windows 10 servicing state attributes. Windows 11 uses updated build and branch values, and in some cases the SMS_WindowsServicingStates table does not align exactly with the same fields used for Windows 10. This means that simply reusing the query with a Windows 11 collection limitation may not correctly map the servicing state.

To troubleshoot, I recommend first verifying that your Windows 11 devices are reporting the correct build and branch values in the SCCM database. You can do this by running a simple query against SMS_R_System to confirm the build01 and osbranch01 fields. If those values are missing or different, the join with SMS_WindowsServicingStates will fail.

Another approach is to use the built-in “Windows Servicing” collections in MECM, which have been updated to support Windows 11. These collections rely on the proper mapping of servicing states and may save you time compared to custom SQL queries. If you prefer custom queries, you’ll need to adjust the join conditions to reflect the Windows 11 branch values (for example, “co_release” or “22H2” depending on your environment).

I hope the response provided some helpful insight. If this answer is helpful, please don’t forget to hit “Accept Answer”

Jason.

matteu31 532 Reputation points

2026-05-19T12:04:12.0433333+00:00
Hello @Jason Nguyen Tran ,

Thanks for your answer.

Based on this, I understand Windows 11 and Windows 10 do not use the same way to know the support expiration.

If I execute this query :

SELECT [Name0],[Operating_System_Name_and0],[Build0] FROM [CM_SUA].[dbo].[vSMS_R_System]

The column Buil0 is equall to NULL for ALL my devices (windows 10 or Windows 11).

Does that mean I need to enable some classes in inventory ?

Then, I don't know if there is a link but I don't have Asset intelligence installed (because deprecated and removed on 2603)

Then, Where are the "Windows servicing" built in collections ? When I go to Device collections on Asset and compliance Tab, I don't have any "Windows servicing" collection. If you're talking about Software library -> Windows servicing and click on the desirect windows version, this doesn't give me any information about support state.
Jason Nguyen Tran 25,120 Reputation points Independent Advisor

2026-05-20T04:07:04.2966667+00:00

To clarify: the Build0 field in vSMS_R_System is not always populated by default. This typically means that the hardware inventory classes required to collect OS build information are not enabled in your environment. You can confirm this by checking your Client Settings in MECM and ensuring that the Operating System inventory classes are selected. Once enabled, clients will report the build values during their next inventory cycle.

Regarding Asset Intelligence, you are correct that it has been deprecated and removed in recent builds, so it is not related to this issue. The missing build data is tied to inventory configuration rather than Asset Intelligence.

As for the “Windows Servicing” collections: these are not found under Device Collections in the Assets and Compliance workspace. Instead, they are located in the Software Library workspace under Windows Servicing. There you can view servicing plans and create collections based on Windows versions and servicing states. These built-in collections are designed to track Windows 10 and Windows 11 servicing milestones without requiring custom SQL queries.

In short, enabling the right inventory classes will populate the Build0 field, and using the Software Library’s Windows Servicing node will give you the official collections for support state tracking. Custom queries can still be used, but they need to be adjusted to align with Windows 11’s branch and build values once the inventory data is available.

I hope this explanation helps clear up the confusion and gives you a path forward. If this answer is helpful, please don’t forget to hit “Accept Answer”.

Jason
matteu31 532 Reputation points

2026-05-20T07:16:24.5366667+00:00

Hello,

Thanks for your answer @Jason Nguyen Tran .

I'm sorry but I don't find where I can use built in device collection to get support information.

When I go to Windows servicing, this is what I see, and there is no information about support date

Answer 2

The WQL syntax in those queries is valid, but the behavior depends on how the collection is limited and how Windows 11 devices are identified in Configuration Manager.

Key points to verify:

Collection limiting and OS targeting
The queries shown return all devices whose SMS_WindowsServicingStates.State is 3 (soon to expire) or 4 (expired). They are not OS-specific. For Windows 10, the collections work because they are limited to a Windows 10–only collection. For Windows 11, the same approach must be used, but with a correctly defined Windows 11–only limiting collection. For example, a query-based collection for a specific OS can be built by joining SMS_R_System with the OS inventory class and filtering on OS properties (such as SKU or version). The documentation shows how to do this for multi-session SKUs:
```
   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_OPERATING_SYSTEM on
     SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId
   where
     SMS_G_System_OPERATING_SYSTEM.OperatingSystemSKU = 175
```
The same pattern should be used to define a Windows 11 devices collection (with the appropriate OS filter), and then the servicing-state collections should be limited to that collection.
Use collection limiting instead of embedding OS logic
Configuration Manager supports collection-limiting queries in Extended WQL. A query can be limited to a specific collection either by:
- Setting LimitToCollectionId (for example, when creating a query with New-CMQuery), or
- Using the console’s Limiting collection field when creating a device collection.
The servicing-state queries should remain generic (only filter on State = '3' or State = '4') and rely on the limiting collection to scope them to Windows 11 devices.
Confirm the Windows 11 collection is correct
If the Windows 11 limiting collection is misconfigured (for example, incorrect OS filter, wrong SKU, or missing OS inventory), then the servicing-state collections will not show the expected Windows 11 devices even though the WQL for State = '3' or State = '4' is correct. Validate that:
- The Windows 11 collection is query-based and correctly filters on Windows 11 OS properties.
- Devices appear in that Windows 11 collection as expected.
- The servicing-state collections are limited to that Windows 11 collection.

How to implement via PowerShell (optional)
If creating or adjusting these collections via PowerShell, use Add-CMDeviceCollectionQueryMembershipRule with the servicing-state WQL and ensure the target collection is already limited to Windows 11 devices:

   $wql = "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 LEFT OUTER JOIN SMS_WindowsServicingStates ON SMS_WindowsServicingStates.Build = SMS_R_System.build01 AND SMS_WindowsServicingStates.branch = SMS_R_System.osbranch01 where SMS_WindowsServicingStates.State = '3'"
   
   Add-CMDeviceCollectionQueryMembershipRule -CollectionName "Win11 Servicing Soon" -QueryExpression $wql -RuleName "Win11 Servicing Soon"

The collection "Win11 Servicing Soon" should itself be limited to the Windows 11 devices collection.

In summary, the WQL for State = '3' and State = '4' is structurally correct; the issue is likely with how Windows 11 devices are identified and used as the limiting collection. Ensure the Windows 11 collection is correctly defined and used as the limiting collection for the servicing-state collections.

References: