Listing ConfigMgr IP Range Boundaries with their Default Gateway(s)

I was tasked with very quickly (few hours notice) having to produce a CSV file listing all IP Range type boundaries together with the Default Gateway(s) used in each IP Range. This is what I came up with.

SQL Data used

There are many ways to get inventoried info about IP Address and Default Gateway from ConfigMgr clients, but for this specific info, the v_Network_DATA_Serialized view is useful.

For boundary information you would normally use the vSMS_Boundary view, but for this script I decided on using the BoundaryEx table since it already has calculated numeric values for the first and last IP Address in an IP Range type boundary.

The Script

So this is probably one of the ugliest, and non-efficient scripts I have written, but I couldn't (quickly at least), figure out a SQL query that generated the data I needed. So PowerShell, and nested queries to the rescue. The script is using the Invoke-SQLCmd, so make sure to install the SQL Server PowerShell module before you run it.

The script runs two super-lightweight SQL queries, stores the data in two arrays, and then let PowerShell have it's go on them.

Warning: Don't run this script on your production server, run it on a client instead. In 25000+ client ConfigMgr environments, it will take several minutes to complete, and use a bit of memory and CPU.

Feedback: If you have any improvement tips, especially around data validation and speed, please let me know in the comments below.

# This script requires the SQL Server PowerShell Module, install via Install-Module -Name SqlServer

$DataSource = "CM01"
$Database = "CM_PS1"
$ExportPath = "E:\Setup\Result.csv"

# Get client IP Info
$ClientQuery = "SELECT DISTINCT DefaultIPGateway0 AS Gateway, IPAddress0 AS IPAddress, IPSubnet0 as Subnet FROM v_Network_DATA_Serialized WHERE (IPSubnet0 IS NOT NULL) AND (IPAddress0 NOT LIKE '%:%')"
$Clients = Invoke-Sqlcmd -Query $ClientQuery -server $datasource -Database $database
 
# Get IP Range type boundaries
$query = "select Name, Value, NumericValueLow, NumericValueHigh from BoundaryEx where BoundaryType = '3'"
$IPRanges = Invoke-Sqlcmd -Query $query -server $datasource -Database $database

# Get the data and build a new arraylist
[System.Collections.ArrayList]$NetworkInfo = @()
foreach ($Client in $Clients){

    # Get only IPv4 info in case the SQL Query filter missed them
    $IPAddress = ($Client.IPAddress | Select-String -Pattern "\d{1,3}(\.\d{1,3}){3}").Matches.Value


    # Convert IP Address to Numeric Value
    $NumericIP = [system.net.ipaddress]::Parse($IPAddress).GetAddressBytes()
    [array]::Reverse($NumericIP)
    $NumericIP = [system.BitConverter]::ToUInt32($NumericIP, 0)

    # Check if IP Address is within the IP Range, loop through each IP Range boundary until I can figure out something more efficient
    foreach ($IPRange in $IPRanges){
        
        # Write-Host "Checking IP Range: $($IPRange.Name), for IP Address: $($Client.IPAddress)"
        $IPAddressInRange = $IPRange.NumericValueLow -le $NumericIP -and $NumericIP -le $IPRange.NumericValueHigh
        If ($IPAddressInRange -eq $true){

            $obj = [PSCustomObject]@{

                # Add values to arraylist
                # IPAddress  =  $Client.IPAddress
                IPRangeName = $IPRange.Name 
                IPRangeValue = $IPRange.Value
                Gateway  = ($Client.Gateway | Select-String -Pattern "\d{1,3}(\.\d{1,3}){3}").Matches.Value
            }

            # Add all the values
            $NetworkInfo.Add($obj)|Out-Null

            # OK, found a match for this IP Address, no need to look further (saves some processing at least)
            Break
        }
        
    }

}

# Export only unique IP Ranges with their default gateway
$NetworkInfo | Select IPRangeName, IPRangeValue, Gateway -Unique | Export-Csv -Path $ExportPath -NoTypeInformation