SCCM 2012 SQL Query for DPs with Package Failures

Author by Matt Herman

In my last post, I covered a script I use to redistribute failed packages on Distribution Points (DPs).  The script relies on a CSV file to control what DPs have content redistributed, and here is the SQL query I use to determine what DPs should be in the CSV.

    DPSI.Name, DPSI.NumberInstalled, DPSI.NumberInProgress, DPSI.NumberErrors, DPSI.NumberUnknown

    vSMS_DPStatusInfo DPSI

    DPSI.NumberErrors > 0 and
    DPSI.NumberInProgress = 0 and
    DPSI.NumberUnknown = 0
    /* DPSI.Name */

It’s not a complex query because I’m utilizing the vSMS_DPStatusInfo view, which pulls together all the data I need.  Basically, I’m looking for DPs that have errors and are not currently processing anything.  That way, I’m not wasting time running the script against DPs that have finished or are already syncing content.

When I run this, I will generally use the ORDER BY DPSI.NumberErrors to populate the CSV file.  This way the DPs with the fewest queue first, and hopefully complete first, leaving fewer servers for the next run.  I use the ORDER BY DPSI.Name to archive the results and track progress over time.


Matt Herman

Technical Architect