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.

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

FROM
    vSMS_DPStatusInfo DPSI

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

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.

Author

Matt Herman

Technical Architect