PowerShell ForEach Loop to Add UserPrincipalName and object ID to a file

Question

I have a file of user emails. I need to gather their UPNs and object IDs.

Get-AzureADUser -Filter "PrimarySMTPAddress eq '$user.EmailAddress'"

I'm using this line to query AzureAD and it works perfectly. However when I place it in a loop I can't seem to get any data.

import-csv .\Book4.csv | ForEach-Object{
    Get-AzureADUser -Filter "PrimarySMTPAddress eq '$_.EmailAddress'" | Select-Object UserPrincipalName, ObjectID
} | Export-Csv -Path .\Book4.csv -NoTypeInformation

Can anyone tell me where I'm going wrong. I thought this would be something simple but I've been stuck for an hour. The CSV file has three column headers: EmailAddress, UserPrincipalName, ObjectID

Alternatively, since you already have the UPN of the users you could use `-SearchString $_.UPN` (where `UPN` would be the CSV header). — Santiago Squarzon, Jun 24 '21 at 03:02

mklement0 · Accepted Answer · 2021-06-24T12:03:27.630

"PrimarySMTPAddress eq '$_.EmailAddress'" doesn't work as intended, because the attempted property access on variable $_, .EmailAddress, isn't effective:

Inside "...", an expandable string, you need $(...), the subexpression operator in order to access a variable's properties^[1], call methods on it, or, more generally embed entire statements.

Therefore, use the following string instead:

"PrimarySMTPAddress eq '$($_.EmailAddress)'"

Also, you're mistakenly trying to read from and write back to the same file (.\Book4.csv) in the same pipeline, which doesn't work (without additional effort), as discussed in Allen Wu's helpful answer.

^{[1] Without enclosing in $(...), "...$_.EmailAddress..." cause $_ to be expanded by itself, whereas .EmailAddress is used verbatim. Compare Write-Output "$HOME.Length" to Write-Output "$($HOME.Length)", for instance.}

Allen Wu · Answer 2 · 2021-06-24T03:09:36.750

3

@mklement0 is correct.

But another important thing is that we can't read from and write back to the same file in the same pipeline. Unless you make sure that the input file is read into memory, in full, before its lines are sent through the pipeline.

Use this:

$users = import-csv E:\temp\Book4.csv | ForEach-Object{
    Get-AzureADUser -Filter "PrimarySMTPAddress eq '$($_.EmailAddress)'" | Select-Object UserPrincipalName, ObjectID
} 

$users | Export-Csv -Path E:\temp\Book4.csv -NoTypeInformation

edited Jun 24 '21 at 03:09

answered Jun 24 '21 at 02:42

Allen Wu

15,529
1
9
20

1

Good point about trying to write back to the same file in the same pipeline. Note that there's a simple workaround, however, which allows sticking with a single pipeline: Enclose the file-reading command (`Import-Csv`) in `(...)`, which forces reading in full, up front: `(import-csv E:\temp\Book4.csv) | ForEach-Object { Get-AzureADUser -Filter "PrimarySMTPAddress eq '$($_.EmailAddress)'" | Select-Object UserPrincipalName, ObjectID } | Export-Csv -Path E:\temp\Book4.csv -NoTypeInformation` – mklement0 Jun 24 '21 at 12:07
1

I appreciate your help! – Justin Reid Jun 24 '21 at 15:13

PowerShell ForEach Loop to Add UserPrincipalName and object ID to a file

2 Answers2