query windows client and version for collection

Question

Hello,

I would like to know if there is a better query to filter on computer/server OS version to dynamically build collection.

my sample for w10 2004:

  select * 
from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.BuildNumber = "19041" and SMS_G_System_SYSTEM.SystemRole = "Workstation"

my sample for server 2019:

select *    
from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.BuildNumber = "17763" and SMS_G_System_SYSTEM.SystemRole = "Server"

Thank you for your answer.

Answer 1

@matteu31
Thank you for posting in Microsoft Q&A forum.
We can get the results without the class SMS_G_System_SYSTEM, like below:
For windows 10 2004:

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 = "19041" and SMS_G_System_OPERATING_SYSTEM.Caption like "%windows 10%"  

For server 2019:

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 = "17763" and SMS_G_System_OPERATING_SYSTEM.Caption like "%Windows Server%"  

---

If the response is helpful, please click "Accept Answer" and upvote it.
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.

Answer 2

Thank you for your answer :)

Is there a way to know if it's physical or virtual machine ?
I find it in : computer system -> provider but I don't know all the possible values...
I have vmware, Inc. but it will be different on hyper-v, citrix, nutanix, dell provider, hp provider, ...

Answer 3

@matteu31
We can know if it is a VM or physical machine by using SMS_R_System.IsVirtualMachine.
We can query the manufacturer by using SMS_G_System_SYSTEM_ENCLOSURE.Manufacturer.
I'm not very clear when you say "computer system -> provider".

Answer 4

Hello,

Thank you for your answer.
I think we are talking about the same "query".
manufacturer is the good word and not provider. Sorry, I'm french...

To have the good query, I need to test it on all virtual machine I suppose...
something like this query :
if manufactuer = %hyper-v% or if manufacturer = %vmware% or ... for each hypervisor type right ?

Answer 5

@matteu31
You may try to create a query in SCCM console with the query:

select * from SMS_G_System_SYSTEM_ENCLOSURE  

And then run the query get the result and check all the manufacturer of your client.