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.
vDDPs.Name, vDDPs.PackageID, vDDPs.MessageState
vDDPs.MessageState = 4
/* vDDPs.Name */
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.
DPSI.Name, DPSI.NumberInstalled, DPSI.NumberInProgress, DPSI.NumberErrors, DPSI.NumberUnknown
DPSI.NumberErrors > 0 OR
DPSI.NumberInProgress > 0 OR
DPSI.NumberUnknown > 0
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.