{"id":165,"date":"2018-06-07T20:45:24","date_gmt":"2018-06-07T20:45:24","guid":{"rendered":"http:\/\/michaelschultz.net\/tech\/?p=165"},"modified":"2018-06-28T22:06:54","modified_gmt":"2018-06-28T22:06:54","slug":"configman-client-version-count-query","status":"publish","type":"post","link":"https:\/\/michaelschultz.net\/tech\/configman-client-version-count-query\/","title":{"rendered":"ConfigMgr Client Version Count Query"},"content":{"rendered":"

I just wanted to share the base query I am using to see the client versions i have in my environment and how many of each client.\u00a0 \u00a0It has three columns: ConfigMgr versions, client version, and count.<\/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> 'SCCM 1511 (5.00.8325.1000)'<\/span>
\nWHEN<\/span> '5.00.8325.1104'<\/span> THEN<\/span> 'SCCM 1511 KB3118485 (5.00.8325.1104)'<\/span>
\nWHEN<\/span> '5.00.8325.1110'<\/span> THEN<\/span> 'SCCM 1511 KB3122677 (5.00.8325.1110)'<\/span>
\nWHEN<\/span> '5.00.8355.1000'<\/span> THEN<\/span> 'SCCM 1602 (5.00.8355.1000)'<\/span>
\nWHEN<\/span> '5.00.8412.1000'<\/span> THEN<\/span> 'SCCM 1606 (5.00.8412.1000)'<\/span>
\nWHEN<\/span> '5.00.8412.1000'<\/span> THEN<\/span> 'SCCM 1606 Early Wave Update KB3180992 (5.00.8412.1204)'<\/span>
\nWHEN<\/span> '5.00.8412.1000'<\/span> THEN<\/span> 'SCCM 1702 (5.00.8498.1000)'<\/span>
\nWHEN<\/span> '5.00.8412.1000'<\/span> THEN<\/span> 'SCCM 1706 (5.00.8540.1000)'<\/span>
\nWHEN<\/span> '5.00.8458.1000'<\/span> THEN<\/span> 'SCCM 1610 (5.00.8458.1000)'<\/span>
\nWHEN<\/span> '5.00.8458.1007'<\/span> THEN<\/span> 'SCCM 1610 Update 1 early wave KB3209501 (5.00.8458.1007)'<\/span>
\nWHEN<\/span> '5.00.8540.1005'<\/span> THEN<\/span> 'SCCM 1706 Update 1 early wave KB4039380 (5.00.8540.1005)'<\/span>
\nWHEN<\/span> '5.00.8540.1007'<\/span> THEN<\/span> 'SCCM 1706 Update 2 early wave KB4036267 (5.00.8540.1007)'<\/span>
\nWHEN<\/span> '5.00.8540.1611'<\/span> THEN<\/span> 'SCCM 1706 Update Rollup 1 KB4042949 (5.00.8540.1611)'<\/span>
\nWHEN<\/span> '5.00.8577.1005'<\/span> THEN<\/span> 'SCCM 1710 (5.00.8577.1005)'<\/span>
\nWHEN<\/span> '5.00.8577.1108'<\/span> THEN<\/span> 'SCCM 1710 Update Rollup 1 KB4057517 (5.00.8577.1108)'<\/span>
\nWHEN<\/span> '5.00.8577.1115'<\/span> THEN<\/span> 'SCCM 1710 Update Rollup 2 KB4086143 (5.00.8577.1115)'<\/span>
\nWHEN<\/span> '5.00.8634.1010'<\/span> THEN<\/span> 'SCCM 1802 (5.00.8634.1010)'<\/span>
\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<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"

I just wanted to share the base query I am using to see the client versions i have in my environment and how many of each client.\u00a0 \u00a0It has three columns: ConfigMgr versions, client version, and count.<\/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,26],"tags":[],"class_list":["post-165","post","type-post","status-publish","format-standard","hentry","category-configmgr","category-queries"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p98a2r-2F","post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/posts\/165","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=165"}],"version-history":[{"count":4,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/posts\/165\/revisions"}],"predecessor-version":[{"id":188,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/posts\/165\/revisions\/188"}],"wp:attachment":[{"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/media?parent=165"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/categories?post=165"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/tags?post=165"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}