SQL Query for Distribution Point Free Space

In this SQL query, I return the Server Name, Site Code, Description, Domain Name, Volume, Total (GB), Free (GB) and % Free.

The query uses 3 main views:

vDistributionPoints
vSummarizer_SiteSystem
v_R_System

You can use this in a Daily Dashboard to identify servers experiencing low disk space and remediate them before the Distribution Point space becomes critically low.

/* SCCM Distribution Points with < 25GB Free Space */
SELECT DISTINCT UPPER(SUBSTRING(dp.ServerName, 1, CHARINDEX ('.', dp.ServerName) -1)) AS 'Distribution Point', SMSSiteCode AS 'Site Code', Description, sys.Resource_Domain_OR_Workgr0 AS 'Domain', SUBSTRING(ss.SiteObject, CHARINDEX('$', ss.SiteObject) - 1, CHARINDEX('$\', ss.SiteObject) - CHARINDEX('$', ss.SiteObject) + 2) AS 'Volume', ss.BytesTotal / 1048576 AS 'Total (GB)', ss.BytesFree / 1048576 AS 'Free (GB)', ss.PercentFree AS '% Free' 
FROM vDistributionPoints AS dp LEFT OUTER JOIN v_R_System AS sys ON (UPPER(SUBSTRING(dp.ServerName, 1, CHARINDEX ('.', dp.ServerName) -1)) = sys.Name0) RIGHT OUTER JOIN 
vSummarizer_SiteSystem AS ss ON UPPER(SUBSTRING(ss.SiteSystem, CHARINDEX('\\', ss.SiteSystem) + 2, CHARINDEX('"]', ss.SiteSystem) - CHARINDEX('\\', ss.SiteSystem) - 3)) = dp.ServerName 
WHERE ss.Role = 'SMS Distribution Point' AND ss.BytesFree < 26214400 -- 25GB
ORDER BY [Free (GB)]


/* Use this for 50GB Threshold */
--WHERE Role = 'SMS Distribution Point' AND BytesFree < 52428800 -- 50GB 

/* Add this to WHERE statement to exclude certain drive letters */
--AND (ss.SiteObject LIKE '%D$%' OR ss.SiteObject LIKE '%E$%' OR ss.SiteObject LIKE '%J$%' OR ss.SiteObject LIKE '%H$%' OR ss.SiteObject LIKE '%K$%')