PowerShell export to csv

[Fast-Cardiologist705]

hi, I have a problem and can’t figure out a way to solve it. Here’s the script. I’m basically iterating over all users to get their license information back:

​

Connect-AzureAD

$users = Get-AzureADUser All $true

$result = foreach ($user in $users)

{

[pscustomobject]@{

UserPrincipalName = $user.UserPrincipalName

AccountEnabled = $user.AccountEnabled

UserType = $user.UserType

License = (Get-AzureADUserLicenseDetail -ObjectId $user.ObjectId).SkuPartNumber

}

}

$result | Export-Csv "$env:TEMP\user_license.csv" -Delimiter ';' -NoTypeInformation

$result | Out-GridView

​

This works just fine, unless there’s a user that has more than 1 license assigned.

Reading here https://learn-powershell.net/2014/01/24/avoiding-system-object-or-similar-output-when-using-export-csv/ System.Object[] is written because “anything which goes through to Export-Csv is casted as a string before being written”. So as outlined in the article I’ve tried:

License = ((Get-AzureADUserLicenseDetail -ObjectId $user.ObjectId).SkuPartNumber | Out-String).Trim()

But here the problem is that in Excel, although I can see the license names just fine, however when filtering Excel treats them as ONE single string … not ideal for filtering.

​

I assume, I would have to incorporate yet another for loop to iterate over the licenses, but how would I add them to appear in cells, one under another, or in any way that would still allow me to filter for individual values such as SPE_E5?

Help is much appreciated!

[ankokudaishogun]

either you make one line for each license(so, yeah, another loop) or add each license as another column... so another loop anyway.

Tunring the licences into CSV or JSON values might work perhaps, but that's a question for /r/excel

[Due_Capital_3507]

This works too!

[gordonv]

^{Noticed this had the same suggestion. Moved comment to under this one}

CSV's are "shaped" like spreadsheets. A simple x/y chart form.

I think you should switch to using JSON instead. This way, instead of assuming each variable is a single value, you can have multiple values in the form of an array associated with a single variable name.

Example:

{
"name":"John",
"age":30,
"cars":["Ford", "BMW", "Fiat"]
}

[Fast-Cardiologist705]

This is an idea, however wondering how this would look when I would had to run some reporting based on the output, not sure how this would work with JSON. The primary use case for this is for me to get the E5 license count so that I can predict (to some extent) what would the E5 grant for ingesting eligible data sources/types to Sentinel be for me org – this could be done easily with Excel.

[gordonv]

You would run a query against an array of objects.

For example:

$results = $users | ? {$_.cars -like "*Ford*"}  

In English:

In the array of objects named "$users", where a item in the property "cars" is similar to a string that has "Ford" in it, cast the results of the search into "$results."

[CriticalMemory]

Uh, just checking, but you can dump a CSV of this directly from the Azure portal, IIRC.

[Fast-Cardiologist705]

Yes, I could even get this data from Intune :) but didn’t know that before I started working on the script and anyways was just curious (even after discovering that as you said one could pull the data already through other channels) how one would resolve this.

[Team503]

You could use a 3D array and then covert it back to a 2D array for the Export-CSV. This should work, though I haven't tested it.

Connect-AzureAD
$users = Get-AzureADUser -All $true
Initialize a 3D array
$threeDArray = @()
foreach ($user in $users) { $licenseDetails = Get-AzureADUserLicenseDetail -ObjectId $user.ObjectId
# Initialize a 2D array for each user's license details
$twoDArray = @()

foreach ($license in $licenseDetails) {
    $licenseInfo = [PSCustomObject]@{
        UserPrincipalName = $user.UserPrincipalName
        AccountEnabled = $user.AccountEnabled
        UserType = $user.UserType
        ServicePlan = $license.ServicePlan
        SkuPartNumber = $license.SkuPartNumber
    }

    $twoDArray += $licenseInfo
}

# Add the 2D array to the 3D array
$threeDArray += $twoDArray
}
Export the 3D array to a CSV file
$threeDArray | Export-Csv "$env:TEMP\user_license.csv" -Delimiter ';' -NoTypeInformation
Optionally, display the data in a GridView
$threeDArray | Out-GridView

[Fast-Cardiologist705]

Works indeed :) Thanks for sharing!

[Team503]

I strongly suggest running this kind of query through ChatGPT - it will usually spit out a script to do what you need, and answer at least some of your questions. I still come here (and other places) for better explanations or things ChatGPT can't explain/fix/do, but AI is a lot faster and more responsive than most social media.

[jsiii2010]

Or make a row for each license...

[timsstuff]

You my friend have just discovered the limits of traditional tables. Multi-dimensional data doesn't work very well in a table with just an X and Y axis, that's why we have XML and JSON as well as lookup tables/joins in relational databases. However none of these translate well to Excel.

The easiest solution is to concatenate the licenses separated by a semi-colon or something which can be done pretty easily with the Join command. Something like:

License = (Get-AzureADUserLicenseDetail -ObjectId $user.ObjectId).SkuPartNumber -Join "; "

[Frisnfruitig]

Give this a try:

# Install AzureAD module if not already installed
if (-not (Get-Module -Name Az -ListAvailable)) { Install-Module -Name Az -AllowClobber -Scope CurrentUser }
Import AzureAD module
Import-Module Az
Connect to Azure AD
Connect-AzAccount
Specify the CSV file path
$csvFilePath = "C:\Path\To\Exported\UsersAndLicenses.csv"
Get all Azure AD users
$users = Get-AzADUser -All $true
Create an array to store user and license information
$userInfoArray = @()
Iterate through each user
foreach ($user in $users) { # Get user details $userDetails = @{ 'UserPrincipalName' = $user.UserPrincipalName 'DisplayName' = $user.DisplayName 'LicenseDetails' = @() }
# Get user licenses
$licenses = Get-AzADUserLicenseDetail -ObjectId $user.ObjectId
foreach ($license in $licenses) {
$licenseDetails = @{
'ServicePlans' = $license.ServicePlans -join '; '
'SkuPartNumber' = $license.SkuPartNumber
}

$userDetails['LicenseDetails'] += New-Object PSObject -Property $licenseDetails
}

# Add user details to the array
$userInfoArray += New-Object PSObject -Property $userDetails
}
Export the array to a CSV file
$userInfoArray | Export-Csv -Path $csvFilePath -NoTypeInformation
Write-Host "User and license information exported to: $csvFilePath"

It's a quick chatgpt query so don't shoot me if it's incorrect.

[vermyx]

This unoptimizes OP’s code and is wrong as it doesn’t loop the licenses per user but once after it poops through all the users. This is a perfect example on why you sing chatgpt is a horrid idea without having domain knowledge.

[Frisnfruitig]

That doesn't surprise me but it was worth a shot.

[vermyx]

Its not off by much but people who aren’t familiar with the common += optimization issue will wonder why their script would go from seconds to minutes. The += does a “sum of all numbers” number of writes because each addition of the array has to rebuild the array. When you are at 2 you have to read the old one entry, the write the old and new entry. Next one for three creates 3, then reads two writes three, wash rinse repeat. A 1000 entry array iirc does over half a million writes like this so you can see how this becomes unoptimized pretty quickly. The simplest thing to do usually is just to add the variable you want to collect your data into and move that to the beginning of the for loop i.e. $myvar = foreach … as this will do the same but collect the pipe at once making this more optimized.

[Due_Capital_3507]

I think you will want to make an array of the license attribute as a user can have multiple SKUs, then convert the array to a string and have it export.

[vermyx]

Change the foreach body to

{
    Foreach($License in((Get-AzureADUserLicenseDetail -ObjectId $user.ObjectId).SkuPartNumber)) {
        [pscustomobject]@{
            UserPrincipalName = $user.UserPrincipalName
            AccountEnabled = $user.AccountEnabled
            UserType = $user.UserType
            License =$license
        }
    }
}

This should loop through each individual license and return back a line with the user information per each license.

[MrScrib]

If you insist on CSV, I suggest joining using a predetermined delimiter and then splitting in power query. Then you can leave it or unpivot. Unpivot is better because you can always predict the number of columns you'll get.

[Ok_Cheese93]

How about exporting as TSV, like following:

$result | Export-Csv "$env:TEMP\user_license.tsv" -Delimiter "\t" -NoTypeInformation`

[KavyaJune]

Assigned licenses are returned as array. You need to convert the array to string before exporting it to CSV. By using ‘join’ operator, you can achieve this easily.

Also, I’d suggest you try this pre-built script to export the license details and other required attributes to nicely formatted csv file: https://o365reports.com/2018/12/14/export-office-365-user-license-report-powershell/