SCCM – SQL Query for showing number of different Operating Systems

Last updated on April 16th, 2023 at 11:26 pm

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.

About Post Author

Andrew Armstrong

Founder of TechyGeeksHome and Head Editor for over 15 years! IT expert in multiple areas for over 23 years. Sharing experience and knowledge whenever possible! Making IT Happen.

te[email protected]

https://blog.techygeekshome.info

administrator

Free Subscription

If you want to be notified when we post more quality guides like this one, sign up to our free subscription service and you will receive an email when a new post is live.

No need to worry, we will not be filling your inbox with spam and you can unsubscribe anytime you like.