MEMCM – Find Content Only Deployed Directly to a Distribution Point

So you want to remove a SCCM Distribution Point, but you are not sure if there is any content on there that is only distributed to it, rather that to the Distribution Point Group , do I have a SQL query for you!

I think it is good practice to deploy the content to the DPG rather than a DP, so that if you ever need to rebuild the DP, the list of content is help in the DPG.

In my case I want to rebuild my two DPs in a DPG as 2019 servers and I want to keep their IP addresses as that saves updating the IP Helpers everywhere on the network.

This is based on SCCM SQL query to list all the content of distribution point group but slightly different, I am using two functions:

  • cm_wto.dbo.fn_ListDPGroupContents – lists contents in a DPG
  • cm_wto.dbo.fn_ListDPContents – lists contents in a DP

Which means the CASE statement had to change

The query returns any content that is listed in the DP that is not listed in the DP group. This way you can then publish that content to the DPG, then remove your DP in confidence nothing will break!

select all SMS_DPContentInfo.Description,SMS_DPContentInfo.IsPredefinedPackage,SMS_DPContentInfo.NALPath,SMS_DPContentInfo.Name,SMS_DPContentInfo.ObjectID,SMS_DPContentInfo.ObjectType,SMS_DPContentInfo.ObjectTypeID,SMS_DPContentInfo.PackageID,SMS_DPContentInfo.SourceSize,
WHEN SMS_DPContentInfo.ObjectType = 0 THEN 'Software Distribution Package'
WHEN SMS_DPContentInfo.ObjectType = 3 THEN 'Driver Package'
WHEN SMS_DPContentInfo.ObjectType = 4 THEN 'Task Sequence Package'
WHEN SMS_DPContentInfo.ObjectType = 5 THEN 'Software Update Deployment Package'
WHEN SMS_DPContentInfo.ObjectType = 6 THEN 'Device Setting Package'
WHEN SMS_DPContentInfo.ObjectType = 7 THEN 'Virtual Package'
WHEN SMS_DPContentInfo.ObjectType = 257 THEN 'Operating System Images Package'
WHEN SMS_DPContentInfo.ObjectType = 258 THEN 'Boot Image Package'
WHEN SMS_DPContentInfo.ObjectType = 259 THEN 'Operating System Install Package'
WHEN SMS_DPContentInfo.ObjectType = 512 THEN 'Software Distribution Application'
ELSE 'Unknown'
END AS 'ObjectType Description'
from cm_wto.dbo.fn_ListDPContents(1033) 
AS SMS_DPContentInfo 

LEFT JOIN cm_wto.dbo.fn_ListDPGroupContents(1033) AS SMS_DPGroupContentInfo 
ON SMS_DPContentInfo.PackageID = SMS_DPGroupContentInfo.PackageID

where SMS_DPContentInfo.NALPath = N'["Display=\\\"]MSWNET:["SMS_SITE=ZZZ"]\\DP1.domain.comz\'
and SMS_DPGroupContentInfo.PackageID is NULL

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.