Exclude certain drives from low drive space report

[archaic0]

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?

[archaic0]

Thanks for the input. Definitely appreciate having the alternatives because you never know when the situation will fit a bit differently.

[RCorbeil]

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 · Accepted solution]

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]

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]

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]

Add tblDiskdrives to your query. That will give you the drive letters along with drives' capacities & free space.