Generate a HTML Report of Managers and Direct Reports with the Graph SDK

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).

Outlook Org Explorer lists no direct reports for a manager
Figure 1: Outlook Org Explorer lists no direct reports for a manager

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.

Reporting managers and their direct reports

Azure AD Managers
Figure 2: Reporting managers and their direct reports

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.

6 Replies to “Generate a HTML Report of Managers and Direct Reports with the Graph SDK”

  1. Funny, I just happened to finish a script for this too.
    1. Pulls users with an employeeID
    2. If the “Get-MgUserDirectReport” returns “True”, I save all the direct reports in a variable.
    3. I loop through the direct reports for that manager to verify at least one of them is employeType “Employee” or “Temp”. If it returns 1 result, it exits the loop for that manager and adds them to the manager group. Then goes to the next manager to check their direct reports. I check for employeeType because we assign managers to service accounts in AD but they are not real humans.
    Takes like 10 minutes to run and finds 540 managers.
    4) lastly, it removes any users from the group if the user isn’t in the result above.

  2. “Finds user accounts with at least one assigned license. This step filters out accounts created for purposes like room and shared mailboxes.”

    Well, it may filter out most of them. Microsoft Teams Rooms license (currently in Basic, Pro, and Premium flavors) is assigned to room mailboxes when you have in-room meeting hardware. There may also be service accounts with subscriptions that have a manager listed. As always, YMMV but this is a great starting point. Thanks Tony.

      1. I added these lines to remove the accounts used by room and shared mailboxes (the script also needs a Connect-ExchangeOnline command).

        # The accounts used for Room and shared mailboxes can hold licenses (for example, for Teams Rooms Devices or to get a larger quota for a shared mailbox)
        # so we remove these accounts here
        $OriginalUserAccounts = $Users.Count
        Write-Host “Finding Azure AD accounts used for shared and room mailboxes…”
        [array]$NonUserAccounts = Get-ExoMailbox -RecipientTypeDetails SharedMailbox, RoomMailbox -ResultSize Unlimited | Select-Object UserPrincipalName, ExternalDirectoryObjectId
        Write-Host “Removing non-user accounts from set to be processed…”
        $Users = $Users | Where-Object {$_.Id -notin $NonUserAccounts.ExternalDirectoryObjectId}
        $RemovedAccounts = $OriginalUserAccounts – $Users.Count
        Write-Host (“Proceeding to process {0} user accounts after removing {1} room and shared mailbox accounts” -f $Users.Count, $RemovedAccounts)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.