→ 🚀What's New? Explore Lansweeper's Fall 2024 Updates! Fall Launch Blog !

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Daniel_B
Lansweeper Alumni
Old name: Disk: Workstations less than 1 GB free HD (Built-in)

The report below lists Windows workstations with less than 1 GB free on a hard-disk

The report will only list assets that meet all of the following criteria:
  • The asset state is set to "active".
  • The asset has been successfully scanned at least once.
  • The asset is a Windows computer (not a server).
  • The asset has a hard-disk with less than 1 GB free space.


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblDiskdrives.Caption,
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As decimal(18,2))
As FreeGB,
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As
TotalSizeGB
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As
decimal(18,2)) < 1 And Cast(Cast(tblDiskdrives.Size As bigint) / 1024 /
1024 As numeric) <> 0 And tblComputersystem.Domainrole <= 1 And (Case
When tblDiskdrives.DriveType = 3 Then 'Local Disk' Else ''
End) = 'Local Disk' And tblState.Statename = 'Active'
Order By tblAssets.Domain,
tblAssets.AssetName
4 REPLIES 4
brent_c76
Engaged Sweeper
Much Thanks! that seems to show exactly what needs to be seen... THANKS!!!
fjca
Champion Sweeper II
Hi, you can use my version, we have it on a weekly report going to the
Helpdesk trouble ticket queue.


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname,
tblAssets.Description,
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],
tblAssets.IPAddress,
tblDiskdrives.Lastchanged As [last changed],
tsysOS.Image As icon
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
Where tblDiskdrives.Caption Like '%C:%' And 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 < 2
And tblDiskdrives.DriveType = 3 And tblAssetCustom.State = 1
brent_c76
Engaged Sweeper
OK, Question???

I have several computers showing up that have the D, E, F and sometimes G: Partitions. these partitions or drives are not OS Related and may be used for end user temporary file storage. How Can I omit D, E, F, G, drives? I've come across a query that shows how to get rid of E, F Drives, but the query is not correct, i have tried to massage it, but I am not a SQL Expert, and was not having any luck.

BTW - Using SQL DB, and the latest version of LANSweeper (v. 5334)
tylerh
Engaged Sweeper
This report should be changed to the following because it picks up the "System Reserved" partition that windows creates when installed causing assets to show in the reports that actually have plenty of space free. The system reserved partition is not visible to users so they can't save data there. This is very minor update to the report but it makes quite a difference when our service desk is using the report to be proactive about reaching out to users with low space.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname,
tblAssets.Description,
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],
tblDiskdrives.Lastchanged As [last changed],
tsysOS.Image As icon,
tblDiskdrives.Volumename
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
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 tblDiskdrives.Volumename != 'system reserved' And
tblComputersystem.Domainrole < 2 And tblDiskdrives.DriveType = 3 And
tblAssetCustom.State = 1


Info on System Reserved Partitions - Technet Article

Thanks,
Tyler Helder
IT Infrastructure Engineer
Tria Beauty