→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
ellis_tsui
Engaged Sweeper
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
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Try tblDiskdrives.Volumename.

View solution in original post

4 REPLIES 4
ellis_tsui
Engaged Sweeper
Thanks RC62N!
It works!
RCorbeil
Honored Sweeper II
Try tblDiskdrives.Volumename.
ellis_tsui
Engaged Sweeper
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
Champion Sweeper III
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.