ConfigMgr SQL Query – ConfigMgr clients installed per AD Site

SQL Query: Configuration Manager Clients Installed per AD Site

If you want a breakdown of how many active Configuration Manager clients you have per Active Directory site — useful for checking coverage across different offices or locations — this SQL query against the Configuration Manager database groups your client count by AD site name:

SELECT AD_Site_Name0 AS 'AD Site Name', COUNT(DISTINCT Name0) AS 'Clients Installed'
FROM v_R_System
WHERE Active0 = 1 AND Client0 = 1 AND Obsolete0 != 1 AND AD_Site_Name0 != 'null'
GROUP BY AD_Site_Name0

The filters here matter: Active0 = 1 excludes clients that haven’t checked in recently, Client0 = 1 ensures you’re only counting machines with the Configuration Manager client actually installed, and Obsolete0 != 1 excludes records Configuration Manager has flagged as obsolete/stale. Excluding AD_Site_Name0 != 'null' filters out records where the AD site couldn’t be resolved, which usually means the device isn’t in a location covered by your AD Sites and Services configuration. Build this as an SSRS report if you want an easy, refreshable view of client coverage by site without going back into SQL each time.

Finding Sites With Suspiciously Low Coverage

A raw count by AD site is useful on its own, but it doesn’t tell you whether a site’s coverage is actually healthy relative to how many devices it should have. Two practical follow-ups worth adding: cross-referencing this output against a count of all discovered resources per AD site (drop the Client0 = 1 filter to get total discovered devices, then compare the two numbers to spot sites where the client just hasn’t been pushed out yet), and adding a client-version column via COALESCE against v_ClientVersions if you want to catch sites that are technically covered but running a stale client build. The underlying v_R_System view and the four filter columns used here remain unchanged and stable across current Configuration Manager branches, so this query needs no modification to keep working.

Resources

• query
• WQL
• our SQL & WQL query library
• Browse our Windows Admin Toolkit picks on Amazon