Filtering based on calculated properties

Tagged: , , ,

This topic contains 3 replies, has 2 voices, and was last updated by Profile photo of Dave Wyatt Dave Wyatt 3 years ago.

  • Author
    Posts
  • #14516
    Profile photo of deiandrei
    deiandrei
    Participant

    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
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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
    Profile photo of deiandrei
    deiandrei
    Participant

    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
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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.