NEW Collection based on Windows build value query fails to include all devices

[User McUser]

Greetings! We have multiple collections on our MECM server for various Windows Build values to try and identify those devices in our environment that need upgrading. We use a query to control membership but in some cases, the query is quite clearly not including some devices that qualify.

For example, this is the query I'm using for 21H2:

select * 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.BuildNumber = "19044"

If I compare that collection's membership with the all client devices collection, there are 14 PCs (all of which definitely show as having build 19044 in the MECM console) which do not end up in the collection using the query. I have forced multiple Hardware Inventory cycles on these PCs and tried a few variations on the query with no luck.

Any idea what the problem might be?

 

[AhmedLS]

Greetings! We have multiple collections on our MECM server for various Windows Build values to try and identify those devices in our environment that need upgrading. We use a query to control membership but in some cases, the query is quite clearly not including some devices that qualify.

For example, this is the query I'm using for 21H2:

select * 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.BuildNumber = "19044"

If I compare that collection's membership with the all client devices collection, there are 14 PCs (all of which definitely show as having build 19044 in the MECM console) which do not end up in the collection using the query. I have forced multiple Hardware Inventory cycles on these PCs and tried a few variations on the query with no luck.

Any idea what the problem might be?

try this one

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 where SMS_R_System.Build = "10.0.19044"

 

[Garth]

Greetings! We have multiple collections on our MECM server for various Windows Build values to try and identify those devices in our environment that need upgrading. We use a query to control membership but in some cases, the query is quite clearly not including some devices that qualify.

For example, this is the query I'm using for 21H2:

select * 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.BuildNumber = "19044"

If I compare that collection's membership with the all client devices collection, there are 14 PCs (all of which definitely show as having build 19044 in the MECM console) which do not end up in the collection using the query. I have forced multiple Hardware Inventory cycles on these PCs and tried a few variations on the query with no luck.

Any idea what the problem might be?

I don't know why I'm only seeing this now. But to answer your question directly.

Where exactly are you looking for the OS details? there are two different places to look. So Each of them has different solutions.

Did you confirm that HW inventory is making to the ConfigMgr db?

Troubleshoot ConfigMgr Hardware Inventory

Many years ago, I published a blog post to help folks track down issues involving Hardware Inventory. By accident, while helping someone troubleshoot ConfigMgr hardware inventory issues, I noticed that a step was missing. In 99% of cases, no one would need or notice this step, but since I...

www.recastsoftware.com

 

[User McUser]

try this one

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 where SMS_R_System.Build = "10.0.19044"

This works!

There are two devices that still don't join the collection but it looks like they haven't ever run a proper hardware inventory which would explain it.

Thank you!