Windows: Workstations less than 1 GB free HD (Built-in)

[Daniel_B]

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

[brent_c76]

Much Thanks! that seems to show exactly what needs to be seen... THANKS!!!

[fjca]

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]

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]

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