Server List with Hard drive Space Used Report

[wolfpack]

I am trying, without much success, to run a simple report that only has the following results generated:
1) Server list by Hostname/Computername
2) Hard drive letters (optional)
3) Total Hard drive space Free (all drives)
4) Total Hard drive space Used (all drives)
5) Total Hard drive space (all drives)

I have been through nearly every forum discussion, and have tried many of the reports that have been posted, but none of them can give me anything close to what I need. Every one I have tried gives me a whole lot of garbage that I don't need, and nothing that I do need. Any help on this would be greatly appreciated.

Thanks!

[wolfpack]

Correct. That one turned out pretty cool also. Thanks!

[RCorbeil]

Oh. By "all drives" you meant "for each drive". That makes more sense.

Ah well, I'll leave the code. You never know; someday somebody might want to produce something similar. I used it as an opportunity to learn how to sort the result of a union.

[RCorbeil]

I'm not sure why you want the total of all drives' space, but try this:

Select Top 1000000
  ServerDrives.icon,
  ServerDrives.AssetID,
  ServerDrives.AssetUnique,
  ServerDrives.Domain,
  ServerDrives.Description,
  ServerDrives.Lastseen,
  ServerDrives.Drive,
  ServerDrives.FileSystem,
  ServerDrives.[Capacity GB],
  ServerDrives.[Used GB],
  ServerDrives.[Free GB]
From
  ( Select Top 1000000
      tsysOS.Image As icon,
      tblAssets.AssetID,
      tblAssets.AssetUnique,
      tblAssets.Domain,
      tblAssets.Description,
      tblAssets.Lastseen,
      tblDiskdrives.Caption As Drive,
      tblDiskdrives.Caption As SortDrive,
      tblDiskdrives.FileSystem,
      Ceiling(tblDiskdrives.Size / 1000 / 1000 / 1000) As [Capacity GB],
      Ceiling((tblDiskdrives.Size - tblDiskdrives.Freespace) / 1000 / 1000 / 1000) As [Used GB],
      Ceiling(tblDiskdrives.Freespace / 1000 / 1000 / 1000) As [Free GB]
    From
      tblAssets
      Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
      Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
      Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
      Inner Join tblDiskdrives On tblDiskdrives.AssetID = tblAssets.AssetID
    Where
      tblAssetCustom.State = 1
      And tblComputersystem.Domainrole >= 2
      And tblDiskdrives.DriveType = 3

    Union All

    Select Top 1000000
      tsysOS.Image As icon,
      tblAssets.AssetID,
      tblAssets.AssetUnique,
      tblAssets.Domain,
      tblAssets.Description,
      tblAssets.Lastseen,
      'TOTAL' As Drive,
      'zzz' As SortDrive,
      Null,
      Ceiling( ( Select Sum(AllDrives.Size)
                 From tblDiskdrives As AllDrives
                 Where AllDrives.AssetID = tblAssets.AssetID
                   And AllDrives.DriveType = 3) / 1000 / 1000 / 1000) As [Capacity GB],
      Ceiling( ( Select Sum(AllDrives.Size - AllDrives.Freespace)
                 From tblDiskdrives As AllDrives
                 Where AllDrives.AssetID = tblAssets.AssetID
                   And AllDrives.DriveType = 3) / 1000 / 1000 / 1000) As [Used GB],
      Ceiling ( (Select Sum(AllDrives.Freespace)
                 From tblDiskdrives As AllDrives
                 Where AllDrives.AssetID = tblAssets.AssetID
                   And AllDrives.DriveType = 3) / 1000 / 1000 / 1000) As [Free GB]
    From
      tblAssets
      Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
      Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
      Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
    Where
      tblAssetCustom.State = 1
      And tblComputersystem.Domainrole >= 2
  ) As ServerDrives
  Order By
    ServerDrives.AssetUnique,
    ServerDrives.SortDrive

It'll produce a list of all the drives in drive letter-order followed by the drives' totals for each server.

[wolfpack]

That worked!!! Thank You! Thank You! Thank You!

[Hemoco · Accepted solution]

Please use the report below for the information you are after.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblDiskdrives.Caption,
Ceiling(tblDiskdrives.Freespace / 1024 / 1024) As FreeMB,
Ceiling((tblDiskdrives.Size / 1024 / 1024) - (tblDiskdrives.Freespace / 1024 /
1024)) As UsedMB,
Ceiling(tblDiskdrives.Size / 1024 / 1024) As TotalMB,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1 And
tblDiskdrives.DriveType = 3
Order By tblAssets.Domain,
tblAssets.AssetName,
tblDiskdrives.Caption