More SQL Queries for Monitoring SCCM 2012 Package Distribution

To keep going on my monitoring packages on Distribution Points (DPs) theme for SCCM 2012, PowerShell Script to Redistribute Failed Packages & SQL Query for DPs with Failed Packages, here are a couple more quick queries that I use. A quick note before I get into the queries.  The structure and organization of the SCCM DB can be confusing if you aren’t familiar with it.  If you’re not sure what you are looking for, I suggest that you don’t look at the tables, instead look at the views.  Microsoft has done a great job creating views that pull together useful information.  In fact, if you take a look at the queries Microsoft has written for most of the reports in SCCM, they utilize views instead of querying tables directly.  Both of my queries here are simple because they utilize views. First, here is a query to see all the DPs with package errors.

SELECT vDDPs.Name, vDDPs.PackageID, vDDPs.MessageState FROM vSMS_DistributionDPStatus vDDPs WHERE vDDPs.MessageState = 4 ORDER BY /* vDDPs.Name */ vDDPs.PackageID

In this case, I am utilizing the vSMS_DistributionDPStatus view, which contains the status for every instance of content on DPs.  MessageState 4 is for errors, but this can be modified to 2 for packages in process and 1 for successful packages.  Also, you can toggle the Order By between Name and PackageID, depending on if you want to focus on DPs or specific packages. Second, here is a query to see the statuses of all DPs with content that is not yet synced.

SELECT DPSI.Name, DPSI.NumberInstalled, DPSI.NumberInProgress, DPSI.NumberErrors, DPSI.NumberUnknown FROM vSMS_DPStatusInfo DPSI WHERE ( DPSI.NumberErrors > 0 OR DPSI.NumberInProgress > 0 OR DPSI.NumberUnknown > 0 ) ORDER BY DPSI.Name

The query is similar to my last post, with the where clause edited to include any DPs with a package that has a status other than installed.  Alternatively, you can comment out the entire where clause to see the status of all DPs in your environment.