SCCM – SQL Query for showing number of different Operating Systems

Estimated reading time: 2 minutes

If you would like to create a report to show you the number of different Operating System’s you have, you can use this script below which should provide you with the information for Servers and the various Windows OS’s.

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

How do I create the SSRS Report?

We have now created a guide that will help you to create your SSRS reports based on the data that this guide will pull into the Configuration Manager database.

You can access this guide by clicking the link below:

Create a SSRS Report using Configuration Manager Database Data

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.