Assets with SSD and less than 10% free disk space

[stiwa]

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.

[Bruce_B]

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]

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]

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]

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]

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]

@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]

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 · Accepted solution]

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]

*Push*