Home > database >  Read CSV and loop through in PowerShell
Read CSV and loop through in PowerShell

Time:04-08

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"

CodePudding user response:

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
            }
        }
    }
}

CodePudding user response:

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.

  • Related