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:


/* 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
--WHERE Role = 'SMS Distribution Point' AND BytesFree < 52428800 -- 50GB
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$%')
ORDER BY [Free (GB)]

Leave a Comment