→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

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

View solution in original post

3 REPLIES 3
RCorbeil
Honored Sweeper II
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
Engaged Sweeper
This works perfectly. Is the memory portion up top reporting what percentage of memory is being used at the time of scanning then?
RCorbeil
Honored Sweeper II
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.