United Kingdom Endpoint Management User Group

The first query targets all computer resources and allows you to specify the byte size range as well as the percentage of available disk space.

The second query only targets site servers.  It provide similar options as the first, but also allow you to specify a particular site service.

***************************************************

--/ Disk space on all computer resources
SELECT
vc.[Guid] AS ResourceGuid,
vc.[Name] AS [Resource],
ld.[Device ID] AS Drive,
ld.[Description] AS [Description],
ld.[Size (Bytes)] AS [Disk Size Bytes],
(ld.[Size (Bytes)])/1024/1024/1024 AS [Disk Size GB],
ld.[Free Space (Bytes)] AS [Free Space Bytes],
(ld.[Free Space (Bytes)])/1024/1024 AS [Free Space MB],
CONVERT(DECIMAL(5,2),100.0 * ld.[Free Space (Bytes)] / ld.[Size (Bytes)]) AS [Free Space(%)], --this is the percentage field
pu.[User] AS [Primary User]
FROM vComputer vc
INNER JOIN vHWLogicalDisk ld ON vc.[Guid] = ld._ResourceGuid
LEFT JOIN Inv_AeX_AC_Primary_User pu ON vc.[Guid] = pu._ResourceGuid
WHERE pu.[Month] = DATENAME(mm, GETDATE())
AND LOWER(ld.[Description]) LIKE '%local%'
--AND t1.[Size (Bytes)] > '2000'
--AND CONVERT(DECIMAL(5,2),100.0 * ld.[Free Space (Bytes)] / ld.[Size (Bytes)]) < '10' ---this is the percentage parameter
ORDER BY vc.[Name]


--/ Disk space on site servers only
SELECT DISTINCT
ss.ComputerGuid AS SiteServerGuid,
vc.[Name] AS SiteServer,
ss.[IP Address],
ld.[Device ID] AS Drive,
ld.[Description] AS [Description],
ld.[Size (Bytes)] AS [Disk Size Bytes],
(ld.[Size (Bytes)])/1024/1024/1024 AS [Disk Size GB],
ld.[Free Space (Bytes)] AS [Free Space Bytes],
(ld.[Free Space (Bytes)])/1024/1024 AS [Free Space MB],
CONVERT(DECIMAL(5,2),100.0 * ld.[Free Space (Bytes)] / ld.[Size (Bytes)]) AS [Free Space(%)], --this is the percentage field
pu.[User] AS [Primary User]
FROM vComputer vc
JOIN vSiteServices ss ON ss.ComputerGuid = vc.[Guid]
JOIN vItem vi ON vi.[Guid] = ss.ResourceTypeGuid
INNER JOIN vHWLogicalDisk ld ON vc.[Guid] = ld._ResourceGuid
LEFT JOIN Inv_AeX_AC_Primary_User pu ON vc.[Guid] = pu._ResourceGuid
WHERE pu.[Month] = DATENAME(mm, GETDATE())
AND LOWER(ld.[Description]) LIKE '%local%'
--AND t1.[Size (Bytes)] > '2000'
--AND CONVERT(DECIMAL(5,2),100.0 * ld.[Free Space (Bytes)] / ld.[Size (Bytes)]) < '10' ---this is the percentage parameter
--AND vi.[Name] = 'MonitorService'  
--AND vi.[Name] = 'PackageService'
--AND vi.[Name] = 'TaskService'
ORDER BY vc.[Name]