Reporting on low HDD space for 2 servers

[Samuel1222]

Hey, I'm trying to make an alert / report for two servers in the case that they get low disk space.

The report I have right now doesn't seem to care about the free space left and just shows all of the drives, as well as only showing one server instead of both via IP. Please see below

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblDiskdrives.Caption,
  Ceiling(tblDiskdrives.Freespace / 1024 / 1024 / 1024) As FreeGB,
  Ceiling((tblDiskdrives.Size / 1024 / 1024 / 1024) - (tblDiskdrives.Freespace /
  1024 / 1024 / 1024)) As UsedGB,
  Ceiling(tblDiskdrives.Size / 1024 / 1024 / 1024) As TotalGB,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where (tblAssets.IPAddress Like '192.168.1.31') Or
  (tblAssets.IPAddress Like '192.168.1.34' And Cast(tblDiskdrives.Freespace /
  1024 / 1024 / 1024 As numeric) < 100 And Cast(tblDiskdrives.Size / 1024 /
  1024 / 1024 As numeric) <> 0 And Cast(tblDiskdrives.Size / 1024 / 1024 /
  1024 As numeric) > 10 And tblAssetCustom.State = 1 And
  tblDiskdrives.DriveType = 3)
Order By tblAssets.Domain,
  tblAssets.AssetName,
  tblDiskdrives.Caption

[Samuel1222]

Hell yeah man that's perfect! Thank you

[RCorbeil]

Step 1 would be to adjust the logic in your WHERE clause. Change it from

Where
  (tblAssets.IPAddress Like '192.168.1.31')
  Or ( tblAssets.IPAddress Like '192.168.1.34'
       And Cast(tblDiskdrives.Freespace / 1024 / 1024 / 1024 As numeric) < 100
       And Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) <> 0
       And Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) > 10
       And tblAssetCustom.State = 1
       And tblDiskdrives.DriveType = 3
     )

to

Where
  (tblAssets.IPAddress Like '192.168.1.31' Or tblAssets.IPAddress Like '192.168.1.34')
  And Cast(tblDiskdrives.Freespace / 1024 / 1024 / 1024 As numeric) < 100
  And Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) <> 0
  And Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) > 10
  And tblAssetCustom.State = 1
  And tblDiskdrives.DriveType = 3