‎09-05-2016 01:38 PM
Solved! Go to Solution.
‎10-25-2016 02:03 PM
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
‎11-18-2016 02:50 PM
‎11-14-2016 11:31 AM
‎11-14-2016 10:58 AM
‎11-11-2016 11:33 AM
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
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
‎11-10-2016 03:37 PM
‎11-10-2016 02:12 PM
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
‎11-07-2016 01:11 PM
‎10-25-2016 02:03 PM
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
‎10-21-2016 09:30 AM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now