Creating Azure groups from CSV matrix

PowerShell Help
1

Hi all,

I have a simple CSV matrix - Group names in Column 1, Usernames in Row A, and an X for each group a user should be in.

image

What I would like is to create those groups in Azure, with the relevant members in please?
Even if it could be condensed down to two columns, that would be great, so a CSV with

Group Name,Username
Group1,Alice;Bob
Group2,Bob;Dave

etc

Thanks :slight_smile:

2

You can use your two column csv exactly as you example, though I personally would run Group Name together rather than have a space. GroupName vs Group Name.

Group Name,Username
Group1,Alice;Bob
Group2,Bob;Dave

There are a couple things you will have to do in your script.

  1. You will need to convert the value of Username from a single value of names separated by a semi colon to something you can feed to Add-AzureADGroupMember one member at a time.

  2. You will need to convert usernames into their Azure ObjectIDs. Add-AzureADGroupMember uses the object ID of both the group you want to populate to identify it, and the members you want to put into the group.

Example script


# Import csv with group names and members
$groupData = Import-Csv -Path C:\PSS\csv\GroupAndUsers.csv -Encoding UTF8

foreach($group in $groupData){

     # Convert member list from a signle value to an array where each name is it's own value 
     $members = $group.Members -split ';'
    
     # Create the new groups
     New-AzureADGroup -DisplayName $group.GroupName -MailEnabled $false -SecurityEnabled $true -MailNickName 'none'

     foreach($member in $members){
        
        # Get the objectIDs.  See Add-AzureADGroupMember docs
        $objectID = Get-AzureADGroup -SearchString $group.Group
        $refObjectID = Get-AzureADUser -SearchString $member
        Add-AzureADGroupMember -ObjectId $objectID.objectID -RefObjectId $refObjectID.objectID

    }
    
}

Source csv for script

GroupName,Members
Group01,bugs.bunny@acme.com;cool.cat@acme.com;daffy.duck@acme.com
Group02,donald.duck@acme.com;elmer.fudd@acme.com;fred.flintstone@acme.com
Group03,cool.cat@acme.com;elmer.fudd@acme.com;george.jetson@acme.com

There may be other clever ways to accomplish this, this is but one way.

There is something else to be aware of when trying to create groups and populate them as the next step in your script. It is not uncommon for there to be a delay between when you create the group, and you are able to query it in order to add members. Since you can’t predict when this will happen or how long the delay, using start-sleep is not a reliable work around.

I have been experimenting with different ideas for verifying the newly created resource before moving to the next step.

3

Thank you Matt, this works great.

If anyone else is interested, you can condense the spreadsheet in Excel using
=TEXTJOIN(“;”,FILTER($B$1:$E$1, B2:E2=“x”))

Where $B$1:$E$1 is the title row with usernames