SQL query to identify missing SMS/ConfigMgr boundaries

SQL query to identify missing SMS/ConfigMgr boundaries

This came up on the MSSMS list from myitforum. Here is the final result of the collaboration. Here is a SQL query you can use to help identify missing boundaries for clients that are discovered.



SELECT DISTINCT

-- v_R_System.Name0

--, v_R_System.Client0

--, v_RA_System_IPAddresses.IP_Addresses0

v_RA_System_IPSubnets.IP_Subnets0

--, v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0

FROM v_R_System LEFT OUTER JOIN

v_RA_System_IPSubnets ON v_R_System.ResourceID = v_RA_System_IPSubnets.ResourceID LEFT OUTER JOIN

v_RA_System_IPAddresses ON v_R_System.ResourceID = v_RA_System_IPAddresses.ResourceID LEFT OUTER JOIN

v_RA_System_SMSAssignedSites ON v_R_System.ResourceID = v_RA_System_SMSAssignedSites.ResourceID

WHERE (v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 IS NULL)

AND (NOT (v_RA_System_IPAddresses.IP_Addresses0 IS NULL))

AND (v_R_System.Client0 IS NULL)

AND (NOT (v_RA_System_IPSubnets.IP_Subnets0 IS NULL))

order by v_RA_System_IPSubnets.IP_Subnets0