cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
stiwa
Champion Sweeper
Hi,

i need a report, where i can see all assets with a SSD and less than 10% free disk space.
Anyone capable of doing this?
Thanks.
1 ACCEPTED SOLUTION
Bruce_B
Lansweeper Alumni
Getting a can't divide by zero error would indicate that either the freespace value or the size value for one of your disk drives is 0. We've adjusted the report to exclude these values.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblDiskdrives.Volumename,
Ceiling(tblDiskdrives.Size / 1024 / 1024 / 1024) As [Max size in GB],
Ceiling(tblDiskdrives.Freespace / 1024 / 1024 / 1024) As [Free Space in GB],
tblDiskdrives.FileSystem,
tblDiskdrives.Description,
tblDiskdrives.Caption,
tblDiskdrives.Compressed,
(tblDiskdrives.Freespace / tblDiskdrives.Size) * 100 As [Percent Free Space],
tblFloppy.Model,
tblFloppy.Name
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where (tblDiskdrives.Freespace / tblDiskdrives.Size) * 100 < 10 And
(tblFloppy.Model Like '%SSD%' Or tblFloppy.Model Like '%Solid State%') And
tblAssetCustom.State = 1 And tblDiskdrives.Freespace > 0 And
tblDiskdrives.Size > 0
Order By tblAssets.AssetName,
tblDiskdrives.Caption

View solution in original post

11 REPLIES 11
Bruce_B
Lansweeper Alumni
This discrepancy happens because the report has to use tblFloppy to filter for SSD's, but uses tblDiskdrives to filter for free space. tblDiskdrives and tblFloppy are only related to eachother per assetID, not per specific hard drive. This means that the report can only filter on assetID and the associated partitions for this assetID, but not for specific partitions for the same asset. This means that unfortunately this report can not be improved to provide 100% accurate information under all circumstances and should be used with caution.
Niko0
Engaged Sweeper III
This report works fine for me, i'm not sure why your having this problem.

Might be a lansweeper related issue.

I think this happens when two disks use the same partitions i am not sure though.
stiwa
Champion Sweeper
Thanks for trying, but both produces the same errors as before (division through null and shows assets where the SSD has more than 10% free space and a non-SSD < 10%).
Niko0
Engaged Sweeper III
This one will display all SSD Drives with below 10% Space in GB.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblFloppy.Model,
tblAssets.Lasttried,
tDiskdrives.freespace,
tDiskdrives.Size,
tDiskdrives.Caption
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Inner Join (Select tblDiskdrives.AssetID,
tblDiskdrives.Caption,
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 /
1024 As Numeric) As freespace,
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As Numeric) As
Size
From tblDiskdrives
Where tblDiskdrives.DriveType = 3) tDiskdrives On tblAssets.AssetID =
tDiskdrives.AssetID
Where (tblFloppy.Model Like '%SSD%' Or tblFloppy.Model Like '%Solid State%') And
tDiskdrives.freespace / tDiskdrives.Size * 100 < 10 And tblAssetCustom.State =
1



Better Version, Added Some more SSD Drive types so it detects all SSD Drives.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblFloppy.Model,
tblAssets.Lasttried,
tDiskdrives.freespace As [Free Space in GB],
tDiskdrives.Size As [Max size in GB],
tDiskdrives.Caption,
Case When (tblFloppy.Model Like '%SSD%' Or
tblFloppy.Model Like '%Solid State%' Or tblFloppy.Model Like '%mSS%' Or
tblFloppy.Model Like '%mSATA%') Then 'Yes' Else 'Not SSD' End As SSD,
Case When (tDiskdrives.freespace / tDiskdrives.Size * 100 < 10) Then 'Yes'
Else 'No' End As [Under 10% FreeSpace]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Inner Join (Select tblDiskdrives.AssetID,
tblDiskdrives.Caption,
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 /
1024 As Numeric) As freespace,
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As Numeric) As
Size
From tblDiskdrives
Where tblDiskdrives.DriveType = 3) tDiskdrives On tblAssets.AssetID =
tDiskdrives.AssetID
Where (tblFloppy.Model Like '%Solid State%' Or tblFloppy.Model Like '%SSD%' Or
tblFloppy.Model Like '%mSS%' Or tblFloppy.Model Like '%mSATA%') And
tDiskdrives.freespace / tDiskdrives.Size * 100 < 10 And tblAssetCustom.State =
1
Order By tDiskdrives.freespace
stiwa
Champion Sweeper
I don't want to limit the report to PHYSICALDRIVE0, as i also want to know if a SSD used as second/third/.. drive is under 10% free space.
Niko0
Engaged Sweeper III
@Stiwa Maybe try this

Select Top 1000000 tblAssets.AssetID,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetName,
tblAssets.Lastseen,
Ceiling(tblDiskdrives.Size / 1024 / 1024 / 1024) As [Max size in GB],
Ceiling(tblDiskdrives.Freespace / 1024 / 1024 / 1024) As [Free Space in GB],
tblDiskdrives.Caption,
tblFloppy.Model,
tblFloppy.Name
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where (tblFloppy.Model Like '%SSD%' Or tblFloppy.Model Like '%Solid State%') And
tblFloppy.Name Like '%PHYSICALDRIVE0' And (tblDiskdrives.Freespace /
tblDiskdrives.Size) * 100 < 10 And tblAssetCustom.State = 1 And
tblDiskdrives.Freespace > 0 And tblDiskdrives.Size > 0
Order By tblDiskdrives.Caption
stiwa
Champion Sweeper
Thanks for the update, but there's something wrong:
The report also shows assets, where a non-SSD disk in the same computer has less than 10% free but the SSD not (see screenshots).
Bruce_B
Lansweeper Alumni
Getting a can't divide by zero error would indicate that either the freespace value or the size value for one of your disk drives is 0. We've adjusted the report to exclude these values.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblDiskdrives.Volumename,
Ceiling(tblDiskdrives.Size / 1024 / 1024 / 1024) As [Max size in GB],
Ceiling(tblDiskdrives.Freespace / 1024 / 1024 / 1024) As [Free Space in GB],
tblDiskdrives.FileSystem,
tblDiskdrives.Description,
tblDiskdrives.Caption,
tblDiskdrives.Compressed,
(tblDiskdrives.Freespace / tblDiskdrives.Size) * 100 As [Percent Free Space],
tblFloppy.Model,
tblFloppy.Name
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where (tblDiskdrives.Freespace / tblDiskdrives.Size) * 100 < 10 And
(tblFloppy.Model Like '%SSD%' Or tblFloppy.Model Like '%Solid State%') And
tblAssetCustom.State = 1 And tblDiskdrives.Freespace > 0 And
tblDiskdrives.Size > 0
Order By tblAssets.AssetName,
tblDiskdrives.Caption
stiwa
Champion Sweeper
*Push*