Require SQL query for non system drive disk free space

Question

Hello All,

Need to find out the server starts from ABCDXXXX which server has the most free disk space on a non C: drive (e.g. D:,E:,F:,G: etc.)

kindly assist me on this

Thanks & Regards,
Kumar B

Answer 1

Hi,

To achieve our goal, we may try the following query.

SELECT bme.Path as ServerName, ps.PerfmonInstanceName as DriveLetter, ROUND(pdav.SampleValue,0) as FreeSpace, pdav.TimeSampled as DateTime, r.RuleName 
FROM PerformanceDataAllView (NOLOCK) AS pdav  
INNER JOIN PerformanceSource (NOLOCK) ps ON pdav.PerformanceSourceInternalId = ps.PerformanceSourceInternalId 
INNER JOIN Rules (NOLOCK) r ON ps.RuleId = r.RuleId 
INNER JOIN BaseManagedEntity (NOLOCK) bme ON ps.BaseManagedEntityID = bme.BaseManagedEntityID 
WHERE r.RuleName like 'Microsoft.Windows.Server.%%.LogicalDisk.FreeSpace.Collection' and ps.PerfmonInstanceName <> 'C:'
AND pdav.TimeSampled = (SELECT MAX(TimeSampled) FROM PerformanceDataAllView WHERE PerformanceSourceInternalId = pdav.PerformanceSourceInternalId )
--AND SampleValue < 20 /* To list drive having less than 20 percentage */
--and bme.Path ='Server Name' /* add FQDN for specific server */
ORDER BY path,PerfmonInstanceName

Hope the above information helps.

Alex Zhu

---

If the response is helpful, please click "Accept Answer" and upvote it.