SCCM. Distribution Points Challenges. Part 2

I have not been blogging about new DP fixing methods for awhile. Last  post was eight month ago. It doesn’t mean I am not doing it anymore or I finally found  silver bullet for package distribution problems. Nope, I’ve been too busy with my SCCM infrastructure. Have found a few new tricks and need to “document” them before I forget.

There are two approaches to fixing distribution problem:  indentify bad Distribution Point or bad package.  I use two custom reports for both methods.

“Number of active distributions per DP”  query

SELECT  s.SiteCode,
SUBSTRING(s.ServerNALPath, CHARINDEX(‘\\’, s.ServerNALPath) + 2, CHARINDEX(‘"]’, s.ServerNALPath) – CHARINDEX(‘\\’, s.ServerNALPath) – 3 ) AS C070,
COUNT(s.sitecode) AS ‘# of Packages’,
  SUM(r.SourceCompressedSize)  / 1024 AS ‘Compressed Size [MB]’ , SUM(r.SourceSize)  / 1024 AS ‘Total Size [MB]’
FROM  v_PackageStatusDistPointsSumm s
JOIN  v_PackageStatusRootSummarizer r ON s.PackageID = r.PackageID
WHERE ( State!=0 AND State!=4)
GROUP BY s.SiteCode, SUBSTRING(s.ServerNALPath, CHARINDEX(‘\\’, s.ServerNALPath) + 2, CHARINDEX(‘"]’, s.ServerNALPath) – CHARINDEX(‘\\’, s.ServerNALPath) – 3 )
HAVING COUNT(s.sitecode)  > 4
ORDER BY COUNT(s.sitecode) DESC, s.SiteCode

 

and “Number of active distributions per package”

SELECT COUNT(*) AS ‘# of Sites’,  s.PackageID,  p.Name,
   rootsum.SourceCompressedSize  / 1024 AS ‘Compressed Size [MB]’ , rootsum.SourceSize  /1024 AS ‘Size [MB]’,
   p.LastRefreshTime, p.SourceDate
FROM  v_PackageStatusDistPointsSumm  s
JOIN v_Package p ON p.PackageID=s.PackageID
JOIN v_PackageStatusRootSummarizer  rootsum ON p.PackageID=rootsum.PackageID
WHERE (s.State!=0 AND s.State <> 4)
GROUP BY s.PackageID,  p.Name, p.LastRefreshTime, p.SourceDate,
rootsum.SourceCompressedSize  / 1024,  rootsum.SourceSize  / 1024
HAVING COUNT(s.sitecode)  > 4
ORDER BY COUNT(*) DESC, s.PackageID

 

I use four packages as a threshold. 

More scripts are coming soon

Advertisements

About oleggap

IT Pro
This entry was posted in SCCM. Bookmark the permalink.