Creating a Report From Azure AD Manager and Direct Reports Data with PowerShell

It’s always good to be able to build on the knowledge contributed by someone else. This brings me to a post by Vasil Michev, the esteemed technical editor for the Office 365 for IT Pros eBook. The post covers how to Create an All Managers group in Microsoft 365 and covers how to do this in different ways for different types of group. It brought back some memories of Microsoft’s initiative in April 2017 to auto-generate a Microsoft 365 group for every manager with its membership populated with the manager’s direct report.

Retrieving Azure AD Manager and Direct Reports

In any case, Vasil discussed how the Get-Recipient (but not Get­ExoRecipient) and Get-User cmdlets have a filter to find accounts that have direct reports using the backlink from users to their managers. By definition, these accounts are managers, so you can use the commands as the basis to control the membership of distribution lists, dynamic distribution lists, or Microsoft 365 groups.

Get-Recipient -Filter {DirectReports -ne $Null}
Get-User -Filter {DirectReports -ne $Null}

The only problem is that the output of the two cmdlets is imperfect. The cmdlets find accounts with direct reports, but their results include some accounts that don’t have any direct reports. In my tenant, I found that the cmdlets found three accounts with no direct reports. I believe that these accounts had direct reports at some point in the past, but they don’t now. For instance, when I queried the accounts to see the set of direct reports reported by Get-User, I see a blank:

Get-User -Identity Ben.Owens | Select-Object Name, Manager, DirectReports

Name      Manager      DirectReports
----      -------      -------------
Ben Owens tony.redmond {}

The same is true when viewing details of the account through Exchange address lists, the organization chart in Teams, or the Outlook Org Explorer (Figure 1).

According to message center notification MC492902 (updated 7 February 2023), the Outlook Org Explorer is only available to users with the “Microsoft Viva Suite” or “Microsoft Viva Suite with Glint” licenses, which is why you might not be seeing it. Originally, Microsoft said that the Org Explorer would be available to accounts with Microsoft 365 E3/E5 or Microsoft 365 Business licenses, but they decided to align this feature with the Viva initiative. The Org Explorer is not available for OWA.

My conclusion is that synchronization between Azure AD and Exchange Online leaves some vestige behind in the DirectReports property following the removal of the last direct report for a manager. It’s enough to stop the filter working accurately.

Reporting Azure AD Managers and Direct Reports

Which brings me back to considering how to report the links between managers and employees using the information stored in Azure AD. I covered this ground in an article two years ago, but I didn’t realize the flaw in Get-User at the time, so the script I wrote (available from GitHub) can produce incorrect results. A different approach is needed.

Given that Azure AD is the source of the information, it makes sense to use Graph APIs to retrieve data. I chose to use the Microsoft Graph PowerShell SDK to avoid the necessity to create a registered app.

The new script (also available from GitHub) does the following:

• Finds user accounts with at least one assigned license. This step filters out accounts created for purposes like room and shared mailboxes.
• Use the Get-MgUserManager cmdlet to check each account to see if it has a manager. If not, note this fact.
• Use the Get-MgUserDirectReport cmdlet to see if the account has direct reports. If it does, record the details of the manager’s reports.
• Create an HTML report detailing each manager and their reports.
• At the end of the report, add a section detailing accounts without managers.
• Output the HTML file and a CSV file containing details of managers and reports.

Figure 2 shows some example output. Because the code is PowerShell, it’s easy to tweak it to include other information about each employee.

Go to the Source to Find Azure AD Managers and Direct Reports

It’s never nice to discover that a technique you thought worked well is no longer fit for purpose and it’s necessary to rework a script. The Get-User and Get-Recipient cmdlets return accurate information about managers and direct reports, but only if managers always have at least one report. I guess that’s possible, but it’s better to make sure by using Graph APIs to retrieve data about managers and their direct reports. At least then you’ll know that your reports show the same reporting relationships that surface elsewhere in Microsoft 365.

Support the work of the Office 365 for IT Pros team by subscribing to the Office 365 for IT Pros eBook. Your support pays for the time we need to track, analyze, and document the changing world of Microsoft 365 and Office 365.

PowerShell Tricks Help Get Real Work Done

As dedicated readers of this blog might have noticed, I write quite a few PowerShell scripts. Notice that I use the word “write” instead of “develop.” That’s because my time of professional development lie in the days VAX BASIC and VAX COBOL in the 1980s. Today, I write PowerShell for fun to illustrate concepts and principals. I don’t write code that is bulletproof or a thing of beauty. In fact, I represent the archetype hacker (in its original meaning).

Upgrading Scripts

All of which means that I am constantly discovering techniques when I go looking for solutions to problems. Some would say that I am always on the lookout for new PowerShell tricks, but that stretches the point.

Take the Microsoft 365 Groups and Teams report script (Figure 1). I have worked on many iterations of this script since 2016 (the latest version is 5.10). The code evolved from using Exchange Online cmdlets to interrogate group mailboxes to the current version that’s mostly based on Graph API requests and is much faster. Some people have run the report for over 20,000 groups.

In some cases, the changes I make are driven by need. For example, the Get-ExoMailboxFolderStatistics and Get-MailboxFolderStatistics cmdlets from V3.01 of the Exchange Online management module have an annoying habit of returning system objects (arrays) for the date of last received email and the number of items in the inbox folder of group mailboxes. In some cases, the cmdlets report that a group mailbox has two dates for the last received email. This is crazy stuff because the Get-MailboxFolderStatistics cmdlet has been around since 2006 and Microsoft really shouldn’t be screwing it up at this point. The solution isn’t based on any PowerShell tricks. Instead, the script now includes code to handle the erratic behavior of Exchange Online cmdlets.

Outputting Email Addresses on Separate Lines

In other cases, I make changes in response to requests. One recent request was to include the email address of a group owner, which I did in V5.8. Then someone asked if they could have the email addresses of all group owners output in the report. Easy, I said, edit your copy of the script to get the owner display names for each group and join the names together into a string. But that didn’t work because they needed each email address to be on a separate line to import the data into Power BI.

Some search brought me to a May 2006 blog written by Jeffrey Snover, who was then the architect evangelizing PowerShell within Microsoft. Jeffrey subsequently became a Microsoft Fellow before leaving to go to Google in the Fall of 2022, just after he wrote the foreword for the 2023 edition of the Office 365 for IT Pros eBook.

Jeffrey’s blog covered what he called the “Ouptut Field Sperator,” or OFS, defined as a special variable containing the string used as a separator when PowerShell converts an array into a string. By default, OFS is a space, but you can change it. This knowledge and some searching brought me to another blog that explained how to use escape characters in the OFS. New line is an escape character, so presto, a solution appeared.

Take this example where we fetch the owners of a group and store the data in an array:

$GroupId = (Get-MgGroup -Filter "displayname eq 'Ultra Fans'").Id
[array]$GroupData = Get-MgGroupOwner -GroupId $GroupId
[array]$GroupOwners = $GroupData.AdditionalProperties.mail

The email addresses of the group owners are now in the array:

Tony.Redmond@office365itpros.com
Ben.James@Office365itpros.com
Chris.Bishop@office365itpros.com

If I convert the array to a string, the output is a line of names separated by spaces:

[string]$Owners = $GroupOwners
$Owners
Tony.Redmond@office365itpros.com Ben.James@Office365itpros.com Chris.Bishop@office365itpros.com

But if I define the special $OFS variable to be a new line character, I get this:

$OFS="`n`n"
[string]$ContactEmail = $GroupOwners
$ContactEmail
Tony.Redmond@office365itpros.com

Ben.James@Office365itpros.com

Chris.Bishop@office365itpros.com

Problem solved and the output has email addresses on separate lines. In some cases, a carriage return and new line might be better for the output. To do this, set $OFS to “`r`n” (see this post). Either way, being able to change the output space character is a nice example of the kind of PowerShell tricks and techniques that you can find on the internet.

Overcoming Export-CSV Limitations

I often use the Export-CSV cmdlet to export report data from PowerShell to a CSV file where people can open and work on the data using tools like Excel. Recently, a French MVP reported that the Teams and Groups Activity report script worked great, but the CSV output dropped the accented characters used in French (like é) from essential information like group names.

It’s not at all surprising that this should happen. CSV files are comma delimited plain-text ASCII files and by default, the cmdlet generates 7-bit ASCII output (other encoding schemes are available). If you want to more precise control over formatting and extended characters, you need something more sophisticated, which is where the ImportExcel module comes in. Exporting to an XLSX file preserves the formatting and group names appear in all their glory. The lesson learned here is that Export-CSV does a good but limited job. If you work with non-ASCII data, seek another solution.

Time to write some code and discover a few more PowerShell tricks to investigate!

Moving the Microsoft 365 Groups Report Script from Azure AD to the Graph SDK

Two years ago, I wrote a script to report the membership of Microsoft 365 groups and teams. The script processes user accounts to find accounts they are members of and generates detailed and summary reports.

As it turned out, I ended up writing two versions of the script: one using standard PowerShell cmdlets from the Exchange Online PowerShell and Azure AD modules, the other using Graph API requests. The Graph version is faster but some people don’t like Graph-based scripts because of the requirement to register an Azure AD app, consent to permissions, and so on.

Time and technology march on and it’s time to review any script that uses the Azure AD module because of its imminent deprecation in June 2023. Imminent sounds like a strange word to use about something that will happen in five and a half months but time slips away and there’s always something different to be done. I had the time and was already committed to upgrading the script to report “stale” guest accounts, so it seemed like a good idea to plunge into the code and replace the Azure AD and Exchange Online cmdlets with the Microsoft Graph PowerShell SDK.

Scripts to Process Azure AD Accounts and Groups

I’ve come to the view that it’s now best to use the SDK for anything to do with Azure AD accounts and groups. Because the Exchange Online management module contains cmdlets that operate against Microsoft 365 groups, I could have used those cmdlets in the script, but it’s easier when a script uses just the one module.

The two versions of the scripts are available from GitHub:

• Graph API-based version.
• Microsoft Graph PowerShell SDK-based version.

Changes to Upgrade to the SDK

Among the changes made to upgrade the script were:

• Connect to the Graph with Connect-MgGraph, setting appropriate permissions and selecting the beta endpoint.
• Replace the Exchange Get-Organization cmdlet with SDK Get-MgOrganization to fetch tenant name.
• Replace Get-AzureADUser with Get-MgUser. The filter used with Get-MgUser fetches only licensed accounts (excludes guests and accounts used for room and resource mailboxes). Replacing Get-AzureADUser is one of the more common changes that people will make as they upgrade scripts. See this article for more information.
• Replace Get-UnifiedGroup with Get-MgTeam to fetch a list of team-enabled groups.
• Replace Get-Recipient with the Graph MemberOf API to find the set of groups a user is a member of. The Invoke-MgGraphRequest cmdlet runs the Graph query to remove the need to register an app.
• Use Get-MgGroupOwner to return group owners instead of fetching this information from the ManagedBy property available with Get-UnifiedGroup.
• Other miscellaneous changes of the type that you find you make when reviewing code.

The code generates the same reports as before (HTML report  – Figure 1 – and two CSV files). All the change is in the plumbing. Nothing is different above water.

Unpredictable Upgrade Effort

It’s hard to estimate how long it will take to upgrade a script to use the Microsoft Graph PowerShell SDK. Factors include:

• The number of lines of code in the script.
• The number of Azure AD cmdlets to replace.
• How easy it is to replace a cmdlet. Microsoft publishes a cmdlet map to guide developers. The caveat is that sometimes the suggested SDK cmdlet generates different output to its Azure AD counterpart, meaning that some additional processing is necessary. Dealing with group members and owners are examples where changes are likely.

One thing’s for sure. The sooner an organization starts to inventory and upgrade its scripts, the sooner the work will be done and the less likely the effort will run into a time crunch when Microsoft deprecates the Azure AD and MSOL modules. Deprecation doesn’t mean that cmdlets necessarily stop working (some will, like the license management cmdlets). Support ceases and no further development of the deprecated modules happen, and that’s not a state you want for operational scripts. Time’s ebbing away…

So much change, all the time. It’s a challenge to stay abreast of all the updates Microsoft makes across Office 365. Subscribe to the Office 365 for IT Pros eBook to receive monthly insights into what happens, why it happens, and what changes to PowerShell modules mean for your tenant.