Workstations - Less than 1GB free disk space, excluding HP and Lenovo reserved system drives

[cmredkey]

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tsysOS.OSname,
  tblAssets.Description,
  tblDiskdrives.Caption,
  Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As
  free,
  Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As
  [total size],
  tblDiskdrives.Lastchanged As [last changed],
  tsysOS.Image As icon,
  tblDiskdrives.Volumename
From tblAssets
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) <
  1024 And Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) <>
  0 And tblDiskdrives.Volumename != 'HP_RECOVERY' And
  tblDiskdrives.Volumename != 'HP_TOOLS' And tblDiskdrives.Volumename !=
  'System Reserved' And tblDiskdrives.Volumename != 'Lenovo_Recovery' And
  tblComputersystem.Domainrole < 2 And tblDiskdrives.DriveType = 3 And
  tblAssetCustom.State = 1

[njordur]

Added Chinese for System Reserved and put it in an not in clause for better readability.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tsysOS.OSname,
  tblAssets.Description,
  tblDiskdrives.Caption,
  Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As
  free,
  Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As
  [total size],
  tblDiskdrives.Lastchanged As [last changed],
  tsysOS.Image As icon,
  tblDiskdrives.Volumename
From tblAssets
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) <
  1024 And Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) <>
  0 And tblComputersystem.Domainrole < 2
  And tblDiskdrives.DriveType = 3 And tblAssetCustom.State = 1 and
  tblDiskdrives.Volumename not in ('System Reserved','HP_RECOVERY','HP_TOOLS','Lenovo_Recovery',N'系统保留')