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
SQL Query works. Was able to get Boundary details. Thanks for the info.
Then it's quite likely SQL permissions. In PowerShell, try to run just line 1-0, and then run $Clients. It should show you a list.
I modified the script for these 2 – but I get ZERO result in csv file. Did install SQL Server module on the client machine. Do, I need to change anything else in the script?
$DataSource = "CB"
$Database = "CM_TOR"
CB – SCCM Server
CM_TOR – Database Name
The script is built for IP Address ranges only, but you can try running the SQL Scripts directly in SQL Server Management Studio to see what you get.
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 '%:%')
select Name, Value, NumericValueLow, NumericValueHigh from BoundaryEx where BoundaryType = '3'