Server drive usage

[PhilM]

Im looking for a report that shows server drive space\usage (so i can automate a report for reference)
Anyone have anything any good?

Windows servers on a domain

[RCorbeil]

Unless you're reporting the values in bytes, there's going to be rounding or truncation done at some point. When it happens is down to your needs/preferences. It's hard enough to get people to agree on whether they're calculating based on powers of 10 (marketing) or powers of 2 (nerds ), never mind the significance of the decimals after you convert the bytes to gigs. (Personally, I've largely given up on that fight. Decades of one voice trying to out-shout an avalanche has proven fruitless. Marketing has won.)

Generally speaking, for my needs, at least, when I'm reporting disk capacities in gigabytes, rounding to the gigabyte is accurate enough. If I need more accuracy, I'd either drop the rounding, as you suggest, or more likely report in megabytes (or kilobytes, or bytes, as appropriate to reflect the level of accuracy needed).

As for the labels on your adjusted output, if you want to be accurate they should read "MiB", not "GB". If you want the results in GiB, you'd need to use Power(2, 30) or Power(1024, 3).

[PhilM]

Cheers

[PhilM]

Cheers

[PhilM]

Cheers

[PhilM]

Cheers

[PhilM]

Cheers

[PhilM]

Cheers

[RCorbeil · Accepted solution]

SELECT Top 1000000
  tsysAssetTypes.AssetTypeIcon10 AS icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysOS.OSname,
  tblDiskdrives.Caption,
  tblDiskdrives.Volumename,
  tblDiskdrives.FileSystem,
  Ceiling(tblDiskdrives.Size / Power(10, 9)) AS [Capacity (GB)],
  Ceiling(tblDiskdrives.Freespace / Power(10, 9)) AS [Free (GB)],
  Ceiling((tblDiskdrives.Size - tblDiskdrives.Freespace) / Power(10, 9)) AS [Used (GB)],
  Cast((tblDiskdrives.Size - tblDiskdrives.Freespace) / tblDiskdrives.Size * 100 AS Decimal(8,2)) AS [Pct Used],
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblDomainRoles.DomainRoleName
FROM
  tblAssets
  INNER JOIN tblAssetCustom    ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes    ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
  INNER JOIN tblDomainRoles    ON tblDomainRoles.DomainRole = tblComputerSystem.DomainRole
  INNER JOIN tblDiskdrives     ON tblDiskdrives.AssetID = tblAssets.AssetID AND tblDiskdrives.DriveType = 3
  INNER JOIN tsysOS            ON tblAssets.OScode = tsysOS.OScode
WHERE
  tblAssetCustom.State = 1
  AND tblComputersystem.Domainrole > 1
ORDER BY
  tblAssets.AssetUnique,
  tblDiskdrives.Caption

[nems]

RC62N wrote:

SELECT Top 1000000
  Ceiling(tblDiskdrives.Size / Power(10, 9)) AS [Capacity (GB)],
  Ceiling(tblDiskdrives.Freespace / Power(10, 9)) AS [Free (GB)],
  Ceiling((tblDiskdrives.Size - tblDiskdrives.Freespace) / Power(10, 9)) AS [Used (GB)],
  Cast((tblDiskdrives.Size - tblDiskdrives.Freespace) / tblDiskdrives.Size * 100 AS Decimal(8,2)) AS [Pct Used],

Shouldnt this be:

  tblDiskdrives.Size / Power(1024, 2) AS [Capacity (GB)],
  tblDiskdrives.Freespace / Power(1024, 2) AS [Free (GB)],
  (tblDiskdrives.Size - tblDiskdrives.Freespace) / Power(1024, 2) AS [Used (GB)],
  Cast((tblDiskdrives.Size - tblDiskdrives.Freespace) / tblDiskdrives.Size * 100 AS Decimal(8,2)) AS [Pct Used],

I think this would be more correct, calculating the diskspace and not rounding with ceiling, or am i mistaken?