1

I have a spreadsheet of a bunch of AD users with the following columns. Column A (First Name) Column B (Last Name) Column C (Email Address). How can i create a script that will -Filter out the email address of each AD Object and give me the SamAccountName property of each user exported back out to a CSC.

I thought it would be

$email = Get-Content -Path c:\users.csv

Get-AdUser -filter -Property EmailAddress

that obviously doesnt work.

So anyway i can provide the emailaddress data point and it spit me back out the samaccountname of all 600 users?

4 Spice ups
2 
import-csv -Path C:\somefilename.csv | ForEach-Object{
  Get-ADUser -Filter * {EmailAddress -eq "$_.EmailAddress"} -properties EmailAddress | Select-Object -ExpandProperty SamAccountName,Name,GivenName,EmailAddress
} | Export-csv -Path c:\Adoutput.csv -NoTypeInformation

Not tested. Please check the same.

3 
import-csv -Path C:\somefilename.csv | ForEach-Object{
  Get-ADUser -Filter * {EmailAddress -eq "$_.EmailAddress"} -properties EmailAddress | Select-Object SamAccountName,Name,GivenName,EmailAddress
} | Export-csv -Path c:\Adoutput.csv -NoTypeInformation

Corrected One!

4

Thanks Dilly. I got an error stating the following:

Get-ADUser : A positional parameter cannot be found that accepts argument
‘EmailAddress -eq “$_.EmailAddress”’.
At C:\Users\1083786\OneDrive - Towne Park Ltd\IT -
Powershell\GetSamAccountNameFromEmail Address.ps1:2 char:3

  • Get-ADUser -Filter * {EmailAddress -eq “$_.EmailAddress”} -properti …
  • CategoryInfo : InvalidArgument: (:slight_smile: [Get-ADUser], ParameterBinding
    Exception
  • FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.ActiveDirecto
    ry.Management.Commands.GetADUser

Mind you on my users.csv i only have one column with the header “emailaddress”

@dillybabu

5

The property is not $.emailaddress it should be $.mail

6

Try this

import-csv -Path C:\psscripts\somefilename.csv | ForEach-Object{
  Get-ADUser -Filter * -properties emailAddress | Select-Object SamAccountName,Name,GivenName,emailAddress
} | Export-csv -Path c:\psscripts\Adoutput.csv -NoTypeInformation
7

Tigris thank you very much for your help. This is working but it does not pull data from my csv file. It works but it pulls everyone in the companies data not just the three objects (3 email addresses) currently in the csv file. I think it has to do -Filter * but not sure. But the results.csv has over 2000 entries and the users.csv only has 3 emails.

8

You are correct. -Filter * will find all of the users. Try something like this:

import-csv -Path C:\psscripts\somefilename.csv | ForEach-Object {
  Get-ADUser -Filter {mail -like $_.emailaddress} -properties mail | Select-Object SamAccountName,Name,GivenName,mail
} | Export-csv -Path c:\psscripts\Adoutput.csv -NoTypeInformation

This code assumes that your CSV input file has a header that includes a column called EmailAddress.

9

I get an error message:


```
Get-ADUser : Property: 'emailaddress' not found in object of type: 'System.Management.Automation.PSCustomObject'.
At C:\Users\1083786\ITFOLDER\IT - Powershell\GetSamAccountNameFromEmail.ps1:2 char:3
+   Get-ADUser -Filter {mail -like $_.emailaddress} -properties mail |  ...
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Get-ADUser], ArgumentException
    + FullyQualifiedErrorId : ActiveDirectoryCmdlet:System.ArgumentException,Microsoft.ActiveDirectory.Management.Commands.GetADUser
```

```

```

users_csv.png

10

Did you change your input file? The original post said that you had first and last name in columns A and B and that email address was in column C. If you have only one column, you can use Get-Content to read from a text file that does not contain headers instead of Import-Csv. Then the filter will be different.

Get-Content -Path C:\psscripts\somefilename.txt | ForEach-Object {
  Get-ADUser -Filter {mail -like $_} -properties mail | Select-Object SamAccountName,Name,GivenName,mail
} | Export-csv -Path c:\psscripts\Adoutput.csv -NoTypeInformation
11

yes sir i did decide to go with a different data point that was unique to the user like his email address. So i the csv file only has 1 column with the header of emailaddress. So should i remove the header and change get-content to import-scv then try the same command?

12

Remove the header and try the code that I posted a few minutes ago (Get-Content instead of Import-Csv).

13

This worked FLAWLESSLY! Thank you very much.

14

This works the only thing that is weird is that if i remove the -whatif the code works but it comes up with a prompt saying asking me are you sure for every user (over 600) and even if i click yes to all it keeps prompting me are you sure you want to SET… I went ahead and clicked yes to all over 600 times to get it done but wondering why it was doing that.

@techadmin8

15

Were you running this code? Was anything different?

Get-Content -Path C:\psscripts\somefilename.txt | ForEach-Object {
  Get-ADUser -Filter {mail -like $_} -properties mail | Select-Object SamAccountName,Name,GivenName,mail
} | Export-csv -Path c:\psscripts\Adoutput.csv -NoTypeInformation

Prompts like that usually are for confirming changes, and the only change is Export-Csv writing to the output file. Usually Export-Csv will write without prompting, unless you specify the -Confirm parameter. You could add -Force to the end of Export-Csv so that it will not prompt for confirmation.

https://www.google.com/search?q=export-csv&rlz=1C1GGRV_enUS802US802&oq=export-csv&aqs=chrome..69i57j0l5.1879j0j7&sourceid=chrome&ie=UTF-8