Can you use -Filter on Get-ADUser to filter out empty fields?

Question

Is there a way to filter out empty fields when grabbing the data from the Active Directory and then inserting it into a SQL table? When I used -Filter on Get-ADUser I don't think this is the correct syntax for doing this.

It fails saying

The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input

$ADARRAY = (Get-ADGroupMember -Identity "Domain Users" -Recursive |
           Get-ADUser -Filter {-not (Mail -like "*")} -Properties Mail)

foreach($OBJECT in $ADARRAY) {
    $NAME = $OBJECT.Name 
    $USER = $OBJECT.SamAccountName
    $EMAIL = $OBJECT.Mail

    $INSERT = "INSERT $TABLE VALUES ('$USER','$EMAIL', '$NAME')"
    $SQL.CommandText = $INSERT 

    $SQL.ExecuteNonQuery()
}

$SQLCON.Close()

Ansgar Wiechers Ansgar Wiechers · Accepted Answer · 2018-06-15T08:04:26

You're getting that error message because Get-ADUser can either read input from the pipeline or use the parameter -Filter. If you take a look at the documentation you'll see that the cmdlet has 3 parameter sets:

Get-ADUser
   [-AuthType <ADAuthType>]
   [-Credential <PSCredential>]
   -Filter <String>
   [-Properties <String[]>]
   [-ResultPageSize <Int32>]
   [-ResultSetSize <Int32>]
   [-SearchBase <String>]
   [-SearchScope <ADSearchScope>]
   [-Server <String>]
   [<CommonParameters>]

Get-ADUser
   [-AuthType <ADAuthType>]
   [-Credential <PSCredential>]
   [-Identity] <ADUser>
   [-Partition <String>]
   [-Properties <String[]>]
   [-Server <String>]
   [<CommonParameters>]

Get-ADUser
   [-AuthType <ADAuthType>]
   [-Credential <PSCredential>]
   -LDAPFilter <String>
   [-Properties <String[]>]
   [-ResultPageSize <Int32>]
   [-ResultSetSize <Int32>]
   [-SearchBase <String>]
   [-SearchScope <ADSearchScope>]
   [-Server <String>]
   [<CommonParameters>]

The second of these would be used for reading pipeline input from Get-ADGroupMember (via the parameter -Identity), but when specifying the parameter -Filter you're forcing the use of the first one, which does not accept your pipeline input.

Also, judging from your code you actually want user objects that do have a mail attribute.

Use a Where-Object filter like this and the code should do what you want:

$ADARRAY = Get-ADGroupMember -Identity 'Domain Users' -Recursive |
           Get-ADUser -Properties Mail |
           Where-Object { $_.Mail -like '*' }

If you really want user objects that don't have a mail attribute change the condition $_.Mail -like '*' to $_.Mail -notlike '*'.

Can you use -Filter on Get-ADUser to filter out empty fields?

2 Answers