SCCM SQL Query: Count Different Operating Systems in Your Environment

If you want a quick report showing how many devices are running each Operating System version, this SQL query against the ConfigMgr database will build that breakdown for you, covering both server and client OS captions.

Query

SELECT CASE
WHEN Caption0 LIKE '%XP%' THEN 'XP'
WHEN Caption0 LIKE '%Windows 7%' THEN 'Windows 7'
WHEN Caption0 LIKE '%Server 2008 R2%' THEN 'Server 2008 R2'
WHEN Caption0 LIKE '%Server 2008%' THEN 'Server 2008'
WHEN Caption0 LIKE '%Server 2003%' THEN 'Server 2003'
WHEN Caption0 LIKE '%Vista%' THEN 'Vista'
WHEN Caption0 LIKE '%Windows 8%' THEN 'Windows 8'
ELSE 'Other' END 'OS Name',
COUNT(sys.ResourceID) AS Total
FROM v_GS_OPERATING_SYSTEM as os INNER JOIN v_GS_SYSTEM as sys
ON os.ResourceID = sys.ResourceID
GROUP BY CASE
WHEN Caption0 LIKE '%XP%' THEN 'XP'
WHEN Caption0 LIKE '%Windows 7%' THEN 'Windows 7'
WHEN Caption0 LIKE '%Server 2008 R2%' THEN 'Server 2008 R2'
WHEN Caption0 LIKE '%Server 2008%' THEN 'Server 2008'
WHEN Caption0 LIKE '%Server 2003%' THEN 'Server 2003'
WHEN Caption0 LIKE '%Vista%' THEN 'Vista'
WHEN Caption0 LIKE '%Windows 8%' THEN 'Windows 8'
ELSE 'Other'
END

Extending it for newer OS versions

The original query’s CASE list reflects the OS versions common when it was written. If your estate has moved on, add extra WHEN Caption0 LIKE '%...%' THEN '...' lines (in both the SELECT and the matching GROUP BY) for whichever captions you expect to see, for example '%Windows 10%', '%Windows 11%', '%Server 2019%', '%Server 2022%', or '%Server 2025%'. Anything that doesn’t match a WHEN clause simply falls into “Other”, so it’s safe to run as-is even without updating it — you’ll just see a larger “Other” bucket.

How do I create the SSRS Report?

We have a separate guide that walks through turning query results like this into an SSRS report based on Configuration Manager database data — you can find it linked below.

Comments

If you have any problems with any of the above, then please leave your comments and questions below using our comments section and we will try to get back to you as soon as we can. We also like to hear back any success stories too, so if you have used our guide and found it helpful, we would love to hear from you.

Resources

• guide
• Configuration Manager
• Create a SSRS Report using Configuration Manager Database Data