Query to search for MAC addresses with wildcard

Sometimes a machine doesn't want to PXE boot for imaging, doesn't show up in a hostname search, or has stale records in SCCM and we need to delete the object(s) to do what we want to do.  That's where a MAC address query can come in.  This query can also utilize wildcard searches using a "%" sign.  This can help if you are given a blurry phone picture of a MAC address and aren't able to determine if that's an '8' or a 'B', or any other characters.

select distinct SMS_R_System.MACAddresses, SMS_R_System.Name from  SMS_R_System where SMS_R_System.MACAddresses like ##PRM:SMS_R_System.MACAddresses## order by SMS_R_System.MACAddresses

Add this to your queries under Monitoring > Overview > Queries and run it as needed.

Managing IP Boundaries using IP Ranges

In our environment we consistently had IP boundary issues utilizing IP subnets, and recently discovered (via our network admins) that IP ranges would be considerably better than IP Subnets.  I had already been in the midst of converting the environment from IP Subnets to IP Ranges when I came across this article which proved the theory.  Therefore, here are some basic methods to start using IP ranges in your environment.

Convert SCCM Boundaries from IP Subnets to IP Ranges

First, you'll want to find a basic IP Calculator - there are many on Google although the one linked is what I used.  You'll also need access to the basic network layout of your environment - preferably acquired from your network admin.  The network layout should include either IP subnets with subnet masks (e.g. 255.255.240.0), or subnets with bit length (e.g. 10.81.64.0/20).  If you are given only subnets with subnet masks and not bit length, use a Subnet Mask Cheat Sheet to determine the bit length with subnets.  Then use the IP Calculator to determine the IP Range.  In SCCM the easiest way to convert is to go to the Properties of the Subnet Mask (under Administration > Hierarchy Configuration > Boundaries), change the Type dropdown to IP Address Range, then enter your IP ranges there. (If you create new IP Range entries alongside the IP Subnet entries, you'd still need to modify the new IP Ranges to add your Boundary Groups).

You will notice a pattern and probably remember the pattern as you do more subnets.  For me, it was that a /20 mask meant I would simply add 15 to the 3rd octet, so, for example, the complete range for a 10.50.32.0 subnet would be 10.50.32.1 to 10.50.47.254 (32+15=47).  Note that if you are doing /24 masks, it's pretty much pointless to convert since the range will be only that subnet anyway (e.g. 10.50.32.1 to 10.50.32.254).

Create a Device Collection based on IP Ranges in SCCM

If you'd like to create a collection based on IP Ranges, the best method is to use the subnet method with a range.  I have seen methods (i.e. here) which actually do not always work correctly since the way WQL works, it calculates only single digit numbers when sorting.  For instance, if your IP range is 10.50.96.1 to 10.50.111.254, and you setup the query as "greater than or equal to 10.50.96.1" and "less than or equal to 10.50.111.254", it will return 0 results, because WQL only reads the first digit in the 3rd octet (9) and compares that with the first number in the 3rd octet comparison (1).  So the method in that link will only work for specific IP ranges and not all of them.

Here's some examples on the correct way to use subnets with a range in your WQL query:

For a given IP subnet of 10.177.160.0/20:

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.IPSubnets like "10.177.16[0-9].0" or   SMS_R_System.IPSubnets like "10.177.17[0-5].0"

The actual range in the example is 10.177.160.1 through 10.177.175.254.  Since WQL works with single-digit numbers, this method must be used and can get a bit complicated.  

Here's another example for a multiple subnet entry of 10.124.0.0/20 and 10.124.16.0/21:

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.IPSubnets like "10.124.[0-9].0" or   SMS_R_System.IPSubnets like "10.124.1[0-9].0" or   SMS_R_System.IPSubnets like "10.124.2[0-3].0"

The ranges for this example are 10.124.0.1 through 10.124.15.254 and 10.124.16.1 through 10.124.23.254.  This can be combined to a range of 10.124.0.1 through 10.124.23.254, then is converted into the WQL above.

Note that it's not possible to just use 10.124.[10-19].0 in the above WQL query because of WQL's design to use single digits. 

Furthermore, if you have multiple ranges, you can go a bit further:

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.IPSubnets like "10.172.9[6-9].0" or SMS_R_System.IPSubnets like "10.172.1[0-1][0-9].0" or SMS_R_System.IPSubnets like "10.172.12[0-7].0"

With subnets of 10.172.96.0/20, 10.172.112.0/21, and 10.172.120.0/21, the subnets are converted and then shown in the above WQL.