SCCM Configmgr 2012 SSRS Report Package compliance status for all Distribution Points

Simple and useful report for troubleshooting package issues:

Package compliance Status report to know what is the success rate of the Applications/Packages/Driver/SUP on the Distribution Points:

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
order by 5

 

SSRS report looks like:

Status

To know list of failed packages on the specific Server,create report using below SQL Query and link it to column 'Not Installed' above report for ease of troubleshooting.

SELECT psd.PackageID,p.Name,
SUBSTRING(PSD.ServerNALPath, 13, CHARINDEX('.', PSD.ServerNALPath) -13 ) AS [Server Name] ,
PSd.LastCopied,PSd.SiteCode [Reporting Site],PSD.SourceVersion,
SP.SourceSize / 1000 AS [Size (MB)],
Case P.PackageType When 0 Then 'Package' When 8 Then 'Application' When 3 Then 'Driver' When 4 Then 'Task Sequence' When 5 Then 'software Update'
When 7 Then 'Virtual' When 257 Then 'OSImage' When 258 Then 'BootImage' When 259 Then 'OS' else ' ' END AS 'Type',
PSd.SummaryDate,PSD.InstallStatus
from v_PackageStatusDistPointsSumm PSD
inner join v_Package P ON P.PackageID=psd.PackageID
inner join SMSPackages SP ON SP.PkgID=P.PackageID
inner join v_PackageStatusRootSummarizer PSR ON PSR.PackageID=psd.PackageID
WHERE (PSd.State <> 0) and (SUBSTRING(PSD.ServerNALPath, 13, CHARINDEX('.', PSD.ServerNALPath) -13 ))=@server
and p.PackageType!=4
order by 7

 

8 Responses to "SCCM Configmgr 2012 SSRS Report Package compliance status for all Distribution Points"

    1. yes but the values for the state value you have used are incorrect. for Ex: state 3 means it is failed.
      check the stage messages and their meanings. i just checked for failed package,it has value 3 but your report says installed.
      v_ContentDistribution state has only 0,1,2,3,4 values init.

      Reply
  1. Nice idea for a report!

    I would use a JOIN instead in the SQL... like this:

    SELECT DISTINCT
    UPPER(SUBSTRING(CDR.DPNALPath,13,CHARINDEX('.', CDR.DPNALPath) -13)) AS ServerName,
    CDR.PkgCount AS Targeted,
    CDR.NumberInstalled AS Installed,
    CDR.PkgCount-CDR.NumberInstalled AS NotInstalled,
    PSd.SiteCode AS ReportingSite,
    ROUND((100 * CDR.NumberInstalled/CDR.pkgcount), 2) AS Compliance

    FROM
    v_ContentDistributionReport_DP CDR LEFT JOIN v_PackageStatusDistPointsSumm PSd
    ON CDR.DPNALPath=PSD.ServerNALPath

    ORDER BY
    6 ASC

    Reply

Leave a Reply to Rikard Ronnkvist Cancel reply