Here is the SQL statement for my SCCM Report on disk free space under 20%, it will display the user machine AD Site, PC Name, Last Logon User, Total Disk Space, Free Space, Free Space Percent( All in GB), Drive Name, Model Name ( Lenovo models will be on Model #1, the rest will be on Model #2), OS, User Work Email, Title, Department, Employee Location, and Employee Manger information.
SELECT TOP (100) PERCENT RV.AD_Site_Name0, v_R_User_1.description0, RV.Netbios_Name0, RV.User_Name0, dbo.v_GS_LOGICAL_DISK.Size0 / 1024 AS [Size(GB)],
dbo.v_GS_LOGICAL_DISK.FreeSpace0 / 1024 AS [Free Space], 100 * dbo.v_GS_LOGICAL_DISK.FreeSpace0 / dbo.v_GS_LOGICAL_DISK.Size0 AS [Free Space Percent], dbo.v_GS_LOGICAL_DISK.Name0,
dbo.v_GS_COMPUTER_SYSTEM_PRODUCT.Version0, dbo.v_GS_COMPUTER_SYSTEM.Model0, OS.Caption0, v_R_User_1.department0, v_R_User_1.Mail0, v_R_User_1.manager0, v_R_User_1.title0
FROM dbo.v_R_System_Valid AS RV INNER JOIN
dbo.v_R_User AS v_R_User_1 ON RV.User_Name0 = v_R_User_1.User_Name0 INNER JOIN
dbo.v_GS_LOGICAL_DISK ON RV.ResourceID = dbo.v_GS_LOGICAL_DISK.ResourceID INNER JOIN
dbo.v_GS_COMPUTER_SYSTEM_PRODUCT ON dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_GS_COMPUTER_SYSTEM_PRODUCT.ResourceID INNER JOIN
dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID LEFT OUTER JOIN
dbo.v_GS_OPERATING_SYSTEM AS OS ON RV.ResourceID = OS.ResourceID CROSS JOIN
dbo.v_R_User
WHERE (100 * dbo.v_GS_LOGICAL_DISK.FreeSpace0 / dbo.v_GS_LOGICAL_DISK.Size0 < 20)
GROUP BY RV.Netbios_Name0, RV.User_Name0, OS.Caption0, RV.AD_Site_Name0, v_R_User_1.department0, v_R_User_1.description0, v_R_User_1.Mail0, v_R_User_1.manager0, v_R_User_1.title0,
dbo.v_GS_LOGICAL_DISK.FreeSpace0, dbo.v_GS_LOGICAL_DISK.Name0, dbo.v_GS_LOGICAL_DISK.Size0, dbo.v_GS_COMPUTER_SYSTEM_PRODUCT.Version0, dbo.v_GS_COMPUTER_SYSTEM.Model0
HAVING (dbo.v_GS_LOGICAL_DISK.Name0 = N’C:’)
ORDER BY [Free Space Percent]
Also, it will show red if the free space under 5%, orange under 10%, and the rest in green color. Here is how you can color coding the data in SSRB:
