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!
6 Comments
Thank you for sharing the query, but I am only getting packages with targeted to 1 or more dp's. However, its not showing anything with do DP targeted even though in the console it shows those. Any idea how to reflect that?
Hi Carl,
thank you for the query.
The following is the modified query for your request. I have also updated the blog post with this query too!
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
Thanks,
Eswar
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
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
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 ?
You need to have read permissions on the database.Watch the blog on this