Compare Content Between SCCM DPs Using SQL Queries

 In enterprise environments using Microsoft Configuration Manager (SCCM), ensuring content consistency across Distribution Points (DPs) is crucial for reliable software deployments, operating system imaging, and patching. When introducing a new DP or auditing content, it's helpful to compare what content is available on one DP versus another.

This post provides a practical SQL query to compare two DPs and list packages that are available on one but missing from the other.

---

📌 Goal

We aim to:

• Compare content between:
• SCCMDP1 (source/master DP)
• SCCMCP2 (target/secondary DP)
• Identify which packages exist on SCCMDP1 but do not exist on SCCMCP2
• Output key details: PackageID, Name, and Type

---

🧠 SQL Query Logic

We utilize SCCM database views:

• v_Package – lists package metadata
• v_DistributionPoint – maps packages to the DPs where they are distributed

---

🧾 SQL Query

SELECT

    Pkg.PackageID,

    Pkg.Name,

    CASE Pkg.PackageType

        WHEN 0 THEN 'Package'

        WHEN 3 THEN 'Driver'

        WHEN 4 THEN 'Task Sequence'

        WHEN 5 THEN 'Software Update'

        WHEN 7 THEN 'Virtual Application'

        WHEN 8 THEN 'Application'

        WHEN 257 THEN 'Image'

        WHEN 258 THEN 'Boot Image'

        WHEN 259 THEN 'Operating System Installer'

        ELSE 'Unknown'

    END AS PackageType

FROM v_Package Pkg

WHERE Pkg.PackageID IN (

    SELECT PackageID

    FROM v_DistributionPoint

    WHERE ServerNALPath LIKE '%SCCMDP1%' -- Source DP

    AND PackageID NOT IN (

        SELECT PackageID

        FROM v_DistributionPoint

        WHERE ServerNALPath LIKE '%SCCMCP2%' -- Target DP

    )

)

ORDER BY PackageType;

---

🧪 Example Output

PackageID	Name	PackageType
ABC00001	Windows 11 Deployment TS	Task Sequence
DRV00200	Dell Latitude 5530 Driver	Driver
APP00345	Adobe Reader Install	Application

---

🛠️ Usage Instructions

1. Open SQL Server Management Studio (SSMS).
2. Connect to your SCCM Site Database (typically CM_<SiteCode>).
3. Paste and execute the query.
4. Review the list to determine what content is missing on SCCMCP2.

---

🔄 Optional Variations

• Reverse the comparison by swapping SCCMDP1 and SCCMCP2.
• Compare multiple DPs using more advanced JOIN logic or CTEs.
• Add columns from v_PackageStatusDistPointsSumm for package status on each DP.

---

✅ Conclusion

This query provides a quick and reliable way to audit SCCM Distribution Point content, especially when:

• Migrating from one DP to another
• Validating replication status
• Troubleshooting missing content on clients

Keeping DPs in sync ensures faster content access and fewer deployment failures across your SCCM-managed environment.