Create SCCM Collection Based on IP Address Default Gateway

Let’s learn how to create SCCM Collection Based on IP Address or Default Gateway. The SCCM (aka ConfigMgr) collection queries based on WQL are powerful. You need to be careful and test them before implementing these WQL queries in production.

I’m not a big fan of creating SCCM device collections based on IP Address range. Another option is to create an SCCM collection based on the Default Gateway IP Address. Check out my recommendation at the bottom of the post.

This post is for educational purposes, and you need to make sure all the WQL queries are tested in a lab or staging environment. You can create WQL queries using the method explained in Easiest Method To Create SCCM WQL Queries.

You can verify SCCM Collection WQL Query using the Preview Tool. This is the best tool to analyze the SCCM collection WQL queries performance and the impact of badly written WQL queries.

• Create SCCM dynamic device Collection for Visual Studio Upgrade
• SCCM Collection Best Practices for Dynamic Collection
• SCCM Collection Based On Installed Application Version

Check IP Address and Default Gateway Details from Resource Explorer

Let’s check IP Address and Default Gateway Details from SCCM resource explorer. This check helps to confirm whether the hardware inventory is working or not. These details available in resource explorer can help us with troubleshooting scenarios.

You can understand which is the best option to select the IP Address and Default Gateway from the following attribute class called Network Adapter Configuration. This attribute class is coming out of SCCM Hardware Inventory.

NOTE! – You can have the IP Address details as part of the System Resource – IP Addresses table. But I don’t recommend using that IP address information collected through heartbeat discovery.

• Launch SCCM Console.
• Navigate to \Assets and Compliance\Overview\Devices\ and select one device.
• Right-click on one of the devices and select Start, and click on Resource Explorer.

You can navigate to Network Adapter Configuration and check the list of IP Addresses and Default Gateway information collected from the client.

Create SCCM Collection Based on IP Address and Default Gateway

Let’s see how to create an SCCM collection based on IP Address and Default Gateway information collected from Windows 11 or Windows 10 devices. You can create a collection based on any Default Gateway of the Windows device. I show how to create a collection based on the IP Addresses Range and Default Gateway.

Let’s create an SCCM collection using the following steps.

• Open SCCM Admin console.
• Navigate to \Assets and Compliance\Overview\Device Collections.
• Right Click Device Collection node and select Create Device Collection.

• Enter the Name Of the Collection – HTMD IP Range 10.1.0.1.
• Enter the Comment of the Collection – SCCM Collection WQL Query for IP Range 10.1.0.1 to 10.1.0.9.
• Select the Limiting collection – Select a collection to use as a limiting collection.

What is SCCM Limiting Collection? The limiting collection establishes the resources you can add to this collection using membership rules. And click on the Next button to continue.

Create WQL Query to find out the Devices with specific IP Addresses and Default Gateway

Let’s try to Create WQL Query to find out the Devices with specific IP Addresses and Default Gateway. In this section of the post, let’s learn how to create WQL Query to create an SCCM Dynamic Collection. You can create WQL Query to find out the devices with specific IPs and Default Gateway.

The membership rules determine the resources included in the collection and when it updates. You can use membership rules to add specific attributes or a set of attributes from the list of WMI tables. In this case, it’s based on the Network Adapter Configuration of a particular Windows 10 or Windows 11 device.

The following is the sample screenshot to show where you can check the IP Address and other attributes collected from the SCCM client and stored in the database.

• From the Membership Rules page of the Create User Collection Wizard, in the Add Rule list,
• Select the type Query Rule membership rule for this collection.

You can configure multiple query rules for each collection. Select Query Rule to continue with the creation of dynamic device collection.

On the Query Rule Properties windows, specify the following information, such as the query’s name, resource class, etc.

• Enter the Unique Name of the Query – HTMD Collection Based on IP Range.
• Select the Resource Class as System Resource for device collection.
• Click on the Edit Query Statement button to continue building dynamic queries from Query Statement Properties.
• Click on the Criteria tab to continue.
• You need to click on the Show Query Language button from the results window to build the dynamic device collection based on the IP Addresses and Default Gateway.

NOTE! – It’s important to understand the process of creating the dynamic WQL query by yourselves. However, I’ll share the WQL queries for different scenarios below in the following sections for this scenario. So that you can copy and paste.

Remove the default WQL query from the Query Language window. Copy and Paste the WQL queries given below and test the scenarios you want. Let’s find out the options to create SCCM Collection Based on IP Address and Default Gateway IP.

SCCM Dynamic Collection Based on Default Gateway IP

Let’s use the following WQL query to test and create SCCM Dynamic Collection Based on Default Gateway IP. The NETWORK_ADAPTER_CONFIGURATION attribute class contains the default gateway information, and you can use that information to build a collection.

• Default Gateway IP = 10.1.0.1

SCCM Collection WQL Query for IP Range 10.1.0.1 to 10.1.0.9

Let’s find out SCCM Collection WQL Query for IP Range 10.1.0.1 to 10.1.0.9. You need to perform a lot of testing to understand which is the best suitable query for you. The following examples help to create SCCM Collection Based on IP Address and Default Gateway IP.

• IP Range = 10.1.0.1 to 10.1.0.9

NETWORK_ADAPTER_CONFIGURATION contains the IP Address column, and this includes a list of IP addresses collected via hardware inventory. Depending on the order of the IP address, you might need to change the WQL queries.

I have given 3 examples below for educational purposes only. I have not performed end-to-end production testing of these queries. I recommend testing this in a lab environment before trying it out in production SCCM.

Example #1 – The WQL query works for most use cases unless the IP you are looking for is the last one in the list.

select *  from  SMS_R_System inner join SMS_G_System_NETWORK_ADAPTER_CONFIGURATION on SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = SMS_R_System.ResourceId where SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress like '%10.1.0.[1-9],%'

Example #2 – The WQL query works for most of the use cases unless the IP you are looking for is the first or last one in the list IP list as part of hardware inventory.

select *  from  SMS_R_System inner join SMS_G_System_NETWORK_ADAPTER_CONFIGURATION on SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = SMS_R_System.ResourceId where SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress like '%,10.1.0.[1-9],%'

Example #3 The following query might not work for the IP range example, 10.1.0.1 to 10.1.0.9 because % at the end might add any number such as 10.1.0.10, 10.1.0.90, etc… unless you add NOT LIKE and condition to the SQL query.

select *  from  SMS_R_System inner join SMS_G_System_NETWORK_ADAPTER_CONFIGURATION on SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = SMS_R_System.ResourceId where SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress like '%10.1.0.[1-9]%'

SCCM Collection WQL Query for IP Range 10.1.0.11 to 10.1.0.19

Let’s find out the SCCM Collection WQL Query for IP Range 10.1.0.11 to 10.1.0.19. You need to perform a lot of testing with the 3 WQL queries given below.

NOTE! – I don’t recommend using these queries in a production environment, which are just for educational purposes. Three examples given in the above section are applicable to this scenario because of the same reasons.

• IP Range = 10.1.0.11 to 10.1.0.19

The WQL query works only when the IP address list starts with IP 10.1.0.*. More scenarios are explained below.

select *  from  SMS_R_System inner join SMS_G_System_NETWORK_ADAPTER_CONFIGURATION on SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = SMS_R_System.ResourceId where SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress like '10.1.0.[1][1-9],%'

I think the following WQL query should work even if you have an IP that you are looking for anywhere in the IP list (except the last IP) as part of the SCCM hardware inventory. I have not tested this scenario, though.

select *  from  SMS_R_System inner join SMS_G_System_NETWORK_ADAPTER_CONFIGURATION on SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = SMS_R_System.ResourceId where SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress like '%10.1.0.[1][1-9],%'

If the IP range that you are looking for is the last IP in the hardware inventory list, then you might need to be more careful to get the appropriate results. So the SCCM collection query will be tricky, and I don’t recommend using the IP Range query option for creating SCCM device collections.

select *  from  SMS_R_System inner join SMS_G_System_NETWORK_ADAPTER_CONFIGURATION on SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = SMS_R_System.ResourceId where SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress like '%10.1.0.[1][1-9]%'

SCCM Collection WQL Query for IP Range 10.1.0.100 to 10.1.0.199

Let’s find out SCCM Collection WQL Query for the IP Range 10.1.0.100 to 10.1.0.199. The following query is much after the above-mentioned WQL queries to build SCCM device collection because it has the maximum allowed digits in an IP Address (3 digits – maximum allowed value of IP is 254).

NOTE! – Three examples given in the above section are applicable for this scenario because of the same reasons. The following example helps to create SCCM Collection Based on IP Address and Default Gateway IP.

select *  from  SMS_R_System inner join SMS_G_System_NETWORK_ADAPTER_CONFIGURATION on SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = SMS_R_System.ResourceId where SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.IPAddress like '%10.1.0.[1][0-9][0-9]%'

Conclusion – SCCM Collection Based on IP Address Default Gateway

Let me conclude this post called SCCM collection based on IP Address or Default Gateway with some takeaways. You must test the above WQL queries in a Test or LAB environment and assess them before even thinking of configuring this into the production environment.

I don’t recommend using the IP addresses range to build dynamic collections. It would be very difficult to manage and increase the risk of accidental deployments. I would strongly recommend avoiding this solution. This post is again only for educational purposes.

Let me know in the comments section below if you have a better solution for this process.

Author

Anoop C Nair is Microsoft MVP! He is a Device Management Admin with more than 20 years of experience (calculation done in 2021) in IT. He is Blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. He writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc.