SCCM SQL Query to get Disk Space Details

Question

Hi team,

Below query generates output in Bytes size
SELECT DISTINCT SiteSystem AS [Site System], Role, BytesTotal AS [Total Disk Space], BytesFree AS [Free Disk Space], PercentFree AS [% Free]
FROM dbo.vSummarizer_SiteSystem
WHERE (Role LIKE 'SMS Distribution Point')

Please help to modify the query from Bytes Size to GB size.
Also how to add DP Name instead of Site System Role. Because it outputs the Role as **["Display=\DP-DP-LA.adatum.contoso.com\"]MSWNET:["SMS_SITE=TUL"]\DP-LA.adatum.contoso.com**

I cannot join vDistributionPoints and vSummarizer_SiteSystem to get the DP name

Answer 1

Hi, @Boopathi Subramaniam
Thank you for posting in Microsoft Q&A forum.

I think you are trying to get all DP's Disk Space Details, you may try the query like below:

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'   

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Did you try using v_gs_logical_disk instead?

Answer 3

But that Sql view is not supported to be queried.
What have you tried and what isn't working? Aka what problem are you trying to solve?