Category: SQL

ConfigMgr Client Boundary and Boundary Group memberships SQL Query

ConfigMgr current branch SQL query to return the boundaries and boundary groups systems are part of.

https://github.com/NecroMonkey/vault/blob/master/SQL/client_boundary_membership_update.sql



SELECT DISTINCT 

sysr.Netbios_Name0,

ipr.Name as [Boundary],

ipr.BoundaryType as [Type],

bg.Name AS [Boundary Group]

FROM     v_R_System AS sysr INNER JOIN

                  System_IP_Address_ARR AS ip ON ip.ItemKey = sysr.ResourceID AND ip.NumericIPAddressValue <> 0 INNER JOIN

                  v_RA_System_IPSubnets AS sub ON sub.ResourceID = sysr.ResourceID LEFT OUTER JOIN

                  v_RA_System_IPv6Prefixes AS v6 ON v6.ResourceID = sysr.ResourceID INNER JOIN

                  BoundaryEx AS ipr ON ipr.BoundaryType = 3 AND ip.NumericIPAddressValue BETWEEN ipr.NumericValueLow AND ipr.NumericValueHigh OR

                  ipr.BoundaryType = 1 AND ipr.Value = sysr.AD_Site_Name0 OR

                  ipr.BoundaryType = 0 AND ipr.Value = sub.IP_Subnets0 OR

                  ipr.BoundaryType = 2 AND ipr.Value = v6.IPv6_Prefixes0 INNER JOIN

                  vSMS_BoundaryGroupMembers as bgm ON ipr.BoundaryID = bgm.BoundaryID INNER JOIN

                  vSMS_BoundaryGroup as bg ON bgm.GroupID = bg.GroupID

GROUP BY sysr.Netbios_Name0, ipr.Name, ipr.BoundaryType, bg.Name

ORDER BY sysr.Netbios_Name0, ipr.Name, bg.Name

ConfigMgr Client Content Breakdown SQL Query

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

 



drop table if exists ##temp1

drop table if exists ##temp2



SELECT distinct sysr.Name0,

sysr.ResourceID, ipr.Name as [Boundary],

ipr.Value

into ##temp1

FROM v_R_System sysr

JOIN System_IP_Address_ARR ip ON ip.ItemKey = sysr.ResourceID AND ip.NumericIPAddressValue <> 0     

JOIN v_RA_System_IPSubnets sub ON sub.ResourceID = sysr.ResourceID      

LEFT JOIN v_RA_System_IPv6Prefixes v6 ON v6.ResourceID = sysr.ResourceID

JOIN BoundaryEx AS ipr ON 

-- Check BoundaryType 3 (IPRANGE)

(ipr.BoundaryType = 3 AND ip.NumericIPAddressValue BETWEEN ipr.NumericValueLow AND ipr.NumericValueHigh)

-- Check BoundaryType 1 (ADSITE)

OR (ipr.BoundaryType = 1 AND ipr.Value = sysr.AD_Site_Name0) 

-- Check BoundaryType 0 (IPSUBNET)

OR (ipr.BoundaryType = 0 AND ipr.Value = sub.IP_Subnets0)

-- Check BoundaryType 2 (IPv6)

OR (ipr.BoundaryType = 2 AND ipr.Value = v6.IPv6_Prefixes0)

order by sysr.ResourceID



select rsys.Name0, rsys.ResourceID,

"Content Source Type" = CASE cdhs.DistributionPointType

WHEN 1 THEN 'Cloud DP (Distribution Point)'

WHEN 2 THEN 'Management Point'

WHEN 3 THEN 'Peer Cache'

WHEN 4 THEN 'DP (Distribution Point)'

WHEN 5 THEN 'BranchCache'

WHEN 6 THEN 'Delivery Optimization Peer'

WHEN 7 THEN 'Delivery Optimization Cache Server'

WHEN 8 THEN 'Microsoft Update'

END,

packages.Name as [Content],

cdhs.ContentID,

CAST(SUM(cast(cdhs.BytesDownloaded as float)) / 1073741824 AS DECIMAL(10,2)) as [GB]

into ##temp2

from

v_R_System as rsys

join ClientDownloadHistory as cdh on rsys.ResourceID = cdh.ClientId

join ClientDownloadHistorySources as cdhs on cdh.id = cdhs.DownloadHistoryID

join ClientDownloadHistoryBoundaryGroups as cdhbg on cdh.id = cdhbg.DownloadHistoryID JOIN

                         v_FullCollectionMembership AS a ON rsys.ResourceID = a.ResourceID JOIN

                         v_Collection AS b ON b.CollectionID = a.CollectionID LEFT JOIN CI_Contentpackages CI on CI.Content_UniqueID = cdhs.ContentID 

JOIN smspackages packages on packages.PkgID = ISNULL(CI.PkgID,cdhs.ContentId)

where (b.CollectionID = 'SMSDM003')

group by rsys.Name0, rsys.ResourceID, cdhs.DistributionPointType, packages.Name, cdhs.ContentID

order by rsys.Name0, rsys.ResourceID, cdhs.DistributionPointType, packages.Name, cdhs.ContentID



select

t1.Name0, t1.Boundary, t1.Value, t2.[Content Source Type], t2.Content, t2.GB

from ##temp1 as t1 join

##temp2 as t2 on t1.ResourceID = t2.ResourceID



drop table if exists ##temp1

drop table if exists ##temp2

ConfigMgr Systems Pending Reboot Collections and Queries

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”

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

Using PowerShell to fake converting ConfigMgr IPSubnet to IPRange boundaries

Over the years that I have done work ConfigMgr, there was a back and forth on what type of boundaries to use between IPRange and IPSubnet. I have worked in places that were one or the other as well as mixed. Normally, what was used was determined by the suggestion of a Microsoft PFE. Now, I want to convert my IPSubnet boundaries to IPRange boundaries. Continue reading “Using PowerShell to fake converting ConfigMgr IPSubnet to IPRange boundaries”