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