SCCM SQL Query: Finding Machines with Low Disk Space

This SQL query pulls low disk space information straight from the SCCM database, useful for building an SSRS report or just running ad hoc against machines that are running critically low on free space.

Requirements

The Logical Disk class must be enabled in your hardware inventory settings (Client Settings > Hardware Inventory > Classes) — without it, the v_GS_LOGICAL_DISK view will be empty.

The Query

SELECT distinct
SYS.Name,
LDSK.Description0 AS [Description],
LDSK.DeviceID0 AS [Drive],
LDSK.VolumeName0 AS [Volume Name],
LDSK.FileSystem0 AS [Filesystem],
LDSK.Size0 AS [Total Size],
LDSK.FreeSpace0 AS [Free Space]
FROM v_FullCollectionMembership_Valid SYS
JOIN v_GS_LOGICAL_DISK LDSK on SYS.ResourceID = LDSK.ResourceID
WHERE LDSK.DriveType0 = 3
AND ((LDSK.FreeSpace0 <= ((LDSK.Size0 * 10)/100)) or (LDSK.FreeSpace0 <= 1024))
and sys.CollectionID = 'SMS00001'

This returns any fixed drive (DriveType0 = 3) that's either below 10% free space or has less than 1GB free, scoped to the All Systems collection (SMS00001). Swap the collection ID to scope it to a specific group of machines instead.

Adding a Percentage-Free Column

If you're feeding this into an SSRS report rather than reading it ad hoc, it's often more useful to show the actual percentage free rather than making the reader do the maths from Total/Free Size. Add a calculated column:

SELECT distinct
SYS.Name,
LDSK.Description0 AS [Description],
LDSK.DeviceID0 AS [Drive],
LDSK.VolumeName0 AS [Volume Name],
LDSK.FileSystem0 AS [Filesystem],
LDSK.Size0 AS [Total Size MB],
LDSK.FreeSpace0 AS [Free Space MB],
CAST(ROUND((LDSK.FreeSpace0 * 100.0) / NULLIF(LDSK.Size0, 0), 1) AS DECIMAL(5,1)) AS [Percent Free]
FROM v_FullCollectionMembership_Valid SYS
JOIN v_GS_LOGICAL_DISK LDSK on SYS.ResourceID = LDSK.ResourceID
WHERE LDSK.DriveType0 = 3
AND ((LDSK.FreeSpace0 <= ((LDSK.Size0 * 10)/100)) or (LDSK.FreeSpace0 <= 1024))
and sys.CollectionID = 'SMS00001'
ORDER BY [Percent Free] ASC

The NULLIF(LDSK.Size0, 0) guards against a divide-by-zero error on any drive that's reporting a zero total size (rare, but it happens with certain virtual/dynamic disks), and the ORDER BY puts your most critical machines at the top of the report.

Still Applies to Current Configuration Manager

This is standard SCCM/Configuration Manager hardware inventory schema — v_GS_LOGICAL_DISK and v_FullCollectionMembership_Valid are core views that have been stable across every SCCM/ConfigMgr release for years and remain unchanged in the current Configuration Manager branch. No changes needed here regardless of which current version you're running.

Resources

• query
• disk space
• WQL
• our SQL & WQL query library