Modify "....less than 1GB free HD..." Report

[mhammond]

How would I go about modifying the built-in report for Servers and Workstations, the "Disk: ....less than 1 GB HD" report, to exclude "RECOVERY" drive letters and other non-boot drives, regardless of drive letter?

Working on proactive maintenance, I am trying to find trends in reasons why PCs on the network are experiencing similar issues. One would be the HDDs getting full. The report runs, but picks up non-essential drives which throws off my numbers. Out of the 15 that showed up on my report, 2 actually had filled their respective boot drives (in this case the C: drive). All the others that were showing up in the report were including Dell RECOVERY drives or secondary hard drives being under 1GB of free space.

I don't know how to exclude those situations.

Below is the default code for the report. How would I put in an exception, or would I need to write a whole new report?

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
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],
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen
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
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) <
1024 And Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) >
1000 And tblComputersystem.Domainrole < 2 And tblDiskdrives.DriveType = 3 And
tblAssetCustom.State = 1

[CyberCitizen]

We have guys that have large drawing files (construction company) so we set ours to 5GB of space and limited to the C Drive only via the below report.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblDiskdrives.Caption,
  Cast(Cast(tblDiskdrives.Freespace As bigint) / 5120 / 5120 As numeric) As
  free,
  Cast(Cast(tblDiskdrives.Size As bigint) / 5120 / 5120 As numeric) As
  [total size],
  tblDiskdrives.Lastchanged As [last changed],
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tsysOS.OSname As OS,
  tblAssets.SP As SP,
  tblAssets.Firstseen,
  tblAssets.Lastseen
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
  Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
    And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where tblDiskdrives.Caption Like '%C%' And Cast(Cast(tblDiskdrives.Freespace As
  bigint) / 5120 / 5120 As numeric) < 1024 And Cast(Cast(tblDiskdrives.Size As
  bigint) / 5120 / 5120 As numeric) <> 0 And Cast(Cast(tblDiskdrives.Size As
  bigint) / 5120 / 5120 As numeric) > 1500 And tblComputersystem.Domainrole < 2
  And tblDiskdrives.DriveType = 3 And tblAssetCustom.State = 1

[rader]

Still learning but I took your script and added the tblDiskdrives.Volumename to it and added the criteria "Not Like '%Recovery%' and it seems to work. By bumping up the free space criteria to over 100GB (<100024) I could see the "Recovery" volumes drop off the report.

Here's your code with the changes I made. Hope this helps.

=========================================
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblDiskdrives.Caption,
tblDiskdrives.Volumename,
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],
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen
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
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where tblDiskdrives.Volumename Not Like '%Recovery%' And
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) <
100024 And Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) >
1000 And tblComputersystem.Domainrole < 2 And tblDiskdrives.DriveType = 3 And
tblAssetCustom.State = 1

==========================================