query windows client and version for collection

matteu31 467 Reputation points
2021-02-01T17:46:34.407+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,871 questions
0 comments No comments
{count} votes

6 answers

Sort by: Most helpful
  1. AllenLiu-MSFT 41,136 Reputation points Microsoft Vendor
    2021-02-02T03:10:14.073+00:00

    @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.

    1 person found this answer helpful.
    0 comments No comments

  2. matteu31 467 Reputation points
    2021-02-02T09:48:05.797+00:00

    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, ...

    0 comments No comments

  3. AllenLiu-MSFT 41,136 Reputation points Microsoft Vendor
    2021-02-08T02:21:07.19+00:00

    @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".

    0 comments No comments

  4. matteu31 467 Reputation points
    2021-02-08T07:36:53.657+00:00

    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 ?

    0 comments No comments

  5. AllenLiu-MSFT 41,136 Reputation points Microsoft Vendor
    2021-02-08T08:47:44.697+00:00

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

    select * from SMS_G_System_SYSTEM_ENCLOSURE  
    

    65311-25.jpg

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

    0 comments No comments