{"id":229,"date":"2018-09-13T20:36:55","date_gmt":"2018-09-13T20:36:55","guid":{"rendered":"http:\/\/michaelschultz.net\/tech\/?p=229"},"modified":"2019-02-18T21:21:26","modified_gmt":"2019-02-18T21:21:26","slug":"configmgr-client-version-count-sql-query","status":"publish","type":"post","link":"https:\/\/michaelschultz.net\/tech\/configmgr-client-version-count-sql-query\/","title":{"rendered":"ConfigMgr Client Version Count SQL Query"},"content":{"rendered":"

UPDATED<\/strong>: Add 1810 on Feb 18, 2019<\/p>\n

I updated my query fro client version counts.\u00a0 If you notice a version missing, please let me know.<\/p>\n

SELECT<\/span> "ConfigMgr Name"<\/span>=<\/span>
\nCASE<\/span> sys.<\/span>Client_Version0
\nWHEN<\/span> '4.00.6487.2000'<\/span> THEN<\/span> 'CM07 SP2 (4.00.6487.2000)'<\/span>
\nWHEN<\/span> '4.00.6487.2157'<\/span> THEN<\/span> 'CM07 R3 (4.00.6487.2157)'<\/span>
\nWHEN<\/span> '4.00.6487.2177'<\/span> THEN<\/span> 'CM07 R3 KB2516517 (4.00.6487.2177)'<\/span>
\nWHEN<\/span> '4.00.6487.2187'<\/span> THEN<\/span> 'CM07 R3 (4.00.6487.2187)'<\/span>
\nWHEN<\/span> '4.00.6487.2188'<\/span> THEN<\/span> 'CM07 R3 (4.00.6487.2188)'<\/span>
\nWHEN<\/span> '5.00.0000.0000'<\/span> THEN<\/span> 'CM12 RTM (5.00.0000.0000)'<\/span>
\nWHEN<\/span> '5.00.7804.1000'<\/span> THEN<\/span> 'CM12 SP1 (5.00.7804.1000)'<\/span>
\nWHEN<\/span> '5.00.7804.1202'<\/span> THEN<\/span> 'CM12 SP1 CU1 (5.00.7804.1202)'<\/span>
\nWHEN<\/span> '5.00.7804.1300'<\/span> THEN<\/span> 'CM12 SP1 CU2 (5.00.7804.1300)'<\/span>
\nWHEN<\/span> '5.00.7804.1400'<\/span> THEN<\/span> 'CM12 SP1 CU3 (5.00.7804.1400)'<\/span>
\nWHEN<\/span> '5.00.7958.1000'<\/span> THEN<\/span> 'CM12 R2 (5.00.7958.1000)'<\/span>
\nWHEN<\/span> '5.00.7958.1101'<\/span> THEN<\/span> 'CM12 R2 KB 2905002 (5.00.7958.1101)'<\/span>
\nWHEN<\/span> '5.00.7958.1203'<\/span> THEN<\/span> 'CM12 R2 CU1 (5.00.7958.1203)'<\/span>
\nWHEN<\/span> '5.00.7958.1303'<\/span> THEN<\/span> 'CM12 R2 CU2 (5.00.7958.1303)'<\/span>
\nWHEN<\/span> '5.00.7958.1401'<\/span> THEN<\/span> 'CM12 R2 CU3 (5.00.7958.1401)'<\/span>
\nWHEN<\/span> '5.00.7958.1501'<\/span> THEN<\/span> 'CM12 R2 CU4 (5.00.7958.1501)'<\/span>
\nWHEN<\/span> '5.00.7958.1604'<\/span> THEN<\/span> 'CM12 R2 CU5 (5.00.7958.1604)'<\/span>
\nWHEN<\/span> '5.00.8239.1000'<\/span> THEN<\/span> 'CM12 R2 SP1 (5.00.8239.1000)'<\/span>
\nWHEN<\/span> '5.00.8239.1203'<\/span> THEN<\/span> 'CM12 R2 SP1 CU1 (5.00.8239.1203)'<\/span>
\nWHEN<\/span> '5.00.8239.1301'<\/span> THEN<\/span> 'CM12 R2 SP1 CU2 (5.00.8239.1301)'<\/span>
\nWHEN<\/span> '5.00.8325.1000'<\/span> THEN<\/span> 'ConfigMgr 1511 (5.00.8325.1000)'<\/span>
\nWHEN<\/span> '5.00.8325.1104'<\/span> THEN<\/span> 'ConfigMgr 1511 KB3118485 (5.00.8325.1104)'<\/span>
\nWHEN<\/span> '5.00.8325.1110'<\/span> THEN<\/span> 'ConfigMgr 1511 KB3122677 (5.00.8325.1110)'<\/span>
\nWHEN<\/span> '5.00.8355.1000'<\/span> THEN<\/span> 'ConfigMgr 1602 (5.00.8355.1000)'<\/span>
\nWHEN<\/span> '5.00.8412.1000'<\/span> THEN<\/span> 'ConfigMgr 1606 (5.00.8412.1000)'<\/span>
\nWHEN<\/span> '5.00.8412.1000'<\/span> THEN<\/span> 'ConfigMgr 1606 Fast Ring Update KB3180992 (5.00.8412.1204)'<\/span>
\nWHEN<\/span> '5.00.8458.1000'<\/span> THEN<\/span> 'ConfigMgr 1610 (5.00.8458.1000)'<\/span>
\nWHEN<\/span> '5.00.8458.1007'<\/span> THEN<\/span> 'ConfigMgr 1610 Update 1 Fast Ring KB3209501 (5.00.8458.1007)'<\/span>
\nWHEN<\/span> '5.00.8412.1000'<\/span> THEN<\/span> 'ConfigMgr 1702 (5.00.8498.1000)'<\/span>
\nWHEN<\/span> '5.00.8412.1000'<\/span> THEN<\/span> 'ConfigMgr 1706 (5.00.8540.1000)'<\/span>
\nWHEN<\/span> '5.00.8540.1005'<\/span> THEN<\/span> 'ConfigMgr 1706 Update 1 Fast Ring KB4039380 (5.00.8540.1005)'<\/span>
\nWHEN<\/span> '5.00.8540.1007'<\/span> THEN<\/span> 'ConfigMgr 1706 Update 2 Fast Ring KB4036267 (5.00.8540.1007)'<\/span>
\nWHEN<\/span> '5.00.8540.1611'<\/span> THEN<\/span> 'ConfigMgr 1706 Update Rollup 1 KB4042949 (5.00.8540.1611)'<\/span>
\nWHEN<\/span> '5.00.8577.1005'<\/span> THEN<\/span> 'ConfigMgr 1710 (5.00.8577.1005)'<\/span>
\nWHEN<\/span> '5.00.8577.1108'<\/span> THEN<\/span> 'ConfigMgr 1710 Update Rollup 1 KB4057517 (5.00.8577.1108)'<\/span>
\nWHEN<\/span> '5.00.8577.1115'<\/span> THEN<\/span> 'ConfigMgr 1710 Update Rollup 2 KB4086143 (5.00.8577.1115)'<\/span>
\nWHEN<\/span> '5.00.8634.1010'<\/span> THEN<\/span> 'ConfigMgr 1802 (5.00.8634.1010)'<\/span>
\nWHEN<\/span> '5.00.8692.1003'<\/span> THEN<\/span> 'ConfigMgr 1806 Fast Ring (5.00.8692.1003)'<\/span>
\nWHEN<\/span> '5.00.8692.1007'<\/span> THEN<\/span> 'ConfigMgr 1806 Slow Ring (5.00.8692.1007)'<\/span>
\nWHEN<\/span> '5.00.8692.1009'<\/span> THEN<\/span> 'ConfigMgr 1806 Fast Ring KB4346645 (5.00.8692.1009)'<\/span>
\nWHEN<\/span> '5.00.8692.1010'<\/span> THEN<\/span> 'ConfigMgr 1806 KB4459354 (5.00.8692.1010)'<\/span>
\n
\n<<\/span>span STYLE<\/span>=<\/span>"color: #0000ff; font-family: Consolas; font-size: small;"<\/span>><\/span>when<\/<\/span>span><\/span> <<\/span>span STYLE<\/span>=<\/span>"color: #ff0000; font-family: Consolas; font-size: small;"<\/span>><\/span>'5.00.8740.1012'<\/span><\/<\/span>span><\/span> <<\/span>span STYLE<\/span>=<\/span>"color: #0000ff; font-family: Consolas; font-size: small;"<\/span>><\/span>then<\/<\/span>span><\/span> <<\/span>span STYLE<\/span>=<\/span>"color: #ff0000; font-family: Consolas; font-size: small;"<\/span>><\/span>'ConfigMgr 1810 (5.00.8740.1012)'<\/span><\/<\/span>span><\/span>
\n
\n<<\/span>span STYLE<\/span>=<\/span>"color: #0000ff; font-family: Consolas; font-size: small;"<\/span>><\/span>when<\/<\/span>span><\/span> <<\/span>span STYLE<\/span>=<\/span>"color: #ff0000; font-family: Consolas; font-size: small;"<\/span>><\/span>'5.00.8740.1024'<\/span><\/<\/span>span><\/span> <<\/span>span STYLE<\/span>=<\/span>"color: #0000ff; font-family: Consolas; font-size: small;"<\/span>><\/span>then<\/<\/span>span><\/span> <<\/span>span STYLE<\/span>=<\/span>"color: #ff0000; font-family: Consolas; font-size: small;"<\/span>><\/span>'ConfigMgr 1810 KB8448657 (5.00.8740.1024)'<\/span><\/<\/span>span><\/span>
\n
\nELSE<\/span> 'Other'<\/span>
\nEND<\/span>,<\/span>
\nsys.<\/span>Client_Version0 AS<\/span> [<\/span>Version]<\/span>,<\/span>
\nCOUNT<\/span>(<\/span>*<\/span>)<\/span> [<\/span>Total]<\/span>
\nFROM<\/span> v_R_System sys
\nWHERE<\/span> Client0 =<\/span> '1'<\/span>
\nGROUP<\/span> BY<\/span> sys.<\/span>Client_Version0
\nORDER<\/span> BY<\/span> 3<\/span> DESC<\/span><\/div><\/div>\n","protected":false},"excerpt":{"rendered":"

UPDATED: Add 1810 on Feb 18, 2019 I updated my query fro client version counts.\u00a0 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)’ … Continue reading “ConfigMgr Client Version Count SQL Query”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"nf_dc_page":"","jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[4,6,17],"tags":[29,7,30,28,18],"class_list":["post-229","post","type-post","status-publish","format-standard","hentry","category-configmgr","category-reports","category-sql","tag-client-version","tag-configmgr","tag-query","tag-sccm","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p98a2r-3H","post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/posts\/229","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/comments?post=229"}],"version-history":[{"count":3,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/posts\/229\/revisions"}],"predecessor-version":[{"id":279,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/posts\/229\/revisions\/279"}],"wp:attachment":[{"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/media?parent=229"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/categories?post=229"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/tags?post=229"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}