Find ADUsers with null value from import-csv (OUs)

I have a list of OUs I have saved to a variable that I then want to search all users with a null valued attribute.

I am not sure how to filter for the OU’s already imported.

Get-ADUser -SearchBase $allOUs -Filter * -Properties mailNickname | ? {$_.mailNickname -notlike "*"} | select samAccountName,DistinguishedName

It has to do with my searchbase parameter I believe since I think PS is looking for an actual (single) OU, not a collection.

How do I correct this please?

$allOUs |
ForEach-Object {
    Get-ADUser -SearchBase $_ -Filter * -Properties mailNickname |
    Where-Object {$_.mailNickname -notlike "*"} |
    select samAccountName,DistinguishedName
}

Hi Curtis, thanks

I get this error for each row of my $allOUs variable:

Get-ADUser : The supplied distinguishedName must belong to one of the following partition(s):‘CN=Configuration,DC=co,DC=com , CN=Schema,CN=Configuration,DC=co,DC=com , DC=amer,DC=co,DC=com’.

my $allOUs variable contains this format (no “”)

OU=Users1,OU=Vendors,DC=amer,DC=co,DC=com
OU=Users2,OU=Vendors,DC=amer,DC=co,DC=com
OU=Users3,OU=Vendors,DC=amer,DC=co,DC=com

Very odd, something weird is being seen in the object. Whatever it is can be cropped of by adding .trim()

$allOUs |
ForEach-Object {
    Get-ADUser -SearchBase $_.trim() -Filter * -Properties mailNickname |
    Where-Object {$_.mailNickname -notlike "*"} |
    select samAccountName,DistinguishedName
}

hrm…again for each row in $allOUs:

Method invocation failed because [System.Management.Automation.PSCustomObject] does not contain a method named ‘Trim’.

Are you sure it’s not because my rows inside of $allOUs are not enclosed by double quotes?

Certain, but I also don’t know the data gets inside your variable at all. I assumed a standard list of OUs as an array of strings which is what you would get by using get-content to pull in data from a file, but you haven’t really said how you are getting the data into $allOUs, and that could be where the problem is. (I know that’s a run-on sentence, but I’m too tired to fix it right now)

Here is the test I was working with

$allOUs = @"
CN=Users,DC=ca,DC=lab
OU=CA,DC=ca,DC=lab
CN=Users,DC=ca,DC=lab
"@ -split "`r`n"

$allOUs | 
ForEach-Object {
    get-aduser -SearchBase $_.Trim() -Filter * | Select Name
}

Similarly, this works just fine without the trim

$allOUs = get-content "C:\Users\Administrator\Desktop\OUs.txt"

$allOUs | 
ForEach-Object {
    get-aduser -SearchBase $_ -Filter * | Select Name
}

So the question really is, what is in your $allOUs variable because it’s not just an array of strings.

Ah i was using import-csv

Let me try get-content tomorrow and advise back.

Thanks

ya, if you have a flat text file with just a list of OUs, and use Import-CSV, it’s going to break your OU structure up based on the comma in the distinguished name into different columns, and by default used the first line as property names for the custom powershell object it creates, so instead of having a list like this in your $allOUs variable:

$allOUs = get-content "C:\Users\Administrator\Desktop\OUs.txt"

CN=Users,DC=ca,DC=lab
OU=CA,DC=ca,DC=lab
CN=Users,DC=ca,DC=lab

Instead you end up with a collection of powershell custom object with the Distingushed name all broken up like this:

$allOUs = import-csv "C:\Users\Administrator\Desktop\OUs.txt"

CN=Users DC=ca DC=lab
-------- ----- ------
OU=CA    DC=ca DC=lab
CN=Users DC=ca DC=lab

The above OUs.txt file contains:

CN=Users,DC=ca,DC=lab
OU=CA,DC=ca,DC=lab
CN=Users,DC=ca,DC=lab

my OU csv has numerous rows of OUs throughout the domain.

I ran:

$allOUs=get-content -Path 'C:\Amer_OU.csv'

I see the contents of the variable thus:

"OU=OutsideVendors,OU=Vendors,DC=amer,DC=co,DC=com"
"OU=Users,OU=Vendors,DC=amer,DC=co,DC=com"

I run this:

$AllOUs |
ForEach-Object {
    Get-ADUser -SearchBase $_ -Filter * -Properties mailNickname |
    Where-Object {$_.mailNickname -notlike "*"} |
    select samAccountName,DistinguishedName
}

I get this error:

Get-ADUser : The supplied distinguishedName must belong to one of the following partition(s): 'CN=Configuration,DC=co,DC=com , 
CN=Schema,CN=Configuration,DC=co,DC=com , DC=amer,DC=co,DC=com'.

I try with trim mthod and still get same above error.

Ok, that means your file is in fact a single column CSV file. Does it have a header value?

IE. Does the top of the file look something like this?

"OU"
"OU=OutsideVendors,OU=Vendors,DC=amer,DC=co,DC=com"
"OU=Users,OU=Vendors,DC=amer,DC=co,DC=com"

Or does it just start out as a list of OUs with no header value?

"OU=OutsideVendors,OU=Vendors,DC=amer,DC=co,DC=com"
"OU=Users,OU=Vendors,DC=amer,DC=co,DC=com"

When you import-csv, the cmdlet returns a collection of PowerShell custom objects as previous discussed. The Column headers are the property names for each object, and each row of data is a different object.

For Example, with the below CSV (OUs.csv):

"OU"
"CN=Users,DC=ca,DC=lab"
"OU=CA,DC=ca,DC=lab"
"CN=Users,DC=ca,DC=lab"

Import-CSV would return 3 objects, each one with a Property called OU

OU                   
--                   
CN=Users,DC=ca,DC=lab
OU=CA,DC=ca,DC=lab   
CN=Users,DC=ca,DC=lab

When you do a ForEach-Object loop with that collection, $_ represents the current object, but if you want the OU value for that object, you have to specify that property like so. $_.OU

$allOUs = import-csv "C:\Users\Administrator\Desktop\OUs.csv"

$allOUs | ForEach-Object {
    $_.OU
}

So if you plug that back into your code, it would look something like this:

$AllOUs |
ForEach-Object {
    Get-ADUser -SearchBase $_.OU -Filter * -Properties mailNickname |
    Where-Object {$_.mailNickname -notlike "*"} |
    select samAccountName,DistinguishedName
}

If your CSV does not have column headers in the first line, then the first OU in the list will be used as the column header, unless you use the -header parameter when you use import-csv.

For Example, with the below CSV (OUs.csv):

"CN=Users,DC=ca,DC=lab"
"OU=CA,DC=ca,DC=lab"
"CN=Users,DC=ca,DC=lab"

Import-CSV would return 3 objects, each one with a Property called OU since that is what you define with the -header parameter

$allOUs = import-csv "C:\Users\Administrator\Desktop\OUs.csv" -Header "OU"

$allOUs | ForEach-Object {
    $_.OU
}

Curtis,

sorry for the late reply.

I wasn’t aware of the -header parmameter and true enough my csv did not contain it. Just row by row of OU values (and no quotes anywhere)

So, I executed this for $allOUs:

$allOUs = import-csv “C:\Users\Administrator\Desktop\OUs.csv” -Header “OU”

and do see my output, row by row (again, no quotes anywhere) but when I plug this back into the original code you supplied:

$AllOUs |
ForEach-Object {
Get-ADUser -SearchBase $.OU -Filter * -Properties mailNickname |
Where-Object {$
.mailNickname -notlike “*”} |
select samAccountName,DistinguishedName
}

I get this new error:

Get-ADUser : Cannot validate argument on parameter ‘SearchBase’. The argument is null. Provide a valid value for
the argument, and then try running the command again.

Here are the properties for $allOUs:

   TypeName: System.Management.Automation.PSCustomObject

Name MemberType Definition


Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
ToString Method string ToString()
OU NoteProperty string OU=OU=DC,DC=amer,DC=co,DC=com

Jeff,
Please provide a sample of what is in your input file. The information seems to go back and forth as to whether or not there are double quotes in the input file. In the last update you said there are not, but in the previous update you showed where you did a get-content and the contents of the variable showed there were double quotes.

Secondly, provide the exact code you are executing as one script. Are you executing the import-csv as one script and then running the foreach-object part as a different script? Are they the same script? Where are these pieces of code in relation to each other? Are you executing in a powershell prompt, or in a development environment like the ISE?

Lastly, in the last update where you are showing the “properties for $allOUs”, the Definition for OU shows a string that is not a Valid OU distinguished name. Providing a sample from the input file will help determine if the input data is incorrect or if something else is going on.

Curtis, sorry for the confusion…

There are “” when I open up the CSV in Notepad ++ but not when I open up in Excel nor when I call the variable. Header is now there per the earlier instruction.

PS C:\> $allOUs

OU                                                                                      
--                                                                                      
OU=Conference Rooms,OU=NKY,OU=DC,DC=amer,DC=co,DC=com                              
OU=Users,OU=NKY,OU=DC,DC=amer,DC=co,DC=com                                         
OU=ConferenceRooms,OU=ODC,OU=DC,DC=amer,DC=co,DC=com                               
OU=Users,OU=ODC,OU=DC,DC=amer,DC=co,DC=com                                         
OU=ConferenceRooms,OU=PDC,OU=DC,DC=amer,DC=co,DC=com                               
OU=Users,OU=PDC,OU=DC,DC=amer,DC=co,DC=com

I was doing this in the ISE but have since save the following code as a .ps1 and dot sourced it forma new PS(5) Admin console.

$allOUs = Import-Csv -Path "C:\vNext deployment\mailNickname\Amer_OU.csv"

ForEach-Object {
    Get-ADUser  -Searchbase $_.OU -Filter * -Properties mailNickname |
    Where-Object {$_.mailNickname -notlike "*"} |
    select samAccountName,DistinguishedName
}

I still get this error:

PS C:\vNext deployment\mailNickname> .\emtpyNicknames.ps1
Get-ADUser : Cannot validate argument on parameter 'SearchBase'. The argument is null. Provide a valid value for the argument, and
then try running the command again.

Hi Jeff,

You are not Piping your data into the ForEach-Object cmdlet. That is why $_.OU is null. There’s no data.

$allOUs = Import-Csv -Path "C:\vNext deployment\mailNickname\Amer_OU.csv"

$allOUs |
ForEach-Object {
    Get-ADUser  -Searchbase $_.OU -Filter * -Properties mailNickname |
    Where-Object {$_.mailNickname -notlike "*"} |
    select samAccountName,DistinguishedName
}

Or you can simplify it and not use a variable at all by piping the output of the Import-Csv cmdlet to the ForEach-Object cmdlet.

Import-Csv -Path "C:\vNext deployment\mailNickname\Amer_OU.csv" |
ForEach-Object {
    Get-ADUser  -Searchbase $_.OU -Filter * -Properties mailNickname |
    Where-Object {$_.mailNickname -notlike "*"} |
    select samAccountName,DistinguishedName
}

Yep I see that now, thanks!

I tried the both options and piped into the foreach but get a different error. Get-ADUser is balking:

Get-ADUser : Directory object not found
At line:3 char:5
+     Get-ADUser  -Searchbase $_.OU -Filter * -Properties mailNickname  ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (:) [Get-ADUser], ADIdentityNotFoundException

Look at $allOUs | GM again:

   TypeName: System.Management.Automation.PSCustomObject

Name        MemberType   Definition                                                          
----        ----------   ----------                                                          
Equals      Method       bool Equals(System.Object obj)                                      
GetHashCode Method       int GetHashCode()                                                   
GetType     Method       type GetType()                                                      
ToString    Method       string ToString()                                                   
OU          NoteProperty string OU=OU=Conference Rooms,OU=NKY,OU=DC,DC=amer,DC=co,DC=com

Look at that funky OU NoteProperty. Is that because of the Header in the csv I added?

Here’s the output of the $allOUs:

OU                                                                                      
--                                                                                      
OU=Conference Rooms,OU=NKY,OU=DC,DC=amer,DC=co,DC=com                              
OU=Users,OU=NKY,OU=DC,DC=amer,DC=co,DC=com  

Curtis, thanks for catching the pipe issue. Noted.

Tried both options but still get an error with Get-ADUser

Get-ADUser : Directory object not found
At line:3 char:9
+         Get-ADUser  -Searchbase $_.OU -Filter * -Properties mailNickn ...

look at this funky NoteProperty for $allOUs | gm:

OU          NoteProperty string OU=OU=Conference Rooms,OU=NKY,OU=DC,DC=amer,DC=co,DC=com

This appears after adding the OU Header in the .csv

Hey Jeff,
There error simply means it cannot find one of the OUs that you have in your input file. Also your Where-Object comparison does not work. Add some verbose output to your script so you can see what OU it is failing on. Lastly, yes that NoteProperty does look odd and I’m not sure why it’s outputting the definition like that; however that does not interfere with the normal operation of the script.

Amer_OUs.csv input file when opened in Notepad

"OU=TestOU1,DC=ca,DC=lab"
"OU=TestOUNotExist,DC=ca,DC=lab"
"OU=TestOU2,DC=ca,DC=lab"

Script

$VerbosePreference = "Continue"

$allOUs = import-csv "C:\Users\Administrator\Desktop\Amer_OUs.csv" -Header "OU"

$allOUs |
ForEach-Object {
    Write-Verbose "Checking: $($_.OU)"
    Get-ADUser  -Searchbase $_.OU -Filter * -Properties mail |
    Where-Object {$_.mail -eq $null} |
    select samAccountName,DistinguishedName
}

$allOUs | Get-Member | Out-Default

Results:

VERBOSE: Checking: OU=TestOU1,DC=ca,DC=lab

samAccountName                                                                                                  DistinguishedName                                                                                             
--------------                                                                                                  -----------------                                                                                             
test2womail                                                                                                     CN=Test2 WithoutMail,OU=TestOU1,DC=ca,DC=lab                                                                  
VERBOSE: Checking: OU=TestOUNotExist,DC=ca,DC=lab
Get-ADUser : Directory object not found
At line:8 char:5
+     Get-ADUser  -Searchbase $_.OU -Filter * -Properties mail |
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (:) [Get-ADUser], ADIdentityNotFoundException
    + FullyQualifiedErrorId : ActiveDirectoryCmdlet:Microsoft.ActiveDirectory.Management.ADIdentityNotFoundException,Microsoft.ActiveDirectory.Management.Commands.GetADUser
 
VERBOSE: Checking: OU=TestOU2,DC=ca,DC=lab
test4womail                                                                                                     CN=Test4 WithoutMail,OU=TestOU2,DC=ca,DC=lab                                                                  


   TypeName: System.Management.Automation.PSCustomObject

Name        MemberType   Definition                              
----        ----------   ----------                              
Equals      Method       bool Equals(System.Object obj)          
GetHashCode Method       int GetHashCode()                       
GetType     Method       type GetType()                          
ToString    Method       string ToString()                       
OU          NoteProperty System.String OU=OU=TestOU1,DC=ca,DC=lab