Read CSV and loop through in PowerShell

Question

I am trying to Import a list of endpoints I have which is saved to a CSV, Loop through the list and Display the DeviceName, Owner, Depatment, Jobtitle. Please help.

$Endpoints = Import-Csv -Path "C:\Users\Ryan\Endpoints.csv"
$Result = @()
$Users = Get-AzureADDevice -filter $Endpoints.Endpoint | select DisplayName, objectID
$Users | ForEach -Object {
  $user = $_
  Get-AzureADDeviceRegisteredOwner -ObjectId $user.ObjectId -All $true |
    ForEach -Object {
      $Result += New-Object PSObject -property @{
        DeviceOwner = $_.DisplayName
        DeviceName = $user.Displayname
        Department = $_.Department
        Jobtitle = $_.Jobtitle
      }
    }
}
$Result | Select DeviceOwner, DeviceName, Department, Jobtitle

I had this working without Import using a static Endpoint Name e.g. $Users = Get-AzureADDevice -SearchString "LaptopName"

Your code has syntactical errors but basically you need to loop over your CSV and query Get-AzureADDevice on each iteration — Santiago Squarzon
– Santiago Squarzon, Commented Apr 7, 2022 at 13:34
It looks like you have a typo: $_Get-AzureADDeviceRegisteredOwner. Also, please indent your code properly to make it easier to read. — mklement0
– mklement0, Commented Apr 7, 2022 at 13:34

Santiago Squarzon · Accepted Answer · 2022-04-07 15:41:03Z

3

Based on assumptions this might be what you're looking for, note the outer loop to iterate over each row of your CSV:

Import-Csv -Path "C:\Users\Ryan\Endpoints Society\Desktop\Endpoint.csv" | ForEach-Object {
    $devices = Get-AzureADDevice -SearchString $_.Endpoint
    if(-not $devices) { return "$($_.EndPoint) not found, skipping" }
    foreach($device in $devices) {
        foreach($owner in Get-AzureADDeviceRegisteredOwner -ObjectId $device.ObjectId -All $true) {
            [pscustomobject]@{
                DeviceOwner = $owner.DisplayName
                DeviceName  = $device.Displayname
                Department  = $owner.Department
                Jobtitle    = $owner.Jobtitle
            }
        }
    }
}

edited Apr 7, 2022 at 15:41

answered Apr 7, 2022 at 14:20

Santiago Squarzon

65.5k5 gold badges26 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

starplus Over a year ago

Santiago, how is that code looking at the imported file its not assigned a variable?

Santiago Squarzon Over a year ago

@starplus yup, you don't really need to save the CSV in a variable, you can stream the results, the output generated by Import-Csv is being passed through the pipeline and iterated over with ForEach-Object

starplus Over a year ago

Got it the code worked - Thanks a lot apricate the help

Santiago Squarzon Over a year ago

@starplus glad to hear it worked :) the loop foreach($device in $devices) { might not be needed but up to you to test it.

FoxDeploy · Accepted Answer · 2022-04-07 16:56:04Z

2

Not your answer, but how to find it

This script suffers greatly from 'one-liner-itis'. It is very hard to follow and going to be impossible to debug because of its over-the-top reliance on pipes and foreach-object commands§

Step one to make this even testable is to unwrap these commands into stand-alone commands, then you can debug line by line and see what the values are at that line.

$Endpoints = Import-Csv -Path "C:\Users\Ryan\Endpoints.csv"
 
$Result=@()
$devices= Get-AzureADDevice -filter $Endpoints.Endpoint | 
    select DisplayName, objectID

#for debugging, we will only look at the first two devices
$devices = $devices[0..1]

foreach($device in $devices){
    $thisDeviceOwners = $_Get-AzureADDeviceRegisteredOwner -ObjectId  $user.ObjectId -All $true

    foreach($owner in $thisDeviceOwners){
       $Result += New-Object PSObject -property @{ 
          DeviceOwner = $_.DisplayName
          DeviceName = $user.Displayname
          Department = $_.Department
          Jobtitle = $_.Jobtitle
          }
    }
}
    $Result | Select DeviceOwner, DeviceName, Department, Jobtitle

Next, just run this one time in your IDE of choice, like PowerShell ISE or Visual Studio Code, then look to see what values you have for these variables. One of them will be empty or odd, and therein lies your problem:

$device
$devices
$thisDeviceOwners
$owner

Note that I changed $devices to only be the first two items in $devices, so you can see how the code is working. Find and fix your problem with one or two items and then the whole script will likely work.

§ - there is nothing wrong with your approach, and in fact PowerShell was built from the ground up to be like the Unix Bash Shell, loads of small commands that do one thing, and can be used to pass results on to the next command. It allows for ad-hoc powerful control.

But in an enterprise script, we should not use one-liners and should instead use commonly accepted coding practices. Each line of code should do only one thing, and shouldn't wrap around.

edited Apr 7, 2022 at 16:56

answered Apr 7, 2022 at 13:41

FoxDeploy

13.8k3 gold badges42 silver badges58 bronze badges

3 Comments

Lee_Dailey Over a year ago

i would remove the $Result += and just capture the output of the loop into the collection directly. try to avoid teaching that structure with arrays since it doesn't do what it seems to do. [grin] lookee ... learn.microsoft.com/en-us/powershell/scripting/learn/deep-dives/…

Santiago Squarzon Over a year ago

Get-AzureADDevice -filter $Endpoints.Endpoint doesn't like something that would work assuming its an array, and it's not a valid filter synatx either

FoxDeploy Over a year ago

Thats a good point, Lee, PowerShell is often taught with layers of 'do this', then later you find out 'actually you should not ever do that'

Collectives™ on Stack Overflow

Read CSV and loop through in PowerShell

2 Answers 2

4 Comments

Not your answer, but how to find it

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Not your answer, but how to find it

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related