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

Copper Contributor

Hello All,

Daily I want to automate the PowerShell script for extracting Power BI Security AD groups using service principal. Few days back we are able to automate the script but suddenly yesterday onwards i am getting the below error. 

PowerShell script I am using to extract Power BI security AD groups which are added to the Workspace as given below

$AppId = "AppID"
$TenantId = "TenantID"
$ClientSecret = "ClientSecret"

$MsalToken = Get-MsalToken -TenantId $TenantId -ClientId $AppId -ClientSecret ($ClientSecret | ConvertTo-SecureString -AsPlainText -Force)

#Connect to Graph using access token
Connect-graph -AccessToken $MsalToken.AccessToken

$Groups = Get-MgGroup -all|Where-Object {($_.DisplayName -like 'a-PBIxx-*')}|Select-Object Id,DisplayName
foreach ($Group in $Groups)
{
$Group_GUID = $Group.Id
$Group_DisplayName = $Group.DisplayName
$GroupMembers = get-mggroupmember -GroupId $Group_GUID -All

foreach ($GroupMember in $GroupMembers)
{
$users = get-mguser -userid $GroupMember.id
$Properties = @{"GroupDisplayName"=$Group_DisplayName;
"MemberDisplayName"=$users.DisplayName;
"MemberEmail"=$users.UserPrincipalName;
"GUID"=$Group_GUID;}


$Obj = New-Object -TypeName PSObject -Property $Properties
Write-Output $Obj | select GUID,GroupDisplayName,MemberDisplayName,MemberEmail |Export-Csv -Path File path/.csv
}
}
Get-Mggroup -all | Where-Object {($_.DisplayName -like 'a-PBIxx-*')} | ? {(Get-Mggroupmember -GroupId $_.Id).count -eq 0 } | Select-Object Id,DisplayName|Export-Csv -Path File path/.csv -NoTypeInformation
$csvData = Import-Csv -Path File path/.csv" | Select-Object @{n='GUID';e={$_.Id}},@{n='GroupDisplayName';e={$_.DisplayName}},MemberDisplayName,MemberEmail
$csvData | Export-Csv -Append -NoTypeInformation -Path "File path/.csv"

 

ynr0225_1-1705915123647.png

Thanks in advance!

4 Replies

@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

Hi @LainRobertson,

 

Thanks for the suggestion, i have ran in PowerShell but i am getting below error before running the script i have installed Microsoft.Graph.beta module. Any suggestions will be helpful.

ynr0225_0-1706012492950.png

Thanks!

 

@ynr0225 

 

Perhaps check your version of Microsoft.Graph.Authentication as even though I'm two versions behind, I see the ClientSecretCredential parameter from the initial error, and it still features in the documentation:

 

 

LainRobertson_0-1706016855605.png

 

It's possible you've got an older version, or even multiple versions installed and the one chosen at run time may be the older version. (One old issue I can recall was the x86 path being searched ahead of the x64.)

 

Anyhow, it's definitely there. That said, I use certificate-based authentication and not client secrets, so it's also possible you might need to tweak the Connect-MgGraph command once you get the initial issue around the unknown parameter resolved.

 

Cheers,

Lain

Hi @LainRobertson,

 

Thanks for the suggestion. After upgrading the Microsoft.graph module, I am able to automate the PowerShell script.

 

Regards,

Nagendra Y