Ignore Drive Letters in Low Disk Space Report

[SeelyeIT]

Running Version: 6.0.0.22

I am looking for the code to ignore multiple drive letters (like CDRom Drive, USB Drives, etc.) I really only want to see low disk space on Drive C:\

Here is my current code:
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
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) <
5120 And Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) <>
0 And tblComputersystem.Domainrole < 2 And tblDiskdrives.DriveType = 3 And
tblAssetCustom.State = 1

[amelo001]

We ended up using this statement and it worked for us.

tblDiskdrives.Caption LIKE 'C:'

Just add it inside your where statement. See below for reference.

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 As Drive,
Cast(tblDiskdrives.Freespace / 1024 / 1024 / 1024 As numeric) As FreeGB,
Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As TotalSizeGB,
tblDiskdrives.Lastchanged As LastChanged
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where Cast(tblDiskdrives.Freespace / 1024 / 1024 / 1024 As numeric) < 10 And
Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) <> 0 And
tblDiskdrives.Caption LIKE 'C:' And
Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) > 10 And
tblState.Statename = 'Active' And tblDomainroles.Domainrolename In
('Stand-alone workstation', 'Member workstation') And
Case tblDiskdrives.DriveType
When 3 Then 'Local Disk'
End = 'Local Disk'
Order By tblAssets.Domain,
tblAssets.AssetName,
Drive



Just change the C to whatever other letter you may need if you need to get different disk info.

[brendonbeech]

Could you post the full Query to include the solution?

[jblair]

Could you post the entire query?

Thanks.

[SeelyeIT]

Never mind, I figured it out. Worked like a charm! Thank You!

[SeelyeIT]

Where do I add this expression in the code?

Everywhere I try to add it, is states an error on that line.

Thanks.

[Juha_Otava · Accepted solution]

Hi,

Try adding Expression:
tblDiskdrives.Caption
with Criteria:
= 'C:'