SCCM Configmgr SSRS SQL report Status of All Packages with Targeted Installed Compliance %

Reporting in Configuration Manager 2012 and later, is Replaced with SSRS with more features and little complicated if you have not used to it.

In this blog post, we will see how to check the status of the packages with their distribution status.

To know the Status of content (Packages) ,you can look at monitoring Node—>Distribution Status—>Content But somehow i did not like view to go every time (I feel it takes much time to load all the packages) to know the distribution status of all applications such as apps, packages, software update packages etc created in configuration manager.

I then created simple SQL Query which you can use to create SSRS report in nice format or Run it from SQL Server management Studio.

What you get from this : Software Name,PackageID,Package Type ,Targeted,Installed,Not Installed,Compliance,Manufacturer,Source Size in MB,Source Version.

The following is the SQL query to get the status of all apps that are distributed to atleast 1 distribution point.

SELECT  distinct v_Package.Name [Software], PSD.PackageID,
Case v_Package.PackageType
When 0 Then 'Package'
When 3 Then 'Driver'
When 4 Then 'Task Sequence'
When 5 Then 'software Update'
When 7 Then 'Virtual'
When 8 Then 'Application'
When 257 Then 'Image'
When 258 Then 'Boot Image'
When 259 Then 'OS'
Else ' '
END AS 'Type',
PSR.Targeted ,PSR.Installed,(psr.Failed+psr.Retrying) as [Not Installed],
ROUND((100 * psr.installed/PSR.Targeted),2) as 'Compliance',
v_Package.Manufacturer, SMSPackages.SourceSize / 1000 AS [Size (MB)]
,v_Package.SourceVersion
FROM         v_PackageStatusDistPointsSumm PSD
INNER JOIN v_Package ON PSD.PackageID = v_Package.PackageID
INNER JOIN SMSPackages ON v_Package.PackageID = SMSPackages.PkgID
inner JOIN v_PackageStatusRootSummarizer PSR ON PSR.PackageID=PSD.PackageID
group by v_Package.Name,psd.PackageID,v_Package.PackageType,psr.Targeted,PSR.Installed,v_Package.Manufacturer,SMSPackages.SourceSize,v_Package.SourceVersion,
PSR.Failed,PSR.Retrying
ORDER BY 7

The following is the updated query to list the status of all applications irrespective of their distribution status (if they are  distributed or not).

SELECT distinct v_Package.Name [Software], case when PSD.PackageID is null then v_Package.PackageID else PSD.PackageID end as PackageId ,
Case v_Package.PackageType
When 0 Then 'Package'
When 3 Then 'Driver'
When 4 Then 'Task Sequence'
When 5 Then 'software Update'
When 7 Then 'Virtual'
When 8 Then 'Application'
When 257 Then 'Image'
When 258 Then 'Boot Image'
When 259 Then 'OS'
Else ' '
END AS 'Type',
PSR.Targeted ,PSR.Installed,(psr.Failed+psr.Retrying) as [Not Installed],
--@Product1 / NULLIF(@Product2,0)
ROUND((100 * psr.installed/nullif(PSR.Targeted,0)),2) as 'Compliance',
v_Package.Manufacturer, SMSPackages.SourceSize / 1000 AS [Size (MB)]
,v_Package.SourceVersion
from v_Package
left join v_PackageStatusDistPointsSumm PSD on v_Package.PackageID=PSD.PackageID
left JOIN SMSPackages ON v_Package.PackageID = SMSPackages.PkgID
left JOIN v_PackageStatusRootSummarizer PSR ON v_Package.PackageID=PSR.PackageID
group by v_Package.Name,psd.PackageID,v_Package.PackageType,psr.Targeted,PSR.Installed,v_Package.Manufacturer,SMSPackages.SourceSize,v_Package.SourceVersion,
PSR.Failed,PSR.Retrying,v_Package.PackageID
ORDER BY 7

More fields, you can customize it.

Next post, it will be SSRS SQL report :Status of Failed Packages on what Distribution points .This can be linked to above report nice way for Troubleshooting. http://eskonr.com/2013/09/sccm-configmgr-2012-ssrs-report-package-compliance-status-for-all-distribution-points/

Hope this helps!