{"id":374,"date":"2020-04-23T05:59:08","date_gmt":"2020-04-23T05:59:08","guid":{"rendered":"http:\/\/michaelschultz.net\/tech\/?p=374"},"modified":"2020-04-23T05:59:08","modified_gmt":"2020-04-23T05:59:08","slug":"configmgr-client-content-breakdown-sql-query","status":"publish","type":"post","link":"https:\/\/michaelschultz.net\/tech\/configmgr-client-content-breakdown-sql-query\/","title":{"rendered":"ConfigMgr Client Content Breakdown SQL Query"},"content":{"rendered":"\n

This SQL query for ConfigMgr current branch gives a breakdown of client content.\u00a0 It is also on my GitHub at https:\/\/github.com\/NecroMonkey\/vault\/blob\/master\/SQL\/ConfigMgr_content_source_breakdown.sql<\/a><\/p>\n

\u00a0<\/p>\n\n\n\n

\n\n
drop table if exists ##temp1
\ndrop table if exists ##temp2
\n
\nSELECT distinct sysr.Name0,
\nsysr.ResourceID, ipr.Name as &#91;Boundary],
\nipr.Value
\ninto ##temp1
\nFROM v_R_System sysr
\nJOIN System_IP_Address_ARR ip ON ip.ItemKey = sysr.ResourceID AND ip.NumericIPAddressValue &lt;> 0     
\nJOIN v_RA_System_IPSubnets sub ON sub.ResourceID = sysr.ResourceID      
\nLEFT JOIN v_RA_System_IPv6Prefixes v6 ON v6.ResourceID = sysr.ResourceID
\nJOIN BoundaryEx AS ipr ON 
\n-- Check BoundaryType 3 (IPRANGE)
\n(ipr.BoundaryType = 3 AND ip.NumericIPAddressValue BETWEEN ipr.NumericValueLow AND ipr.NumericValueHigh)
\n-- Check BoundaryType 1 (ADSITE)
\nOR (ipr.BoundaryType = 1 AND ipr.Value = sysr.AD_Site_Name0) 
\n-- Check BoundaryType 0 (IPSUBNET)
\nOR (ipr.BoundaryType = 0 AND ipr.Value = sub.IP_Subnets0)
\n-- Check BoundaryType 2 (IPv6)
\nOR (ipr.BoundaryType = 2 AND ipr.Value = v6.IPv6_Prefixes0)
\norder by sysr.ResourceID
\n
\nselect rsys.Name0, rsys.ResourceID,
\n"Content Source Type" = CASE cdhs.DistributionPointType
\nWHEN 1 THEN 'Cloud DP (Distribution Point)'
\nWHEN 2 THEN 'Management Point'
\nWHEN 3 THEN 'Peer Cache'
\nWHEN 4 THEN 'DP (Distribution Point)'
\nWHEN 5 THEN 'BranchCache'
\nWHEN 6 THEN 'Delivery Optimization Peer'
\nWHEN 7 THEN 'Delivery Optimization Cache Server'
\nWHEN 8 THEN 'Microsoft Update'
\nEND,
\npackages.Name as &#91;Content],
\ncdhs.ContentID,
\nCAST(SUM(cast(cdhs.BytesDownloaded as float)) \/ 1073741824 AS DECIMAL(10,2)) as &#91;GB]
\ninto ##temp2
\nfrom
\nv_R_System as rsys
\njoin ClientDownloadHistory as cdh on rsys.ResourceID = cdh.ClientId
\njoin ClientDownloadHistorySources as cdhs on cdh.id = cdhs.DownloadHistoryID
\njoin ClientDownloadHistoryBoundaryGroups as cdhbg on cdh.id = cdhbg.DownloadHistoryID JOIN
\n                         v_FullCollectionMembership AS a ON rsys.ResourceID = a.ResourceID JOIN
\n                         v_Collection AS b ON b.CollectionID = a.CollectionID LEFT JOIN CI_Contentpackages CI on CI.Content_UniqueID = cdhs.ContentID 
\nJOIN smspackages packages on packages.PkgID = ISNULL(CI.PkgID,cdhs.ContentId)
\nwhere (b.CollectionID = 'SMSDM003')
\ngroup by rsys.Name0, rsys.ResourceID, cdhs.DistributionPointType, packages.Name, cdhs.ContentID
\norder by rsys.Name0, rsys.ResourceID, cdhs.DistributionPointType, packages.Name, cdhs.ContentID
\n
\nselect
\nt1.Name0, t1.Boundary, t1.Value, t2.&#91;Content Source Type], t2.Content, t2.GB
\nfrom ##temp1 as t1 join
\n##temp2 as t2 on t1.ResourceID = t2.ResourceID
\n
\ndrop table if exists ##temp1
\ndrop table if exists ##temp2<\/div><\/div>\n\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
This SQL query for ConfigMgr current branch gives a breakdown of client content.\u00a0 It is also on my GitHub at https:\/\/github.com\/NecroMonkey\/vault\/blob\/master\/SQL\/ConfigMgr_content_source_breakdown.sql \u00a0<\/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,17],"tags":[],"class_list":["post-374","post","type-post","status-publish","format-standard","hentry","category-configmgr","category-queries","category-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p98a2r-62","post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/posts\/374","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=374"}],"version-history":[{"count":2,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/posts\/374\/revisions"}],"predecessor-version":[{"id":419,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/posts\/374\/revisions\/419"}],"wp:attachment":[{"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/media?parent=374"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/categories?post=374"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/michaelschultz.net\/tech\/wp-json\/wp\/v2\/tags?post=374"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}