
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎08-19-2020 11:11 AM
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!
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
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎08-20-2020 03:58 PM
Try tblDiskdrives.Volumename.
4 REPLIES 4

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎08-20-2020 04:41 PM
Thanks RC62N!
It works!
It works!
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎08-20-2020 03:58 PM
Try tblDiskdrives.Volumename.

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎08-20-2020 09:28 AM
Thanks Brandon.
Ya I have tried it before, but the following error was shown:
The multi-part identifier "tblvolume.label" could not be bound.
Ya I have tried it before, but the following error was shown:
The multi-part identifier "tblvolume.label" could not be bound.

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎08-19-2020 10:06 PM
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.
