Servers with low disk space

[AZHockeyNut]

Thanks to my DBA I now have a PCT free column so I don't need to do the math!
***Begin****


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblDiskdrives.Caption,
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As
free,
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As
[total size],
CAST(CAST(ROUND(((CAST(CAST(tblDiskdrives.Freespace AS BIGINT) / 1024 / 1024 AS NUMERIC) / CAST(CAST(tblDiskdrives.Size AS BIGINT) / 1024 / 1024 AS NUMERIC)) * 100), 2) AS NUMERIC(6, 2)) AS VARCHAR(50)) + '%' AS PercentFree,
tblDiskdrives.Lastchanged As [last changed],
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.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 Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) <
1024 And Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) <>
0 And tblComputersystem.Domainrole > 1 And tblDiskdrives.DriveType = 3 And
tblAssetCustom.State = 1


*****END****
enjoy!!