Count OS Versions by Servers and Workstations

Version: SCCM 2012 SP1 CU2

If you need to count the versions of operating systems without getting edition specific, use this SQL query. This will provide counts grouping the operating system by workstations and servers and then versions.

This particular SQL query looks for versions ranging from XP/WS2003 Server to W8.1/WS2012R2.

select TYPE, OS, Sum(Count) as 'Count' from (
select Distinct 'Workstations' as 'Type', (
 CASE
 WHEN OPSYS.Version0 like '5.%' THEN 'Windows XP'
 WHEN OPSYS.Version0 like '6.0.%' THEN 'Windows Vista'
 WHEN OPSYS.Version0 like '6.1.%' THEN 'Windows 7'
 WHEN OPSYS.Version0 like '6.2.%' THEN 'Windows 8'
 WHEN OPSYS.Version0 like '6.3.%' THEN 'Windows 8.1'
 Else OPSYS.Version0
 END 
 ) AS 'OS',
Count(OPsys.ResourceID)as count from v_GS_OPERATING_SYSTEM OPSYS
join v_R_System S on S.ResourceID=OPSYS.ResourceID
where S.Operating_System_Name_and0 like '%workstation%'
Group by OPSYS.Version0) WSQuery
Group by Type, OS
UNION
select TYPE, OS, Sum(Count) from (
select Distinct 'Servers' as 'Type', (
 CASE
 WHEN OPSYS.Version0 like '5.2%' THEN 'Windows Server 2003'
 WHEN OPSYS.Version0 like '6.0.%' THEN 'Windows Server 2008'
 WHEN OPSYS.Version0 like '6.1.%' THEN 'Windows Server 2008 R2'
 WHEN OPSYS.Version0 like '6.2.%' THEN 'Windows Server 2012'
 WHEN OPSYS.Version0 like '6.3.%' THEN 'Windows Server 2012 R2'
 Else OPSYS.Version0
 END 
 ) AS 'OS',
Count(OPsys.ResourceID)as count from v_GS_OPERATING_SYSTEM OPSYS
join v_R_System S on S.ResourceID=OPSYS.ResourceID
where S.Operating_System_Name_and0 like '%server%'
Group by OPSYS.Version0) SVRQuery
Group by Type, OS

Sample Output:

Count OS Versions by Servers and Workstations

Count OS Versions by Servers and Workstations