→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎05-16-2013 09:34 AM
Solved! Go to Solution.
‎05-16-2013 09:08 PM
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblDiskdrives.Caption,
Ceiling(tblDiskdrives.Freespace / 1024 / 1024) As FreeMB,
Ceiling((tblDiskdrives.Size / 1024 / 1024) - (tblDiskdrives.Freespace / 1024 /
1024)) As UsedMB,
Ceiling(tblDiskdrives.Size / 1024 / 1024) As TotalMB,
Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size When 0 Then 1
Else tblDiskdrives.Size End) * 100) As FreePCT,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size When 0 Then 1
Else tblDiskdrives.Size
End) * 100) < 5 And tblAssetCustom.State = 1 And
tblComputersystem.Domainrole > 1 And tblDiskdrives.DriveType = 3
Order By tblAssets.Domain,
tblAssets.AssetName,
tblDiskdrives.Caption
‎05-17-2013 08:46 AM
‎05-16-2013 09:41 PM
Select Top 1000000
tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Description,
tblAssets.Lastseen,
tblDiskDrives.Caption AS Drive,
tblDiskDrives.FileSystem,
Ceiling(tblDiskDrives.FreeSpace/1000/1000/1000) AS [Available GB],
Ceiling(tblDiskDrives.Size/1000/1000/1000) AS [Capacity GB],
Cast(Round(tblDiskDrives.Freespace/tblDiskDrives.Size*100,2) AS Numeric(18,2)) AS [% Free]
From
tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblDiskDrives On tblDiskDrives.AssetID = tblAssets.AssetID
Where
tblAssetCustom.State = 1
And tblComputersystem.Domainrole >= 2
and tblDiskDrives.DriveType = 3
And tblDiskDrives.Freespace/tblDiskDrives.Size < 0.05
Order By
tblAssets.AssetName,
tblDiskDrives.Caption
‎05-16-2013 09:08 PM
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblDiskdrives.Caption,
Ceiling(tblDiskdrives.Freespace / 1024 / 1024) As FreeMB,
Ceiling((tblDiskdrives.Size / 1024 / 1024) - (tblDiskdrives.Freespace / 1024 /
1024)) As UsedMB,
Ceiling(tblDiskdrives.Size / 1024 / 1024) As TotalMB,
Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size When 0 Then 1
Else tblDiskdrives.Size End) * 100) As FreePCT,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size When 0 Then 1
Else tblDiskdrives.Size
End) * 100) < 5 And tblAssetCustom.State = 1 And
tblComputersystem.Domainrole > 1 And tblDiskdrives.DriveType = 3
Order By tblAssets.Domain,
tblAssets.AssetName,
tblDiskdrives.Caption
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now