Share via

Collection Query Help

Zachery Chandler 1 Reputation point
2021-02-04T20:04:05.73+00:00

I am trying to modify this query to exclude windows 2003 servers. Any help would be much appreciated I am not a SQL guy at all

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.Client is null

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,364 questions
Microsoft Configuration Manager

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Gary Blok 1,736 Reputation points
    2021-02-05T04:23:32.653+00:00

    You'd be better off doing another Collection with a Query for Server 2003, then exclude it from your collection.

    You can actually look at this script, as it would create all of the collections you'd probably want, then create your own with Includes & Excludes to get your desired group of machines: https://github.com/prae1809/PowerShell-Scripts/tree/master/OperationalCollections

    Also note, Collections are not SQL, they are WQL: https://learn.microsoft.com/en-us/mem/configmgr/core/servers/manage/create-queries

    64339-image.png

    0 comments
  2. AllenLiu-MSFT 43,061 Reputation points Microsoft Vendor
    2021-02-08T02:32:03.55+00:00

    @Zachery Chandler
    Thank you for posting in Microsoft Q&A forum.
    You may use the query like below: 

    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.Client is null and OperatingSystemNameandVersion not like '%Server 5.2%'

    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.

    0 comments