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

View solution in original post

9 REPLIES 9
RCorbeil
Honored Sweeper II
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
Engaged Sweeper II
Cheers
PhilM
Engaged Sweeper II
Cheers
PhilM
Engaged Sweeper II
Cheers
PhilM
Engaged Sweeper II
Cheers
PhilM
Engaged Sweeper II
Cheers
PhilM
Engaged Sweeper II
Cheers
RCorbeil
Honored Sweeper II
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
Engaged Sweeper
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?