SCCM Distribution Point Status: Reporting on Every DP’s Health

Configuration Manager’s console shows Distribution Point status under the Monitoring workspace, but if you want that information available to people who don’t have console access — say, via a web-based SSRS report — you can pull the same data straight from the site database with SQL.

What this reports on

This query summarises, per distribution point, how many packages are targeted, how many installed successfully, how many failed, and a success percentage — the same figures the console’s “Distribution Point Configuration Status” view shows, but in report form.

select UPPER
(SUBSTRING(PSD.ServerNALPath,13,CHARINDEX('.', PSd.ServerNALPath) -13)) AS [DP Name],
count(*) [Targeted] ,
count(CASE when PSD.State='0' then '*' END) AS 'Installed',
count(CASE when PSD.State not in ('0') then '*' END) AS 'Not Installed',
round((CAST(SUM (CASE WHEN PSD.State='0' THEN 1 ELSE 0 END) as float)/COUNT(psd.PackageID ) )*100,2) as 'Success%',
psd.SiteCode [Reporting Site]
From v_PackageStatusDistPointsSumm psd,SMSPackages P
where p.PackageType!=4
and (p.PkgID=psd.PackageID)
group by PSd.ServerNALPath,psd.SiteCode

The p.PackageType!=4 filter excludes a specific internal package type from the results — leave it as-is unless you specifically need to include those. The ServerNALPath substring extraction pulls a readable DP name out of the raw NAL path string stored in the table.

Using it

You can run this directly against the SCCM site database for ad-hoc checks, or drop it into an SSRS report definition so it’s available to helpdesk or operations staff without console access. Adjust the GROUP BY and add extra columns from v_PackageStatusDistPointsSumm if you want more granular breakdowns, e.g. by package name.