I've been using and modifying/experimenting with Chris Nackers' SQL query for missing boundaries (http://myitforum.com/myitforumwp/2011/12/07/sql-query-to-identify-missing-smsconfigmgr-boundaries/) below (changed to add aliases)--but this seems to mainly be showing us non-clients, as several computers that were indeed missing boundaries (using SCCM 2007 SP2 R3, and all our boundaries are protected, most are IP Range, a few IP Subnet, none AD Site) are not being listed, and everything in the listing has NULL SYS.Client0.

Is there a better query to pinpoint this issue, or maybe using something (error code or log?) that would show computers that can't find a distribution point or some other evidence of not having a boundary?

Thanks!

SELECT DISTINCT SYS.Name0, SYS.Client0, IPA.IP_Addresses0, IPS.IP_Subnets0, SMSAS.SMS_Assigned_Sites0
 
FROM dbo.v_R_System SYS
LEFT OUTER JOIN dbo.v_RA_System_IPSubnets IPS ON SYS.ResourceID = IPS.ResourceID 
LEFT OUTER JOIN dbo.v_RA_System_IPAddresses IPA ON SYS.ResourceID = IPA.ResourceID 
LEFT OUTER JOIN dbo.v_RA_System_SMSAssignedSites SMSAS ON SYS.ResourceID = SMSAS.ResourceID 
LEFT OUTER JOIN dbo.v_RA_System_SystemOUName SOU ON SYS.ResourceID = SOU.ResourceID

WHERE (SMSAS.SMS_Assigned_Sites0 IS NULL) 
AND (NOT (IPA.IP_Addresses0 IS NULL)) 
AND (NOT (IPS.IP_Subnets0 IS NULL)) 
AND SYS.Operating_System_Name_and0 LIKE 'microsoft%server%'

ORDER BY IPS.IP_Subnets0, SYS.Name0

I'm not sure I understand your question... Most likely if you clients are not in a boundary they will show as non-clients. (client=NULL)

Welllll...we had a server that was not included in an IP Range boundary (IP is x.x.x.34, and there were two established boundaries of x.x.x.0-29 and x.x.x.129-187), and it was not able to download packages to cache, SYS.Client0 = 1, and extending the lower boundary from 29 to 34 fixed the issue.  It didn't show in the query.  Several other servers were similar...

I presume this may have meant that SMSAssignedSites may not have been NULL (since that's the first parameter in the WHERE part), but it still didn't help to identify the server didn't have a boundary.

Thanks.

John, something else about our environment--the client is installed via OSD on all new workstations, and servers (built manually or via scripts) have the client installed if required (we are a large medical entity, and many servers and some workstations cannot run SCCM and other clients due to FDA certification or other issues, or are behind restrictive VLAN ACL's to insulate them, so won't be able to communicate with SCCM and other things).  Automatic Client Installs (ACU) are setup on most, not all Workstation and laptop OU's, but, not on most Server OU's.

I think the query is basically showing conditions where ACU will not work, but, not actually reporting computers that can't receive packages as they are missing a boundary--and that is the information we are looking for.

Hope that clarifies a bit!

Thanks.

I gotcha now... I think most people, myself included, rely on finding clients that are not assigned to determine if a boundary is missing. If you expect clients to not be assigned that's not going to work for you.

WHERE (SMSAS.SMS_Assigned_Sites0 IS NULL) 

AND (NOT (IPA.IP_Addresses0 IS NULL))
AND (NOT (IPS.IP_Subnets0 IS NULL))

= This is saying show me all clients not assigned but they do have an IP address and they do have a subnet discovered.

In the case of CM12 it is actually possible for that not to work anyway because you can have separate boundaries for client assignment and content lookup.

I am not aware of any query that can compare the IP address, AD Site and IP subnet from each client to what's configured in boundaries and find machines that do not fall into any boundary.

Yes...I finally realized exactly what this report is REALLY showing just last week.

I know I and others have brought up being able to have some way to determine boundary issues, from the client-side, as being REALLY handy and needed, and have never found any documented way to do that.

Need looking for a resolution...<G>

Thanks.