SCCM 2012 Content Progress Report

In ConfigMgr 2012 there’s a handy little node on the ‘Monitoring’ panel called ‘Content Status’ under ‘Distribution Status’. This will return all content, whether that is Applications, Packages, OS Images, Drivers etc, and the current distribution status of them.

If you click one of them, you’ll get a nice pie chart showing a summary of Failed, In Progress and Successfully distributed/verified on your distribution points.

You can then click ‘View Status’ and it will give a breakdown of the individual distribution points for that package and a description of their individual status (i.e. awaiting content, failed to validate hash etc). Here’s an example of the ‘In Progress’ and ‘Failed’ nodes for a package recently distributed:

StatusInProgress

StatusError

This is great, it shows which distribution points have failed distribution, those that are in progress, and those that have succeeded, but only for that package. What if you wanted to find all packages that were in a specific distribution state, or even those that have a specific status message (i.e. all those that had a content hash mismatch, or all those awaiting prestaged content).

I couldn’t find a built-in report to show me this, or at least not one that replicated the detail that I was seeing in the console, so I decided to write my own.

The SQL below will generate a report similar to that in the screenshot below. It shows the Package ID for the content, the name and type, the target distribution point with site code, the status (Success, In Progress or Failed, and also give the option of ALL), the actual Message as displayed in the console and the number of groups the distribution point is a member of. This can then be exported (e.g. to excel) for further data manipulation and sorting/filtering.

Report

Main SQL Query:

SELECT
SMS_DistributionDPStatus.PackageID,
dbo.v_Package.Manufacturer + ‘ ‘ + dbo.v_Package.Name + ‘ ‘ + dbo.v_Package.[Version] AS [Content Name],
REPLACE(dbo.RBAC_SecuredObjectTypes.ObjectTypeName, ‘SMS_’,) AS [Content Type],
REPLACE(SMS_DistributionDPStatus.Name, ‘\’, ”) AS [Distribution Point],
SMS_DistributionDPStatus.SiteCode,
CASE SMS_DistributionDPStatus.MessageState
WHEN 1 THEN ‘Success’
WHEN 2 THEN ‘In Progress’
WHEN 4 THEN ‘Failed’
ELSE ‘Unknown’
END AS [Status],
CASE SMS_DistributionDPStatus.MessageID
WHEN 2303 THEN ‘Content was successfully refreshed’
WHEN 2324 THEN ‘Failed to access or create the content share’
WHEN 2330 THEN ‘Content was distributed to distribution point’
WHEN 2384 THEN ‘Content hash has been successfully verified’
WHEN 2323 THEN ‘Failed to initialize NAL’
WHEN 2354 THEN ‘Failed to validate content status file’
WHEN 2357 THEN ‘Content transfer manager was instructed to send content to the distribution point’
WHEN 2360 THEN ‘Status message 2360 unknown’
WHEN 2370 THEN ‘Failed to install distribution point’
WHEN 2371 THEN ‘Waiting for prestaged content’
WHEN 2372 THEN ‘Waiting for content’
WHEN 2380 THEN ‘Content evaluation has started’
WHEN 2381 THEN ‘An evaluation task is running. Content was added to the queue’
WHEN 2382 THEN ‘Content hash is invalid’
WHEN 2383 THEN ‘Failed to validate content hash’
WHEN 2391 THEN ‘Failed to connect to remote distribution point’
WHEN 2398 THEN ‘Content Status not found’
WHEN 8203 THEN ‘Failed to update package’
WHEN 8204 THEN ‘Content is being distributed to the distribution point’
WHEN 8211 THEN ‘Failed to update package’
ELSE ‘Status message ‘ + CAST(SMS_DistributionDPStatus.MessageID AS VARCHAR) + ‘ unknown’
END AS [Message],
SMS_DistributionDPStatus.GroupCount AS [Group Count]
FROM
dbo.vSMS_DistributionDPStatus AS SMS_DistributionDPStatus
INNER JOIN dbo.RBAC_SecuredObjectTypes
ON SMS_DistributionDPStatus.ObjectTypeID = dbo.RBAC_SecuredObjectTypes.ObjectTypeID
LEFT OUTER JOIN dbo.v_Package
ON SMS_DistributionDPStatus.PackageID = dbo.v_Package.PackageID
WHERE (SMS_DistributionDPStatus.MessageState = @MessageState) OR (@MessageState = 999)
ORDER BY PackageID ASC

Prompt SQL Query:

SELECT DISTINCT
CASE SMS_DistributionDPStatus.MessageState
WHEN 1 THEN ‘Success’
WHEN 2 THEN ‘In Progress’
WHEN 4 THEN ‘Failed’
ELSE ‘Unknown’
END AS [Status],
SMS_DistributionDPStatus.MessageState AS [State Value]
FROM
vSMS_DistributionDPStatus AS SMS_DistributionDPStatus
UNION
SELECT
‘ALL’ AS [Status],
999 AS [State Value]
ORDER BY
SMS_DistributionDPStatus.MessageState DESC

 

Note: due to the way WordPress converts apostrophes and quotation marks, it’s probable that a direct copy and paste of the SQL will not run properly, please be sure to replace apostrophes with SQL-accepted quotes.

There are some unknown message IDs that I have missed off, I will add them in as and when I come across them. Please feel free to comment with any new ones and I will amend the post accordingly.
Or if there is a table in the ConfigMgr database that contains them all, please let me know.

Leave a Reply

Your email address will not be published.Required fields are marked *