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

Reporting in CM12 is Replaced with SSRS with more features and little complicated if you are not used to it.

To know the Status of content (Packages) ,you can look at monitoring Node—>Distribution Status—>Content But somehow i did not like this to go Every time (I feel it takes much time to load all the packages) if i want to know the Compliance Status of all created packages in CM12.

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.

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

More fields,customize it.

Next post,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/

4 Responses to "SCCM Configmgr SSRS SQL report Status of All Packages with Targeted Installed Compliance %"

  1. Perfect timing, I have a meeting this morning to discuss eliminating about 95 DPs. This will help make my case that most of them really aren't being used for much today since we have Nomad.

    One addition that may be required. If you have any packages that are not currently targeted at any DPs, you will need this Where statement to avoid a divide by zero error from the "Compliance" field calculation.

    WHERE psr.Targeted 0

    Reply
    1. the query what i posted to get status for all dps what you see in console but your condition refers to the DP's in which,no packages targeted. you can use this condition to know none of the packages targeted to DP

      Reply
  2. Hi,

    I do get the following when executed from SQL Management Studio

    Msg 229, Level 14, State 5, Line 1
    The SELECT permission was denied on the object 'v_PackageStatusRootSummarizer', database 'CM_T01', schema 'dbo'.

    Any idea what goes wrong ?

    Reply

Leave a Reply