Configuration Manager Versions Summary Report

I'm sure there are a dozen if not hundreds of blogs posts out there with this exact same information; just posting it mostly for myself.  If it helps someone else, great.  As of late July 2016, these are the versions (and their Marketing or public names) for the ConfigMgr Client.  It doesn't go back to SMS 1.0, or even cm07--so not as useful for "everything ever". 

But if you get "unknown" versions when you run it, you can fill in your own blanks.

SELECT COUNT(resourceID) [Count],
    Client_Version0 [Version]
, case
when client_version0 = '5.00.7711.0000' then 'ConfigMgr 2012 RTM'
when client_version0 = '5.00.7804.1000' then 'ConfigMgr 2012 SP1'
when client_version0 = '5.00.7804.1202' then 'ConfigMgr 2012 SP1 CU1'
when client_version0 = '5.00.7804.1300' then 'ConfigMgr 2012 SP1 CU2'
when client_version0 = '5.00.7804.1400' then 'ConfigMgr 2012 SP1 CU3'
when client_version0 = '5.00.7804.1500' then 'ConfigMgr 2012 SP1 CU4'
when client_version0 = '5.00.7804.1600' then 'ConfigMgr 2012 SP1 CU5'
when client_version0 = '5.00.7958.1000' then 'ConfigMgr 2012 R2'
when client_version0 = '5.00.7958.1060' then 'ConfigMgr 2012 R2 for Linux'
when client_version0 = '5.00.7958.1203' then 'ConfigMgr 2012 R2 CU1'
when client_version0 = '5.00.7958.1254' then 'ConfigMgr 2012 R2 CU1 for Linux'
when client_version0 = '5.00.7958.1303' then 'ConfigMgr 2012 R2 CU2'
when client_version0 = '5.00.7958.1401' then 'ConfigMgr 2012 R2 CU3'
when client_version0 = '5.00.7958.1501' then 'ConfigMgr 2012 R2 CU4'
when client_version0 = '5.00.7958.1604' then 'ConfigMgr 2012 R2 CU5'
when client_version0 = '5.00.8239.1000' then 'ConfigMgr 2012 R2 SP1'
when client_version0 = '5.00.8239.1203' then 'ConfigMgr 2012 R2 SP1 CU1'
when client_version0 = '5.00.8239.1301' then 'ConfigMgr 2012 R2 SP1 CU2'
when client_version0 = '5.00.8239.1403' then 'ConfigMgr 2012 R2 SP1 CU3'
when client_version0 = '5.00.8325.1000' then 'ConfigMgr 1511'
when client_version0 = '5.00.8355.1000' then 'ConfigMgr 1602'
when client_version0 = '5.00.8355.1001' then 'ConfigMgr 1602 with policyagentendpoint.dll update'
when client_version0 = '5.00.8355.1306' then 'ConfigMgr 1602 with KB3155482'
when client_version0 = '5.00.8355.1307' then 'ConfigMgr 1602 with KB3174008'
when client_version0 = '5.00.8412.1000' then 'ConfigMgr 1606 TAP'
when client_version0 = '5.00.8412.1006' then 'ConfigMgr 1606'
when client_version0 = '5.00.8412.1007' then 'ConfigMgr 1606 with KB3180992'
else 'unknown' end as [Marketing Version]
  FROM dbo.v_R_System_Valid
  GROUP BY
    Client_Version0
  ORDER BY [Version] desc

there's also this way...if you don't want to deal with all those pesky cumulative updates, or hotfixes

;with cte as (select resourceid, substring(client_version0,6,4) as [Ver] from v_r_system_valid)

select Ver,
Case
when Ver = '7711' then 'ConfigMgr 2012 RTM'
when Ver = '7958' then 'ConfigMgr 2012'
when Ver = '8239' then 'ConfigMgr 2012 R2'
when Ver = '8325' then 'ConfigMgr 1511'
when Ver = '8355' then 'ConfigMgr 1602'
when Ver = '8412' then 'ConfigMgr 1606'
else 'unknown'
 end as [Version]
,Count(resourceid) [Count]
from cte
group by ver
order by ver