cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mhammond
Champion Sweeper
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
2 REPLIES 2
CyberCitizen
Honored Sweeper
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
Champion Sweeper III
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

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