Failed to automate PowerShell script using service principal for Power BI Security AD group

ynr0225 

Hi.

My best guess would be that the StackOverflowException is coming from the loops (i.e. the foreach) statements.

As an side, there's no reason to be using the third-party Get-MsalToken function, since Connect-MgGraph (for which Connect-Graph is simply an alias) is enough for your script.

The script is also quite inefficient in two contexts:

• Querying Graph;
• Memory utilisation.

Querying Graph

You're pulling everything contained in Azure AD into your script and then filtering for what you want. This is called client-side filtering and it scales very poorly as environments grow larger.

Instead, you should be using server-side filtering whenever it's available, as it is for Graph group and user objects. It's generally surfaced through the -Filter parameter.

Imagine your directory contains 50,000 groups and only five of those are Power BI control groups. You'd be pulling 50,000 groups down from Graph and storing them locally only to run the rest of the script against five groups.

By using server-side filtering, you're only pulling down from Graph the five groups you require.

Memory utilisation

Once you pull the entire directory's contents client-side, you're then storing it in a variable, which means the memory manager (the .NET GAC) cannot free up memory should it suffer from memory pressure.

Note: I've ignored the final three lines as they seem to mirror the earlier script contents and suffer from all the same issues.

Based on the script you've provided, I'd go with a different revision that:

• Does not unnecessarily use Get-MsalToken;
• Uses server-side filtering instead of client-side filtering;
• Does not store massive whole-of-directory variables client-side, but instead uses the pipeline as PowerShell was largely intended to be used.

Example script

Note: For this example script, I'm using the richer Microsoft.Graph.Beta.* modules.

# Note: It's not good, secure practice to store client credentials within a script.
$AppId = "AppID";
$TenantId = "TenantID";
$ClientSecret = "ClientSecret";

$CsvFile = ".\somefile.csv";

#Connect to Microsoft Graph.
Connect-MgGraph -TenantId $TenantId -ClientSecretCredential ([pscredential]::new($AppId, (ConvertTo-SecureString -String $ClientSecret -AsPlainText -Force)));

# Fetch the Power BI groups and export the contained users (note, neither this example nor the original perform recursive expansion).
Get-MgBetaGroup -Filter "startsWith(displayName, 'a-PBIxx-')" |
    ForEach-Object {
        $Group = $_;

        Get-MgBetaGroupMember -GroupId ($Group.id) |
            ForEach-Object {
                if ($_.AdditionalProperties.ContainsKey("userType"))
                {
                    $_.AdditionalProperties.Add("id", $_.id);
                    $User = [pscustomobject] ([hashtable] $_.AdditionalProperties);      # This isn't strictly necessary but does make for easier reading in the code the follows.

                    [pscustomobject] @{
                        GroupId = $Group.id;
                        Group = $Group.displayName;
                        UserId = $User.id;
                        UserType = $User.userType;
                        UserName = $User.displayName;
                        Mail = $User.mail;
                        UserPrincipalName = $User.userPrincipalName;
                    }
                }
            }
    } |
        Export-Csv -Path $CsvFile -NoTypeInformation;

Cheers,

Lain