none
Combine output for AzureADGroup and AzureADGroupMember RRS feed

  • Question

  • I am just starting to learn PowerShell and would appreciate some assistance in exporting Groups and their members from AzureAD,I have exported all the groups to a csv, then imported them as an array. I can step through the array and get its members.But need help in outputting both the Group information and the members to the same file.

    This is the script I used to create the Groups list.

    Get-AzureADGroup -All 1 | select ObjectId,Description,DirSyncEnabled,DisplayName,LastDirSyncTime,Mail,MailNickName,MailEnabled,SecrityEnabled | Sort-Object DisplayName | Export-Csv -Path "C:\Temp\AzureAD-GRP-00.csv" -NoTypeInformation -Append

    Here is my current script.

    $FileName = "C:\Temp\Outputfile-01.csv"
    $csv = Import-Csv "C:\temp\AzureAD-GRP-00.csv"
    if (Test-Path $FileName) 
    {
      Remove-Item $FileName
    }
    foreach ($Line in $csv)
    {
        $GroupName = $line.ObjectId
    Get-AzureADGroupMember -ObjectId $GroupName | select DisplayName,UserPrincipalName,GivenName,Surname,Mail,MailNickName,TelephoneNumber,Department,PhysicalDeliveryOfficeName,AccountEnabled,UserType,ObjectId | Export-Csv -Path "C:\Temp\Outputfile-01.csv" -NoTypeInformation -Append
    }

    I need the following data for Groups
    ObjectId,Description,DirSyncEnabled,DisplayName,LastDirSyncTime,Mail,MailNickName,MailEnabled,SecrityEnabled

    and the following data for Group members
    DisplayName,UserPrincipalName,GivenName,Surname,Mail,MailNickName,TelephoneNumber,Department,PhysicalDeliveryOfficeName,AccountEnabled,UserType,ObjectId

    In following format
    Groups... Group members...
    Acounting User1
    Acounting User2
    Acounting User3
    Contracts User1
    Contracts User2
    Staff User1
    Staff User2
    Staff User3
    Staff User4

    Thursday, March 7, 2019 6:34 PM

Answers

  • Assumed I've got you right ... from a logic point of view it wouldn't make sense to store these data together in one table. They are actually hierarchical data and you try to save it as structured data. This will probably end up in a big mess or will be at least hard to use. ;-)

    You would have to store the complete information about a group for every member of this group again and again and again. That's why the Active Directory is a database and not a table. ;-)

    To have the member information combined with the groupname you could try something like this:

    $result = foreach ($Line in $csv) {
        $GroupName = $line.ObjectId
        Get-AzureADGroupMember -ObjectId $GroupName | 
            Select-Object -Property @{Name = 'GroupName'; Expression = {$GroupName}}, DisplayName, UserPrincipalName, GivenName, Surname, Mail, MailNickName, TelephoneNumber, Department, PhysicalDeliveryOfficeName, AccountEnabled, UserType, ObjectId
    }
    $result | Export-Csv -Path "C:\Temp\Outputfile-01.csv" -NoTypeInformation -Append


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''


    • Edited by BOfH-666 Friday, March 8, 2019 2:49 AM
    • Marked as answer by Flatdog-Dave Monday, March 11, 2019 7:37 PM
    Friday, March 8, 2019 2:47 AM

All replies

  • Assumed I've got you right ... from a logic point of view it wouldn't make sense to store these data together in one table. They are actually hierarchical data and you try to save it as structured data. This will probably end up in a big mess or will be at least hard to use. ;-)

    You would have to store the complete information about a group for every member of this group again and again and again. That's why the Active Directory is a database and not a table. ;-)

    To have the member information combined with the groupname you could try something like this:

    $result = foreach ($Line in $csv) {
        $GroupName = $line.ObjectId
        Get-AzureADGroupMember -ObjectId $GroupName | 
            Select-Object -Property @{Name = 'GroupName'; Expression = {$GroupName}}, DisplayName, UserPrincipalName, GivenName, Surname, Mail, MailNickName, TelephoneNumber, Department, PhysicalDeliveryOfficeName, AccountEnabled, UserType, ObjectId
    }
    $result | Export-Csv -Path "C:\Temp\Outputfile-01.csv" -NoTypeInformation -Append


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''


    • Edited by BOfH-666 Friday, March 8, 2019 2:49 AM
    • Marked as answer by Flatdog-Dave Monday, March 11, 2019 7:37 PM
    Friday, March 8, 2019 2:47 AM
  • Thank you for assistance. This is for Exec management that do not know anything about IT, they want an excel document that they can filter by group or user to review the membership.
    Monday, March 11, 2019 7:35 PM
  • This is for Exec management that do not know anything about IT, they want an excel document that they can filter by group or user to review the membership.

    Ha ha ...  I know what you mean. But sometimes we tech-savvy people have to stand upright and have to say: "No, not this way!"  ;-)  :-D


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''


    • Edited by BOfH-666 Monday, March 11, 2019 11:02 PM
    Monday, March 11, 2019 11:01 PM