Filter Parameter Problem

This topic contains 8 replies, has 3 voices, and was last updated by Profile photo of Poshoholic Poshoholic 3 years, 10 months ago.

  • Author
    Posts
  • #7180
    Profile photo of Martin9700
    Martin9700
    Participant

     

     

    Hi Everyone,

    Trying to use the filter parameter on Get-CASMailbox and running into an issue.  The original thought was to do this:

    Get-CASMailbox -ResultSize Unlimited -filter {hasactivesyncdevicepartnership -eq $true -and whencreated -gt "05/30/2013"} | select displayname,whencreated

    Works great.  Want to make the date a variable?  Can't get it to work.  I've tried datetime variable, I've converted it to string using .ToString("MM/dd/yyyy") with no luck.

    I've also attempted to assign the scriptblock to a variable (even tried an hashtable) but still no luck.  Here's the test code at this time:

     

    cls
    $dt = ((Get-Date).AddDays(-5)).ToString("MM/dd/yyyy")
    $filter = {hasactivesyncdevicepartnership -eq $true -and whencreated -gt $dt}
    #$filter = {$(filter)}
    
    Write-Host "`$DT = $dt"
    Write-Host $filter
    
    Get-CASMailbox -ResultSize Unlimited -filter $filter | select displayname,whencreated
    Write-Host "`$DT = $dt"

    I've attempted to put the $DT in double and single quotes, and a few other methods.  The result seems to be $null no matter what I do (so I'm either getting ALL mailboxes which if you use the manual command and say WhenCreated -eq $null happens) or I get an error (usually unable to convert to datetime variable).

    The weird thing is if I run it from PowerGUI the code works fine, it's only when I run it from the shell that I have the problem.

    Of course, I could solve this by simply piping into Where and be done with it but the performance hit is significant.  With my little 200 person network using the manual filter parameter the query took about half a second.  With the Where clause it took 6.5 seconds!  Larger deployments this would get significantly longer!

    So, any thoughts?

    *I have tried a lot of different things, so if I forgot to mention something here, sorry... I'm trying to keep it concise and not write a novel!  🙂

  • #7181
    Profile photo of Martin9700
    Martin9700
    Participant

    Just testing...

    [sourcecode language="powershell"]

    $DT = (Get-Date).AddDays(-1)

    [/sourcecode]

  • #7183
    Profile photo of Richard Siddaway
    Richard Siddaway
    Moderator

    I haven't got an Exchange box handy at the moment but one thought – what is the type of the whencreated field? Is it a datetime? Also is that field a real field on the mailbox or is it derived by the cmdlet?

  • #7185
    Profile photo of Martin9700
    Martin9700
    Participant

    Ok, I'm back into the office too and can more easily get to MY Exchange server!

    It's a DateTime field.  I've also run the script without the .ToString method and that was no help, if you're heading that way.  Not sure about the cmdlet derived field!  That would make sense.

    Here's the Get-Member from Get-CASMailbox.

  • #7189
    Profile photo of Poshoholic
    Poshoholic
    Member

    There are two key details that will help you resolve this issue:

    1. In Exchange cmdlets, -Filter is a string, not a script block.
    2. In PowerShell, ScriptBlock objects implicitly convert to strings.

    To understand how these details impact what you are trying to do here, let's look first at the static input you created that is working:

    {hasactivesyncdevicepartnership -eq $true -and whencreated -gt "05/30/2013"}

    If you were to assign that to a scriptblock and then typecast that to a string, you would see the following:

    hasactivesyncdevicepartnership -eq $true -and whencreated -gt "05/30/2013"

    That is a valid string filter for the Exchange cmdlet you are using. If you want to switch from using a statically defined date to a variable, then you just need to make sure that the string that you end up with uses the same format as the one that you get from a static date value.

    If you invoke the following code, pay close attention to what you get as a string value for your filter:

    $dt = Get-Date -Format 'MM\/dd\/yyyy'
    [string]{hasactivesyncdevicepartnership -eq $true -and whencreated -gt $dt}

    That outputs the following on my system:

    hasactivesyncdevicepartnership -eq $true -and whencreated -gt $dt

    You can see here that the implicit conversion of scriptblocks to strings is identical to using single-quotes (i.e. variables are not expanded). Yet when you invoke an Exchange cmdlet, it executes on the remote system where $dt is not defined, and therefore your filter does not work as expected. Instead, try building your string filter using double-quotes instead of a script block so that you have full control over what is passed into the Exchange server and so that you can pass over the date value, just like when you were statically defining it. When taking this approach, don't forget to escape any dollar signs that you actually want passed through (like the one for the $true variable). Here's how I would define the filter you want on the client:

    $filter = "hasactivesyncdevicepartnership -eq `$true -and whencreated -gt`"$dt`""

    Taking this approach should resolve the issues you were having with the filter parameter in Exchange cmdlets.

  • #7191
    Profile photo of Martin9700
    Martin9700
    Participant

    Yes, I have tried various iterations of this, including using "" instead of `" and so on.  Here's the error message I receive:
    Invoke-Command : Cannot bind parameter 'Filter' to the target. Exception setting "Filter": "Invalid filter syntax. For
    a description of the filter parameter syntax see the command help.
    "hasactivesyncdevicepartnership -eq True -and whencreated -gt "05/29/2013"" at position 36."

     

    And the code:

    $dt = ((Get-Date).AddDays(-5)).ToString("MM/dd/yyyy")
    $filter = "hasactivesyncdevicepartnership -eq $true -and whencreated -gt `"$dt`""
    Get-CASMailbox -ResultSize Unlimited -filter $filter | select displayname,whencreated

  • #7192
    Profile photo of Poshoholic
    Poshoholic
    Member

    Look at the error text you get back. Your value of $true was converted to "True" in the filter. You need to escape that variable so that you actually pass in $true without evaluating it on the client.

  • #7193
    Profile photo of Martin9700
    Martin9700
    Participant

    Doh! That got it. Thank you Kirk!

    Cannot tell you how many times I've told people to read the error message 😀

  • #7195
    Profile photo of Poshoholic
    Poshoholic
    Member

    Excellent, glad that resolved it for you Martin! 🙂

You must be logged in to reply to this topic.