Script to get usernames based on email address

Question

_{Monday, August 22, 2016 2:43 PM}

Hello Everyone,

We are having an issue with starting to clean up Active Directory in preparation for Azure AD Connect deployment. There are a number of user accounts that we do not need to sync (16,000), and we need to add a flag to them to not sync them. We have a list of email addresses that are linked to user accounts, but we don't have the user names. We could do and manually find these, but that's not realistic. The duplicated addresses were exported from IDfix, they have an associated contact that we still need. The user accounts those email addresses are listed on do not need to be synced. They are all different addresses, from yahoo, gmail. various other external addresses.

The goal would be to take the list of email addresses that we have, run a query to get the username on them, export and append to a csv, so we can run a different script to apply and AdminDiscription attribute change to prevent a sync. 

Here is what I have, 

$exportlocation = c:\sripts\useracct.csv
$importlocation = c:\scripts\emailList.csv

foreach ($line in $importlocation) {Get-ADUser -Filter {mail -eq $line.email}}  | Export-csv -Path $exportlocation -append

I keep getting an empty pipe error. I can run the get-aduser part no problem on its own, it just doesn't return the UPN or simply SamAccountName on its own to export. When I add the Export-csv to the standalone script with manual email address entry I return:

 Export-csv : Cannot append CSV content to the following file: C:\scripts\useracct.csv. The appended object does n
have a property that corresponds to the following column: . To proceed with mismatched properties, add the -Force
switch and retry.

Adding -Force simply puts the attributes in the csv on one line with nothing else.  Little stumped. I don't care if it exports everything as long as its in separate columns that I can copy and paste, but I'm as about as far as I know right now.

All replies (6)

_{Tuesday, August 23, 2016 2:10 AM ✅Answered}

Hi Rob,

From the code itself to say, I  suppose we could use this instead:

$exportlocation = Import-Csv -Path 'c:\scripts\emailList.csv'
$exportlocation = 'c:\scripts\useracct.csv'
foreach($line in $importlocation)
{
 Get-ADUser -Filter {mail -eq "$($line.email)"}  | Export-csv -Path $exportlocation -append -NoTypeInformation
}

Best regards,

Andy_Pan

Please remember to mark the replies as an answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact [email protected].

_{Monday, August 22, 2016 3:09 PM}

I think you need to move the export-csv part into your foreach. Try something like this:

$exportlocation = c:\sripts\useracct.csv
$importlocation = c:\scripts\emailList.csv

foreach ($line in $importlocation) {Get-ADUser -Filter {mail -eq $line.email} | Export-csv -Path $exportlocation -append}

If my answer helped you, check out my blog: Deploy Happiness

_{Monday, August 22, 2016 3:46 PM}

That helped it get a little further! Thanks!

It seems it doesn't want to part the csv very well now:

Get-ADUser : Property: 'email' not found in object of type: 'System.String'.
At line:1 char:1

I know the csv has the email set as the property for the column. Even if I remove the email heading in the csv it gives the same error. 

Maybe its the variable that is called? $line.email? I did a little experiment where i set $filter = $line.email and then yelled saying $filter is not defined.

When I enter everything manually (email address and csv export path), it simply gives me >> endlessly

_{Monday, August 22, 2016 4:00 PM}

OK! I got the script figured out. It needs quotes everywhere!

$exportlocation = 'c:\scripts\useracct.csv'$importlocation = 'c:\scripts\emailList.csv'foreach($line in $importlocation) {Get-ADUser -Filter {mail -eq '$line.email'}  | Export-csv -Path $exportlocation -append}

This runs and everything. No errors, but also no sign of it actually going through the lines either. And nothing actually gets added to the csv. It remains empty. I don't think this actually ran correctly.

_{Tuesday, August 23, 2016 8:15 AM}

If you  do not read the file nothing else will work

** import_csv c:\scripts\emailList.csv |**
**     Foreach-object{**
**         # use eash line "$_" in your command**
**    }**

Good luck. Learning PowerShell would be a  big help to you and all respondents here. (I know "respondents" is s big word = look it up)

\(ツ)_/

_{Tuesday, August 23, 2016 1:19 PM}

Oh I can't belive I didn't notice I never actually imported the CSV. That did it. all working perfectly now!