→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
archaic0
Engaged Sweeper II
I would like to exclude certain drives on certain workstations from the low drive space report.

For example, I would like to exclude the E drive on workstation1 from the report. I know I can add Not Like '%workstation1%' to filter out the entire workstation, but how do I only exclude one drive from that workstation?
1 ACCEPTED SOLUTION
archaic0
Engaged Sweeper II
I was able to play around and get this approach to work where I create a concatenated string to compare to the machines I want to filter out. I'll have to try your approach as well to see if it works out the same way, but this is working for me currently.





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 tblComputersystem.Domainrole < 2 And
tblDiskdrives.DriveType = 3 And tblAssetCustom.State = 1 And

tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%sunflower\1IP-01\1~E:%' And tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%sunflower\1IP-03\1~E:%' And
tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%sunflower\1IP-08\1~E:%' And tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%sunflower\1IP-12\1~E:%' And
tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%sunflower\1IP-13\1~E:%' And tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%sunflower\1IP-14\1~E:%' And
tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%sunflower\1IP-16\1~E:%' And tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%SUNFLOWER\TOPEK-TEMP\1~E:%' And
tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%SUNFLOWER\JC-CLD01\1~E:%' And tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%sunflower\1TRUST-THIN\1~E:%' And
tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%SUNFLOWER\1IS-NETADMIN6\1~D:%'

View solution in original post

6 REPLIES 6
archaic0
Engaged Sweeper II
Thanks for the input. Definitely appreciate having the alternatives because you never know when the situation will fit a bit differently.
RCorbeil
Honored Sweeper II
If it works for you then run with it. If you're open to trying alternatives, you should also be able to set that machine+drive filtering up something like this
  -- IF drive is E:
-- AND computer name matches (list)
-- THEN ignore it
NOT ( (tblDiskdrives.Caption LIKE '%E%')
AND ( tblAssets.AssetUnique LIKE '%1IP-01%'
OR tblAssets.AssetUnique LIKE '%1IP-03%'
OR tblAssets.AssetUnique LIKE '%1IP-08%'
OR tblAssets.AssetUnique LIKE '%1IP-12%'
OR tblAssets.AssetUnique LIKE '%1IP-13%'
OR tblAssets.AssetUnique LIKE '%1IP-14%'
OR tblAssets.AssetUnique LIKE '%1IP-16%'
OR tblAssets.AssetUnique LIKE '%TOPEK-TEMP%'
OR tblAssets.AssetUnique LIKE '%JC-CLD01%'
OR tblAssets.AssetUnique LIKE '%1TRUST-THIN%'
)
)

-- IF drive is 😧
-- AND computer name contains 1IS-NETADMIN6
-- THEN ignore it
AND NOT (tblAssets.AssetUnique LIKE '%1IS-NETADMIN6%' AND tblDiskdrives.Caption LIKE '%D%')

Six of one, half-a-dozen of the other and all that. Whatever makes the most sense to you and works, works. 🙂
archaic0
Engaged Sweeper II
I was able to play around and get this approach to work where I create a concatenated string to compare to the machines I want to filter out. I'll have to try your approach as well to see if it works out the same way, but this is working for me currently.





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 tblComputersystem.Domainrole < 2 And
tblDiskdrives.DriveType = 3 And tblAssetCustom.State = 1 And

tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%sunflower\1IP-01\1~E:%' And tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%sunflower\1IP-03\1~E:%' And
tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%sunflower\1IP-08\1~E:%' And tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%sunflower\1IP-12\1~E:%' And
tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%sunflower\1IP-13\1~E:%' And tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%sunflower\1IP-14\1~E:%' And
tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%sunflower\1IP-16\1~E:%' And tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%SUNFLOWER\TOPEK-TEMP\1~E:%' And
tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%SUNFLOWER\JC-CLD01\1~E:%' And tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%sunflower\1TRUST-THIN\1~E:%' And
tblAssets.AssetUnique + '~' + tblDiskdrives.Caption Not Like '%SUNFLOWER\1IS-NETADMIN6\1~D:%'

RCorbeil
Honored Sweeper II
Try reversing the approach you're using to try to match the one machine+drive. Instead of doing (NOT machine AND NOT drive), uniquely identify the machine/drive you want filter and NOT that, i.e. NOT (machine AND drive).

So
WHERE
(tblAssets.AssetUnique NOT LIKE '%1ip-08%' AND tblDiskdrives.Caption NOT LIKE '%E%')
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

becomes
WHERE
NOT (tblAssets.AssetUnique LIKE '%1ip-08%' AND tblDiskdrives.Caption LIKE '%E%')
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

archaic0
Engaged Sweeper II
The problem is I think I need a subquery or at least something more complex than the system seems to allow. I tried this:


Where (tblAssets.AssetUnique Not Like '%1ip-08%' And
tblDiskdrives.Caption Not Like '%E%') 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

And it took my parenthesis out which breaks the query by excluding ALL drive letters 'E'.

I just want the E drive of ONE machine named 1ip-08 excluded.
RCorbeil
Honored Sweeper II
Add tblDiskdrives to your query. That will give you the drive letters along with drives' capacities & free space.