Forum Discussion

jcasqueiro's avatar
jcasqueiro
Brass Contributor
Oct 19, 2023

Redirecting output to a csv file

Hi,

I am running the following commands to list users in my tenant:


$UserId = (Get-AzureADUser -All $true).objectId
foreach ($Object in $UserId) {Get-AzureADUserExtension -ObjectId $Object}

 

result:
Key Value
--- -----
odata.metadata https://graph.windows.net/76885cf7-0f1a-48...
odata.type Microsoft.DirectoryServices.User
createdDateTime 09/10/2022 22:03:48
employeeId
onPremisesDistinguishedName
userIdentities []
extension_fe2174665583431c953114ff7268b7b3_Education_ObjectType Teacher
extension_fe2174665583431c953114ff7268b7b3_Education_AnchorId Teacher_170768.404
extension_fe2174665583431c953114ff7268b7b3_Education_SyncSource SIS
extension_fe2174665583431c953114ff7268b7b3_Education_SyncSource_TeacherId 170768.404
extension_fe2174665583431c953114ff7268b7b3_Education_TeacherNumber 600074951
extension_fe2174665583431c953114ff7268b7b3_Education_TeacherStatus Active
extension_fe2174665583431c953114ff7268b7b3_Education_Title Docente
extension_fe2174665583431c953114ff7268b7b3_Education_SyncSource_SchoolId 170768.1
extension_fe2174665583431c953114ff7268b7b3_Education_Email email address removed for privacy reasons

..... NEXT RECORD....

 

Is there  way to redirect this output to a csv file in the format:

           Userfield1   Userfield2    Userfield3 .....

Rec1

Rec2

Rec3

.....

 

I have tried with the statement below but it doesn't work:

foreach ($Object in $UserId) {Get-AzureADUserExtension -ObjectId $Object | Export-Csv -Path "c:\temp.csv" -Append -Encoding UTF8}

 

Thanks/Brgds

joao

  • jcasqueiro 

     

    Hi, Joao.

     

    There's an easier and more efficient way to do what you're trying to do.

     

    It's also worth pointing out that the AzureAD and AzureADPreview modules are slated for retirement (now March 2024, but this date has already slipped multiple times), meaning it would be a good idea for you to stop using those and get onto the newer modules beginning with Microsoft.Graph, which are the modules Microsoft is continuing to focus development of.

     

     

    For this following example, I'm using the Microsoft.Graph.Beta.Users module. When you install any of the Microsoft.Graph modules, a shared module named Microsoft.Graph.Authentication is also installed. This is expected and required.

     

    This first example shows you how to connect and pull the extension attributes for a user.

     

    Example 1

    Connect-MgGraph -TenantId robertsonpayne.onmicrosoft.com -NoWelcome;
    $u = Get-MgBetaUser -UserId "email address removed for privacy reasons" -Property *;
    $u.AdditionalProperties;

     

    So, we don't need two separate calls to Azure get the extension attributes, however, they're not much use to us in their current key-value pair (using a type known as a "Dictionary") format. We need them in object format if we're going to output them to file.

     

    To do this, we are going to use a process called "type casting", which simply means converting from one type into another type. In our case, we're converting the HashTable into new class-like object.

     

    We actually have to perform two type casts:

     

    1. From the original Dictionary type into HashTable type;
    2. From HashTable type into PSCustomObject type.

     

    Fortunately, this is quite easy in PowerShell, as shown in Example 2.

     

    Example 2

    [PSCustomObject] [hashtable] $u.AdditionalProperties;

     

    Now that we have the extension attributes in a "normal" object format, we can proceed to put this together and export values to CSV.

     

    Example 3

    Get-MgBetaUser -UserId "email address removed for privacy reasons" -Property * |
        ForEach-Object {
            $ExtensionAttributes = [PSCustomObject] [HashTable] $_.AdditionalProperties;
    
            [PSCustomObject] @{
                "id" = $_.id;
                "userPrincipalName" = $_.userPrincipalName;
                "employeeNumber" = $ExtensionAttributes.extension_1e6f665fbc8a4fdd80e12b4c5f2d2a20_employeeNumber;
            }
        } | Export-Csv -NoTypeInformation -Path "C:\tmp.csv";

     

    In example 3 in the block beginning on line 5, I'm pulling just a few attributes, with one of those (employeeNumber) being an extensionAttribute. You can add whatever you like within this block.

     

    Here's a screen dump of the various commands and stages of progression.

     

     

     

    Cheers,

    Lain

  • LainRobertson's avatar
    LainRobertson
    Silver Contributor

    jcasqueiro 

     

    Hi, Joao.

     

    There's an easier and more efficient way to do what you're trying to do.

     

    It's also worth pointing out that the AzureAD and AzureADPreview modules are slated for retirement (now March 2024, but this date has already slipped multiple times), meaning it would be a good idea for you to stop using those and get onto the newer modules beginning with Microsoft.Graph, which are the modules Microsoft is continuing to focus development of.

     

     

    For this following example, I'm using the Microsoft.Graph.Beta.Users module. When you install any of the Microsoft.Graph modules, a shared module named Microsoft.Graph.Authentication is also installed. This is expected and required.

     

    This first example shows you how to connect and pull the extension attributes for a user.

     

    Example 1

    Connect-MgGraph -TenantId robertsonpayne.onmicrosoft.com -NoWelcome;
    $u = Get-MgBetaUser -UserId "email address removed for privacy reasons" -Property *;
    $u.AdditionalProperties;

     

    So, we don't need two separate calls to Azure get the extension attributes, however, they're not much use to us in their current key-value pair (using a type known as a "Dictionary") format. We need them in object format if we're going to output them to file.

     

    To do this, we are going to use a process called "type casting", which simply means converting from one type into another type. In our case, we're converting the HashTable into new class-like object.

     

    We actually have to perform two type casts:

     

    1. From the original Dictionary type into HashTable type;
    2. From HashTable type into PSCustomObject type.

     

    Fortunately, this is quite easy in PowerShell, as shown in Example 2.

     

    Example 2

    [PSCustomObject] [hashtable] $u.AdditionalProperties;

     

    Now that we have the extension attributes in a "normal" object format, we can proceed to put this together and export values to CSV.

     

    Example 3

    Get-MgBetaUser -UserId "email address removed for privacy reasons" -Property * |
        ForEach-Object {
            $ExtensionAttributes = [PSCustomObject] [HashTable] $_.AdditionalProperties;
    
            [PSCustomObject] @{
                "id" = $_.id;
                "userPrincipalName" = $_.userPrincipalName;
                "employeeNumber" = $ExtensionAttributes.extension_1e6f665fbc8a4fdd80e12b4c5f2d2a20_employeeNumber;
            }
        } | Export-Csv -NoTypeInformation -Path "C:\tmp.csv";

     

    In example 3 in the block beginning on line 5, I'm pulling just a few attributes, with one of those (employeeNumber) being an extensionAttribute. You can add whatever you like within this block.

     

    Here's a screen dump of the various commands and stages of progression.

     

     

     

    Cheers,

    Lain

Resources