Report for disk space with conditional statement.

[nmulder]

Hi,
I am in the process of making a report that will return all servers with 10% or less disk space available. However I am wanting to make a conditional statement in it to weed out servers with larger drives. For instance, I would want all servers with less than 10% disk space if that remaining disk space on that drive is less than 25GB. This would eliminate a drive with 100GB free from a terrabyte drive. I have the 10% working, but need to add the conditional part. Here is what I have so far:

Select Top 1000000 tblDiskdrives.Caption,
  Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) /
  1024 As [GB Free],
  Floor(Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) /
  Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) *
  100) As Freepct,
  Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As
  [Total size],
  tblDiskdrives.Lastchanged As [Last changed],
  tblAssets.AssetID,
  tblAssets.Description,
  tblAssets.AssetName
From tblDiskdrives
  Inner Join tblAssets On tblAssets.AssetID = tblDiskdrives.AssetID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where Floor(Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024
  As numeric) / Cast(Cast(tblDiskdrives.Size As bigint) / 1024 /
  1024 As numeric) * 100) <= 20 And Cast(Cast(tblDiskdrives.Size As bigint) /
  1024 / 1024 As numeric) <> 0 And tblComputersystem.Domainrole > 1 And
  tblDiskdrives.DriveType = '3'
Order By Floor(Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 /
  1024 As numeric) / Cast(Cast(tblDiskdrives.Size As bigint) / 1024 /
  1024 As numeric) * 100),
  Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) / 1024,
  tblDiskdrives.Caption

There is some sql in here that is to not report partitions we have setup as pagefiles also.
Any help would be awesome. Thanks!
Nick

[RCorbeil]

The % Free is the percentage of drive space free at the time of the scan. If you want the percentage of space in use at the of the scan, just subtract the percent free from 100, i.e.

  100 - Cast(tblDiskdrives.Freespace / tblDiskdrives.Size * 100 As
  Numeric(36,2)) As [% Used],

or if you'd prefer to do it the long way,

  Cast((tblDiskdrives.Size - tblDiskdrives.Freespace) / tblDiskdrives.Size *
  100 As Numeric(36,2)) As [% Used],

[nmulder]

This works perfectly. Is the memory portion up top reporting what percentage of memory is being used at the time of scanning then?

[RCorbeil · Accepted solution]

Something like this?

Select Top 1000000
  tblDiskdrives.Caption,
  tblDiskdrives.Freespace,

  Cast(tblDiskdrives.Freespace / tblDiskdrives.Size * 100 As Numeric(36,2)) As [% Free],

-- base 2 gigs (memory is measured in base 2)
  Cast(tblDiskdrives.Freespace / Power(2, 30) As Numeric(36,2)) As [GiB Free],
  Cast(tblDiskdrives.Size      / Power(2, 30) As Numeric(36,2)) As [GiB Capacity],

-- base 10 gigs (hard drives and such are marketed in base 10)
  Cast(tblDiskdrives.Freespace / Power(10, 9) As Numeric(36,2)) As [GB Free],
  Cast(tblDiskdrives.Size      / Power(10, 9) As Numeric(36,2)) As [GB Capacity],

  tblDiskdrives.Lastchanged As [Last changed],
  tblAssets.AssetID,
  tblAssets.Description,
  tblAssets.AssetName

From
  tblDiskdrives
  Inner Join tblAssets On tblAssets.AssetID = tblDiskdrives.AssetID
  Inner Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID

Where
  tblComputersystem.Domainrole > 1
  And tblDiskdrives.DriveType = '3'
-- drive capacity is not zero MebiBytes
  And Floor(tblDiskdrives.Size / Power(2, 20)) <> 0
-- available space <= 20%
  And tblDiskdrives.Freespace / tblDiskdrives.Size * 100 <= 20

-- CHOOSE ONE:

-- EITHER (base 2 gigs free < 25)
  AND Floor(tblDiskdrives.Freespace / Power(2, 30) ) < 25

-- OR (base 10 gigs free < 25)
  AND Floor(tblDiskdrives.Freespace / Power(10, 9) ) < 25


Order By
  [% Free],
  tblDiskdrives.Freespace,
  tblDiskdrives.Caption

I simplified some of the calculations -- there's no need to convert size/capacity to MB/GB when calculating percentage, for example. If I read what you're asking for correctly, you're just wanting to add one or the other of the FreeSpace < 25 statements.