Shows any Window system with drive over 80% full, excluding Backup & Recovery Drives
Select Top (1000000) tsysOS.Image As icon,
tblDiskdrives.Caption As Drive,
Cast(Cast(((Cast(Cast(tblDiskdrives.Size As bigint) -
Cast(tblDiskdrives.Freespace As bigint) As float) / Cast(tblDiskdrives.Size As
float)) * 100) As int) As varchar(10)) + '%' As Used,
Cast(Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 /
1024 As int) As varchar(10)) + ' GB' As Free,
Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
int) As varchar(10)) + ' GB' As Total,
tblAssets.Description,
tblAssets.AssetID,
tblAssets.AssetName,
tblDiskdrives.Lastchanged As lastchanged,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Firstseen As firstseen,
tblAssets.Lastseen As lastseen
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Where tblAssets.AssetID In (Select Distinct tblDiskdrives.AssetID
From tblDiskdrives
Where tblDiskdrives.DriveType = 3 And ((Lower(tblDiskdrives.Volumename) Not
Like '%backup%' And Lower(tblDiskdrives.Volumename) Not Like
'%recovery%') Or tblDiskdrives.Volumename Is Null Or
tblDiskdrives.Volumename = '')) And ((Cast(Cast(tblDiskdrives.Size As
bigint) - Cast(tblDiskdrives.Freespace As bigint) As float) /
Cast(tblDiskdrives.Size As float)) * 100) > 80 And
tblComputersystem.Domainrole < 2 And tblDiskdrives.DriveType = 3 And
tblAssetCustom.State = 1 And ((Lower(tblDiskdrives.Volumename) Not Like
'%backup%' And Lower(tblDiskdrives.Volumename) Not Like '%recovery%') Or
tblDiskdrives.Volumename Is Null Or tblDiskdrives.Volumename = '')
Order By tblAssets.AssetName,
Drive,
Used Desc
