CODEWITHSUNDEEP
powershellactive-directory
Celestialchippy
Celestialchippy

Reputation: 271

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

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()

Upvotes: 5

Views: 9301

Answers (2)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200233

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 '*'.

Upvotes: 5

hsimah
hsimah

Reputation: 1303

You're close, but Filter takes a filter string not a script block (try Get-Help Get-ADUser).

This should get you what you need:

Get-ADUser -Filter "Mail -notlike '*'"

This page explains more on the filter syntax for AD.

Upvotes: 4

Related Questions