Filtering based on calculated properties

Tagged: , , ,

This topic contains 3 replies, has 2 voices, and was last updated by  Dave Wyatt 3 years, 4 months ago.

  • Author
  • #14516


    Hi guys,

    I have a new problem, where I got stuck:
    I am generating a huge csv report, where I would like to remove a lot of rows. But there are so many, that Excel can't handle them. So, I thought I might leave them out directly when generating the report.
    So, here is a simplified example of what I do:

    dir C:\ -recurse | Select Name,@{n="Size";e={$_.Length / 1MB}}

    Now, how can I filter the results, so that I get only the files with the new property "Size" larger than 20 MB? The filter must look for the Size Property, not $_.Length
    Or even more simplified: Can I apply "Where" after "Select"? I couldn't manage to do it.

  • #14517

    Dave Wyatt

    Sure, you can, but you may find that you get better performance with filtering first, then adding the calculated property. Either of these should work:

    dir C:\ -recurse | Where-Object { $_.Length -gt 20MB } | Select Name,@{n="Size";e={$_.Length / 1MB}}
    dir C:\ -recurse | Select Name,@{n="Size";e={$_.Length / 1MB}} | Where-Object { $_.Size -gt 20 }
  • #14557


    I tested it today. Well, the above mentioned example works indeed, but the original one, which is a bit more complex fails to do the filtering.
    So, here is the actual script (I only changed the domain name):

    Get-ADComputer -Filter * -SearchBase "DC=fabrikam,DC=com" -Properties IntDSFeedback |
    where {$_.enabled -eq "True"} |
    Select Name,DistinguishedName,@{n="Bitlocker";e={$_.IntDSFeedback | Select-String "##Bitlocker.Platform=laptop"}},@{n="RK";e={Get-ADObject -Filter * -SearchBase $_.distinguishedname |
    where {$_.objectclass -eq "msFVE-RecoveryInformation"}}} |
    where {$_.Bitlocker -ne ""} |
    Export-Csv D:\laptops.csv

    That IntDSFeedback property is a custom property, which contains an array of strings and I only wanted to get those PCs, where that string existed.
    The bold part of the code was the one not working –> the exported csv file also contained PCs where the bitlocker property was empty.

    Well, it turned out that the part which created me problems was the comparison: "-ne" didn't work –> it only worked with "-notlike".

    My final command looks like this:

    Get-ADComputer -Filter * -SearchBase "DC=fabrikam,DC=com" -Properties IntDSFeedback |
    where {($_.enabled -eq "True") -and (($_.IntDSFeedback | Select-String "##Bitlocker.Platform=laptop") -notlike "")} |
    Select Name,DistinguishedName,@{n="Bitlocker";e={$_.IntDSFeedback | Select-String "##Bitlocker.Platform=laptop"}},@{n="RK";e={Get-ADObject -Filter * -SearchBase $_.distinguishedname |
    where {$_.objectclass -eq "msFVE-RecoveryInformation"}}} |
    Export-Csv D:\laptops.csv

    And yes, indeed, I got a great performance improvement by applying the filter as early as possible, even though it didn't look very pretty 🙂
    Thanks Dave!

  • #14562

    Dave Wyatt

    The problem is your use of Select-String. It either returns null (if no matches were found), or one or more Microsoft.PowerShell.Commands.MatchInfo object (if one or more matches were found.) In both cases, $_.BitLocker -ne "" will evaluate to True:

    $null -ne ""

    When you used the -notlike operator, what you've actually done is casted your result from Select-String to a string. $null becomes the empty string, and MatchInfo objects basically display the string. You could have done it a little more explicitly like this. The expression for the BitLocker property could be written as:

    $_.IntDSFeedback | Select-String "##Bitlocker.Platform=laptop" | Select -ExpandProperty Line -First 1

    This will result in a value of $null if no matches were found, and a String if the pattern was found. In the Where-Object call, you could do this:

    where { $_.Bitlocker }
    # Or
    where { -not [string]::IsNullOrEmpty($_.BitLocker) }

    It's up to you which one you find to be more clear. $null always evaluates to $false in a boolean expression, as do empty strings (and zero for numeric types, $false boolean variables, etc.) Any non-empty string will be $true.

You must be logged in to reply to this topic.