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
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.