Home > Software engineering >  Powershell foreach loop to query AD for users from a .csv file
Powershell foreach loop to query AD for users from a .csv file

Time:11-12

Total beginner with powershell trying to become more efficient in my helpdesk job. Essentially I have a .csv file with a list of e-mail addresses that I want to use to query AD to see what accounts are enabled/disabled. I don't get any output from what I've tried so far which is:

Import-Module ActiveDirectory
$emails = Import-Csv -Path "c:\users\me\mycsvfile.csv"
foreach($email in $emails) {
    Get-ADUser -Filter {userprincipalname -eq '$email.email'} | Select-Object name,enabled
}

The .csv file has a header for that column called email which is why I've used '$email.email'. Anyone have any idea as to what I'm doing wrong (probably lots).

Many thanks

CodePudding user response:

These ad filters are a bit trial and error.

foreach($email in $emails) {
    $upn = $email.email
    Get-ADUser -Filter {userprincipalname -eq $upn} | Select-Object name,enabled
 }

name   enabled
----   -------
js     True

CodePudding user response:

A UserPrincipalName property can be the same as the users email address, but in fact they are different properties.
The similarity is that a UserPrincipalName (UPN) is the name of a system user in an email address format.

Let's assume in your domain the UPN is also the EmailAddress of a user.

I'm guessing your csv is really a text file with email addresses each on a separate line and that it has no header email.

Something like

[email protected]
[email protected]
[email protected]
someone.else@yourdomain.com

In that case, you either need to use Get-Content so you'll get all addresses (lines) as string array and loop over:

Import-Module ActiveDirectory
$emails = Get-Content -Path "c:\users\me\mycsvfile.csv"
foreach($email in $emails) {
    Get-ADUser -Filter "UserPrincipalName -eq '$email'" | Select-Object Name, Enabled
}

OR use Import-Csv and supply a header for the field(s) in there:

Import-Module ActiveDirectory
$emails = Import-Csv -Path "c:\users\me\mycsvfile.csv" -Header 'email'
foreach($email in $emails) {
    Get-ADUser -Filter "UserPrincipalName -eq '$($email.email)'" | Select-Object Name, Enabled
}
  • the -Filter parameter should actually be a string not a script block
  • when using object.property syntax in a filter, you need to surround it with $(), making it a sub expression that gets parsed by PowerShell into the value it contains.
  • Related