→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
SeelyeIT
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Juha_Otava
Engaged Sweeper III
Hi,

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

View solution in original post

6 REPLIES 6
amelo001
Engaged Sweeper
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
Engaged Sweeper
Could you post the full Query to include the solution?
jblair
Engaged Sweeper
Could you post the entire query?

Thanks.
SeelyeIT
Engaged Sweeper
Never mind, I figured it out. Worked like a charm! Thank You!
SeelyeIT
Engaged Sweeper
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
Engaged Sweeper III
Hi,

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