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:
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 Comments
Now the query is a bit better. Using JOIN to add info on State and PackageType
Hrm... I Guess Jörgen Nilsson's info was from CM07.
Will look in to it and update the post.
Thanks for the heads up.
oh yes.CM12 has content library thus changes 🙂
Gud one
Did a little bit more work around this...
http://www.snowland.se/2013/10/07/configmgr-package-status-reports/
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.
Pingback: ConfigMgr Package Status Reports »
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