I have a .csv with thousands of emails, and I need to use Active Directory to find the state of a particular, custom variable, using powershell

Question

I'm new and don't know enough about powershell.

I've got a .csv that is nothing but "EMAILS" for the header and some 6000 emails under it. (email1@company, email2@company, etc.)

I need to find the state of a particular, custom property for each one.

Individually, I know that I can run

Get-ADUser -Filter {mail -eq 'email@company'} -properties customproperty

in order to find one particular user's state.

I have been hitting my head against a wall trying to make it work with import-csv and export-csv, but I keep getting red all over my console.

Can someone point me an to example where import-csv and export-csv are used properly, with a command run against the contents?

Answer 1

Here's what I would do.

First, fetch all users that have email addresses in AD, and save them into a hashtable. This will make lookups absurdly faster and place less overall load on your domain controller. If you've got 100,000 user accounts it may not be the best option, but I expect that it will be in general.

$ADUsers = @{};
Get-ADUser -Filter "mail -like '*'" -Properties mail, customproperty | ForEach-Object { 
    $ADUsers.Add($_.mail, $_.customproperty);
}

Now you import the CSV and do lookup using a calculated property with Select-Object, and export it back out.

Import-Csv -Path $InputFile | Select-Object -Property emails, @{n='customproperty';e={$ADUsers[$_.emails]}} | Export-Csv -Path $OutputFile -NoTypeInformation;

Answer 2

So without seeing the code of what you posted, where I think you will run problems is with how interaction of two things.

The first will be that when you use the Import-CSV cmdlet. You will receive an array of objects with a property for each column and not an array of strings.

This is ties into the second part of the issue which is the sensitivity of the AD module filter. But the short answer is don't use {} inside the filter because it will break if you use {mail -eq $ImportedCSV.EMAILS}. mklement0 has a wonderful answer that goes into the details. But a simple rule of thumb is "double quote outer, single quote" inner.

So you could expand the EMAILS property either with Select-Object -ExpandProperty EMAILS to have an array which works inside {} or you could use "mail -eq '$ImportedCSV.EMAILS'".

Here is an example with both the expansion and using the "property -eq '$variable'" style filter:

Import-CSV C:\Example\Path.csv |
    Select-Object -ExpandProperty EMAILS |
    ForEach-Object {
        Get-ADUser -Filter "mail -eq '$_'" -Properties customproperty
    } |
    Select-Object mail,customproperty |
    Export-CSV C:\Example\OutputPath.csv -NoTypeInformation

Answer 3

Please use below code

$csvInput = Import-CSV C:\Example\test.csv

Foreach ($line in $csvinput) {
  Get-ADUser -Filter {mail -eq $line.mail} -Properties mail, customproperty | 
     Select-Object mail,customproperty | 
        Export-CSV C:\Example\OutputPath.csv -NoTypeInformation
}