Example Situation
You have multiple package servers and need to monitor and alert when the free disk space on any of these servers is below a specified percentage. In the example below, we will generate an email notification to a select group of administrators with a list of package servers where the free space is less than 10%. In this example, we have sites with desktop computers serving as package servers and other sites with servers serving as package servers, and they all have set naming conventions. The desktops all start their names with “Desk”, and the Servers all start their names with “Serv.” Additionally, the desktops only have a C drive, and the servers are configured to use non-system drives and are either set as E or F.
SELECT Distinct
vc.[Name] AS SiteServer,
ld.[Device ID] AS Drive,
(ld.[Size (Bytes)])/1024/1024/1024 AS [Disk Size GB],
(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
FROM vComputer vc
JOIN vSiteServices ss ON ss.ComputerGuid = vc.[Guid]
INNER JOIN vHWLogicalDisk ld ON vc.[Guid] = ld._ResourceGuid
WHERE
ld.[Size (Bytes)] > 0 and
LOWER(ld.[Description]) LIKE '%local%'
AND CONVERT(DECIMAL(5,2),100.0 * ld.[Free Space (Bytes)] / ld.[Size (Bytes)]) < '10' ---this is the percentage parameter
ORDER BY vc.[Name]