A couple builds ago, Microsoft added a cool new column to the ConfigMgr console. It is really cool but I wanted to more with it. I wanted pending reboot collections and reports. Now I want to share what I found and did. Continue reading “ConfigMgr Systems Pending Reboot Collections and Queries”
Tag: sccm
ConfigMgr Client Version Count SQL Query
UPDATED: Add 1810 on Feb 18, 2019
I updated my query fro client version counts. If you notice a version missing, please let me know.
SELECT "ConfigMgr Name"=
CASE sys.Client_Version0
WHEN '4.00.6487.2000' THEN 'CM07 SP2 (4.00.6487.2000)'
WHEN '4.00.6487.2157' THEN 'CM07 R3 (4.00.6487.2157)'
WHEN '4.00.6487.2177' THEN 'CM07 R3 KB2516517 (4.00.6487.2177)'
WHEN '4.00.6487.2187' THEN 'CM07 R3 (4.00.6487.2187)'
WHEN '4.00.6487.2188' THEN 'CM07 R3 (4.00.6487.2188)'
WHEN '5.00.0000.0000' THEN 'CM12 RTM (5.00.0000.0000)'
WHEN '5.00.7804.1000' THEN 'CM12 SP1 (5.00.7804.1000)'
WHEN '5.00.7804.1202' THEN 'CM12 SP1 CU1 (5.00.7804.1202)'
WHEN '5.00.7804.1300' THEN 'CM12 SP1 CU2 (5.00.7804.1300)'
WHEN '5.00.7804.1400' THEN 'CM12 SP1 CU3 (5.00.7804.1400)'
WHEN '5.00.7958.1000' THEN 'CM12 R2 (5.00.7958.1000)'
WHEN '5.00.7958.1101' THEN 'CM12 R2 KB 2905002 (5.00.7958.1101)'
WHEN '5.00.7958.1203' THEN 'CM12 R2 CU1 (5.00.7958.1203)'
WHEN '5.00.7958.1303' THEN 'CM12 R2 CU2 (5.00.7958.1303)'
WHEN '5.00.7958.1401' THEN 'CM12 R2 CU3 (5.00.7958.1401)'
WHEN '5.00.7958.1501' THEN 'CM12 R2 CU4 (5.00.7958.1501)'
WHEN '5.00.7958.1604' THEN 'CM12 R2 CU5 (5.00.7958.1604)'
WHEN '5.00.8239.1000' THEN 'CM12 R2 SP1 (5.00.8239.1000)'
WHEN '5.00.8239.1203' THEN 'CM12 R2 SP1 CU1 (5.00.8239.1203)'
WHEN '5.00.8239.1301' THEN 'CM12 R2 SP1 CU2 (5.00.8239.1301)'
WHEN '5.00.8325.1000' THEN 'ConfigMgr 1511 (5.00.8325.1000)'
WHEN '5.00.8325.1104' THEN 'ConfigMgr 1511 KB3118485 (5.00.8325.1104)'
WHEN '5.00.8325.1110' THEN 'ConfigMgr 1511 KB3122677 (5.00.8325.1110)'
WHEN '5.00.8355.1000' THEN 'ConfigMgr 1602 (5.00.8355.1000)'
WHEN '5.00.8412.1000' THEN 'ConfigMgr 1606 (5.00.8412.1000)'
WHEN '5.00.8412.1000' THEN 'ConfigMgr 1606 Fast Ring Update KB3180992 (5.00.8412.1204)'
WHEN '5.00.8458.1000' THEN 'ConfigMgr 1610 (5.00.8458.1000)'
WHEN '5.00.8458.1007' THEN 'ConfigMgr 1610 Update 1 Fast Ring KB3209501 (5.00.8458.1007)'
WHEN '5.00.8412.1000' THEN 'ConfigMgr 1702 (5.00.8498.1000)'
WHEN '5.00.8412.1000' THEN 'ConfigMgr 1706 (5.00.8540.1000)'
WHEN '5.00.8540.1005' THEN 'ConfigMgr 1706 Update 1 Fast Ring KB4039380 (5.00.8540.1005)'
WHEN '5.00.8540.1007' THEN 'ConfigMgr 1706 Update 2 Fast Ring KB4036267 (5.00.8540.1007)'
WHEN '5.00.8540.1611' THEN 'ConfigMgr 1706 Update Rollup 1 KB4042949 (5.00.8540.1611)'
WHEN '5.00.8577.1005' THEN 'ConfigMgr 1710 (5.00.8577.1005)'
WHEN '5.00.8577.1108' THEN 'ConfigMgr 1710 Update Rollup 1 KB4057517 (5.00.8577.1108)'
WHEN '5.00.8577.1115' THEN 'ConfigMgr 1710 Update Rollup 2 KB4086143 (5.00.8577.1115)'
WHEN '5.00.8634.1010' THEN 'ConfigMgr 1802 (5.00.8634.1010)'
WHEN '5.00.8692.1003' THEN 'ConfigMgr 1806 Fast Ring (5.00.8692.1003)'
WHEN '5.00.8692.1007' THEN 'ConfigMgr 1806 Slow Ring (5.00.8692.1007)'
WHEN '5.00.8692.1009' THEN 'ConfigMgr 1806 Fast Ring KB4346645 (5.00.8692.1009)'
WHEN '5.00.8692.1010' THEN 'ConfigMgr 1806 KB4459354 (5.00.8692.1010)'
<span STYLE="color: #0000ff; font-family: Consolas; font-size: small;">when</span> <span STYLE="color: #ff0000; font-family: Consolas; font-size: small;">'5.00.8740.1012'</span> <span STYLE="color: #0000ff; font-family: Consolas; font-size: small;">then</span> <span STYLE="color: #ff0000; font-family: Consolas; font-size: small;">'ConfigMgr 1810 (5.00.8740.1012)'</span>
<span STYLE="color: #0000ff; font-family: Consolas; font-size: small;">when</span> <span STYLE="color: #ff0000; font-family: Consolas; font-size: small;">'5.00.8740.1024'</span> <span STYLE="color: #0000ff; font-family: Consolas; font-size: small;">then</span> <span STYLE="color: #ff0000; font-family: Consolas; font-size: small;">'ConfigMgr 1810 KB8448657 (5.00.8740.1024)'</span>
ELSE 'Other'
END,
sys.Client_Version0 AS [Version],
COUNT(*) [Total]
FROM v_R_System sys
WHERE Client0 = '1'
GROUP BY sys.Client_Version0
ORDER BY 3 DESC
CASE sys.Client_Version0
WHEN '4.00.6487.2000' THEN 'CM07 SP2 (4.00.6487.2000)'
WHEN '4.00.6487.2157' THEN 'CM07 R3 (4.00.6487.2157)'
WHEN '4.00.6487.2177' THEN 'CM07 R3 KB2516517 (4.00.6487.2177)'
WHEN '4.00.6487.2187' THEN 'CM07 R3 (4.00.6487.2187)'
WHEN '4.00.6487.2188' THEN 'CM07 R3 (4.00.6487.2188)'
WHEN '5.00.0000.0000' THEN 'CM12 RTM (5.00.0000.0000)'
WHEN '5.00.7804.1000' THEN 'CM12 SP1 (5.00.7804.1000)'
WHEN '5.00.7804.1202' THEN 'CM12 SP1 CU1 (5.00.7804.1202)'
WHEN '5.00.7804.1300' THEN 'CM12 SP1 CU2 (5.00.7804.1300)'
WHEN '5.00.7804.1400' THEN 'CM12 SP1 CU3 (5.00.7804.1400)'
WHEN '5.00.7958.1000' THEN 'CM12 R2 (5.00.7958.1000)'
WHEN '5.00.7958.1101' THEN 'CM12 R2 KB 2905002 (5.00.7958.1101)'
WHEN '5.00.7958.1203' THEN 'CM12 R2 CU1 (5.00.7958.1203)'
WHEN '5.00.7958.1303' THEN 'CM12 R2 CU2 (5.00.7958.1303)'
WHEN '5.00.7958.1401' THEN 'CM12 R2 CU3 (5.00.7958.1401)'
WHEN '5.00.7958.1501' THEN 'CM12 R2 CU4 (5.00.7958.1501)'
WHEN '5.00.7958.1604' THEN 'CM12 R2 CU5 (5.00.7958.1604)'
WHEN '5.00.8239.1000' THEN 'CM12 R2 SP1 (5.00.8239.1000)'
WHEN '5.00.8239.1203' THEN 'CM12 R2 SP1 CU1 (5.00.8239.1203)'
WHEN '5.00.8239.1301' THEN 'CM12 R2 SP1 CU2 (5.00.8239.1301)'
WHEN '5.00.8325.1000' THEN 'ConfigMgr 1511 (5.00.8325.1000)'
WHEN '5.00.8325.1104' THEN 'ConfigMgr 1511 KB3118485 (5.00.8325.1104)'
WHEN '5.00.8325.1110' THEN 'ConfigMgr 1511 KB3122677 (5.00.8325.1110)'
WHEN '5.00.8355.1000' THEN 'ConfigMgr 1602 (5.00.8355.1000)'
WHEN '5.00.8412.1000' THEN 'ConfigMgr 1606 (5.00.8412.1000)'
WHEN '5.00.8412.1000' THEN 'ConfigMgr 1606 Fast Ring Update KB3180992 (5.00.8412.1204)'
WHEN '5.00.8458.1000' THEN 'ConfigMgr 1610 (5.00.8458.1000)'
WHEN '5.00.8458.1007' THEN 'ConfigMgr 1610 Update 1 Fast Ring KB3209501 (5.00.8458.1007)'
WHEN '5.00.8412.1000' THEN 'ConfigMgr 1702 (5.00.8498.1000)'
WHEN '5.00.8412.1000' THEN 'ConfigMgr 1706 (5.00.8540.1000)'
WHEN '5.00.8540.1005' THEN 'ConfigMgr 1706 Update 1 Fast Ring KB4039380 (5.00.8540.1005)'
WHEN '5.00.8540.1007' THEN 'ConfigMgr 1706 Update 2 Fast Ring KB4036267 (5.00.8540.1007)'
WHEN '5.00.8540.1611' THEN 'ConfigMgr 1706 Update Rollup 1 KB4042949 (5.00.8540.1611)'
WHEN '5.00.8577.1005' THEN 'ConfigMgr 1710 (5.00.8577.1005)'
WHEN '5.00.8577.1108' THEN 'ConfigMgr 1710 Update Rollup 1 KB4057517 (5.00.8577.1108)'
WHEN '5.00.8577.1115' THEN 'ConfigMgr 1710 Update Rollup 2 KB4086143 (5.00.8577.1115)'
WHEN '5.00.8634.1010' THEN 'ConfigMgr 1802 (5.00.8634.1010)'
WHEN '5.00.8692.1003' THEN 'ConfigMgr 1806 Fast Ring (5.00.8692.1003)'
WHEN '5.00.8692.1007' THEN 'ConfigMgr 1806 Slow Ring (5.00.8692.1007)'
WHEN '5.00.8692.1009' THEN 'ConfigMgr 1806 Fast Ring KB4346645 (5.00.8692.1009)'
WHEN '5.00.8692.1010' THEN 'ConfigMgr 1806 KB4459354 (5.00.8692.1010)'
<span STYLE="color: #0000ff; font-family: Consolas; font-size: small;">when</span> <span STYLE="color: #ff0000; font-family: Consolas; font-size: small;">'5.00.8740.1012'</span> <span STYLE="color: #0000ff; font-family: Consolas; font-size: small;">then</span> <span STYLE="color: #ff0000; font-family: Consolas; font-size: small;">'ConfigMgr 1810 (5.00.8740.1012)'</span>
<span STYLE="color: #0000ff; font-family: Consolas; font-size: small;">when</span> <span STYLE="color: #ff0000; font-family: Consolas; font-size: small;">'5.00.8740.1024'</span> <span STYLE="color: #0000ff; font-family: Consolas; font-size: small;">then</span> <span STYLE="color: #ff0000; font-family: Consolas; font-size: small;">'ConfigMgr 1810 KB8448657 (5.00.8740.1024)'</span>
ELSE 'Other'
END,
sys.Client_Version0 AS [Version],
COUNT(*) [Total]
FROM v_R_System sys
WHERE Client0 = '1'
GROUP BY sys.Client_Version0
ORDER BY 3 DESC