View HDD Free Space with 2 Decimal Places

[brianraley]

I'd like to be able to view the Free in GB field on the 'Disk: HDD, Size, Type and Pct' report with two digits of usable data - eg 1/3GB = .33

Here's what I've updated in the code to get the two decimals but all I see are two preceding zeros and not actual usable data.

Select Top 1000000 upgrade_tblComputers.ComputerUnique,
upgrade_tblComputers.Computername As Computer,
upgrade_tblComputersystem.Model As [Computer Model],
upgrade_tblDiskDrives.Caption As Drive,
Cast(Cast(upgrade_tblDiskDrives.Size As BigInt) / 1024 / 1024 / 1024
As Numeric) As [Total size (GB)],
Cast(Cast(upgrade_tblDiskDrives.Freespace As BigInt) / 1024 / 1024 /
1024 As Numeric(8,2)) As [Free in GB],
Cast((Cast(upgrade_tblDiskDrives.Size As BigInt) -
Cast(upgrade_tblDiskDrives.Freespace As BigInt)) / 1024 / 1024 /
1024 As Numeric) As [Used Space (GB)],
upgrade_tblDiskDrives.Volumeserialnumber As [Drive Volume Number],
upgrade_tblFloppy.Model As [HDD Model],
upgrade_tblDiskDrives.Lastchanged As [Last changed],
Cast(Cast(Cast(upgrade_tblDiskDrives.Freespace As BigInt) As Numeric) /
Cast(Cast(upgrade_tblDiskDrives.Size As BigInt) As Numeric) * 100 As
BigInt) As [Used PCT]
From upgrade_tblComputers
Inner Join upgrade_tblDiskDrives On upgrade_tblComputers.Computername =
upgrade_tblDiskDrives.Computername
Inner Join upgrade_tblOperatingsystem On upgrade_tblComputers.Computername =
upgrade_tblOperatingsystem.Computername
Inner Join upgrade_tblComputersystem On upgrade_tblComputers.Computername =
upgrade_tblComputersystem.Computername
Inner Join upgrade_tblFloppy On upgrade_tblComputers.Computername =
upgrade_tblFloppy.Computername
Where upgrade_tblComputers.Computername Not Like 'COMPUTER' And
upgrade_tblDiskDrives.DriveType = '3'
Order By Computer,
Drive,
[HDD Model]

[Hemoco]

Thanks for posting, RC62N! Kudos to you.

[brianraley]

Thank you so very much RC62N,

This not only gave me exactly what I was looking for in the preceding 2 decimal places but also cleaned up the multiple duplicates of the same drives that I was looking at before. This report is going to make the perfect export to track daily disk trending.

[RCorbeil · Accepted solution]

Try this as a starting point. I've updated the table references to reflect LANSweeper 5 rather than use the upgrade_ views.

Point: I've removed the link to tblFloppy. The link you had established wasn't logically sound. (You can establish a clean link from tblAssets to tblDiskDrives and you can establish a clean link from tblAssets to tblFloppy, but you don't have a clean link between tblDiskDrive and tblFloppy.)

Point: I've included both decimal and binary calculations. Hard drives are advertised and sold based on decimal, RAM is measured based on decimal. Real nerds resent the hard drive marketers who redefined binary as decimal in order to make their drives sound bigger, but reality is that SI has gone alone with them and defined Gibi as the binary version of decimal Giga.

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 AS icon,
  tblAssetCustom.Model,
  tblDiskdrives.Caption,
  tblDiskdrives.Description,
  tblDiskdrives.DriveType,
  tblDiskdrives.FileSystem,

  -- decimal-based sizes, the way hard drives are advertised (gigabytes)
  Cast(tblDiskdrives.Size                             / Power(10, 9) AS Numeric(8,2)) AS [Capacity (GB)],
  Cast(tblDiskdrives.Freespace                        / Power(10, 9) AS Numeric(8,2)) AS [Free (GB)],
  Cast((tblDiskdrives.Size - tblDiskdrives.Freespace) / Power(10, 9) AS Numeric(8,2)) AS [Used (GB)],

  -- binary-based sizes, the way RAM is measured and advertised (giga-binary-bytes)
  Cast(tblDiskdrives.Size                             / Power(2, 30) AS Numeric(8,2)) AS [Capacity (GiB)],
  Cast(tblDiskdrives.Freespace                        / Power(2, 30) AS Numeric(8,2)) AS [Free (GiB)],
  Cast((tblDiskdrives.Size - tblDiskdrives.Freespace) / Power(2, 30) AS Numeric(8,2)) AS [Used (GiB)],

  Cast((tblDiskdrives.Size - tblDiskdrives.Freespace) / tblDiskdrives.Size * 100 AS Numeric(8,2)) AS [Used %],
  tblDiskdrives.Volumename,
  tblDiskdrives.Volumeserialnumber
FROM
  tblAssets
  Inner Join tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblDiskdrives ON tblAssets.AssetID = tblDiskdrives.AssetID
WHERE
  tblDiskdrives.DriveType = 3
  AND tblAssetCustom.State = 1

You should be able to use that as a launch-point and refine it to your needs.