Just thought I would share a SQL query I made. I was looking for missing
boundaries in SCCM. Like most places we use private IP's.
If there is a value under 'Subnets Detected' but a NULL under
'Sorted:Boundary Values' you MIGHT have a missing boundary. You will have
to determine for yourself as it could just be someone VPN'ing in and it's
their local subnet.
If there is a NULL under 'Subnets Detected' but a value under
'Sorted:Boundary Values' you MIGHT have a boundary no longer used. Or it
could be nothing is on that subnet right now with an SCCM client.
The powershell command to add a boundary is:
New-CMBoundary -Name "<boundary_name>" -Type IPSubnet -Value "<subnet_ip>"
example: New-CMBoundary -Name "Boundary 10.0.0.0" -Type IPSubnet -Value
"10.0.0.0"
The powershell command to remove a boundary is:
Remove-CMBoundary -name "<subnet_displayname>" -force
example: Remove-CMBoundary -name "Boundary 10.0.0.0" -force
Code:
SELECT
IP_Subnets0 AS 'Subnets Detected',
vSMS_Boundary.Value AS 'Sorted:Boundary Values',
COUNT(v_RA_System_IPSubnets.ResourceID)AS 'Count of Devices',
vSMS_Boundary.DisplayName,
vSMS_Boundary.CreatedOn,
vSMS_Boundary.ModifiedOn
FROM v_RA_System_IPSubnets
Full Join vSMS_Boundary on vSMS_Boundary.Value =
v_RA_System_IPSubnets.IP_Subnets0
Where (v_RA_System_IPSubnets.IP_Subnets0 like '172.1[6-9].%' or
v_RA_System_IPSubnets.IP_Subnets0 like '172.2[0-9].%' or
v_RA_System_IPSubnets.IP_Subnets0 like '172.3[0-1].%') or
(vSMS_Boundary.Value like '172.1[6-9].%' or
vSMS_Boundary.Value like '172.2[0-9].%' or
vSMS_Boundary.Value like '172.3[0-1].%')or
v_RA_System_IPSubnets.IP_Subnets0 like '10.%' or
v_RA_System_IPSubnets.IP_Subnets0 like '192.168.%' or
vSMS_Boundary.Value like '10.%' or
vSMS_Boundary.Value like '192.168.%'
Group By vSMS_Boundary.Value,
v_RA_System_IPSubnets.IP_Subnets0,vSMS_Boundary.DisplayName,vSMS_Boundary.CreatedOn,vSMS_Boundary.ModifiedOn
Order By [Sorted:Boundary Values]
Dave
