Disk space report with volume name

[ellis_tsui]

Hi,

I have prepared a diskspace report as follows. Now I want to add volume name of the disks to the report as well, could someone advise how can it be done? Thanks!

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblDiskdrives.Caption As Drive,
  Cast(tblDiskdrives.Freespace / 1024 / 1024 / 1024 As numeric) As FreeGB,
  Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As TotalSizeGB,
  Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size
    When 0 Then 1
    Else tblDiskdrives.Size
  End) * 100) As [%SpaceLeft],
  Case
    When Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size
        When 0 Then 1
        Else tblDiskdrives.Size
      End) * 100) < 10 Then '#f7caca'
    When Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size
        When 0 Then 1
        Else tblDiskdrives.Size
      End) * 100) < 30 Then '#f7f0ca'
  End As backgroundcolor,
  tblDiskdrives.Lastchanged As LastChanged
From tblAssets
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (tblAssets.AssetName Like 'hmhsfs%' Or tblAssets.AssetName Like 'hmhsex%')
  And Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) <> 0 And
  tblState.Statename = 'Active' And Case tblDiskdrives.DriveType
    When 3 Then 'Local Disk'
  End = 'Local Disk'
Order By [%SpaceLeft],
  tblAssets.Domain,
  tblAssets.AssetName,
  Drive

[ellis_tsui]

Thanks RC62N!
It works!

[RCorbeil · Accepted solution]

Try tblDiskdrives.Volumename.

[ellis_tsui]

Thanks Brandon.
Ya I have tried it before, but the following error was shown:
The multi-part identifier "tblvolume.label" could not be bound.

[brandon_jones]

You should be able to add the field tblvolume.label. Just be sure you place a comma at the end of the line if it is not the last field.