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.
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).
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.
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.
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.
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.
“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.
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)
{"id":null,"mode":"button","open_style":"in_modal","currency_code":"EUR","currency_symbol":"\u20ac","currency_type":"decimal","blank_flag_url":"https:\/\/office365itpros.com\/wp-content\/plugins\/tip-jar-wp\/\/assets\/images\/flags\/blank.gif","flag_sprite_url":"https:\/\/office365itpros.com\/wp-content\/plugins\/tip-jar-wp\/\/assets\/images\/flags\/flags.png","default_amount":100,"top_media_type":"featured_image","featured_image_url":"https:\/\/office365itpros.com\/wp-content\/uploads\/2022\/11\/cover-141x200.jpg","featured_embed":"","header_media":null,"file_download_attachment_data":null,"recurring_options_enabled":true,"recurring_options":{"never":{"selected":true,"after_output":"One time only"},"weekly":{"selected":false,"after_output":"Every week"},"monthly":{"selected":false,"after_output":"Every month"},"yearly":{"selected":false,"after_output":"Every year"}},"strings":{"current_user_email":"","current_user_name":"","link_text":"Virtual Tip Jar","complete_payment_button_error_text":"Check info and try again","payment_verb":"Pay","payment_request_label":"Office 365 for IT Pros","form_has_an_error":"Please check and fix the errors above","general_server_error":"Something isn't working right at the moment. Please try again.","form_title":"Office 365 for IT Pros","form_subtitle":null,"currency_search_text":"Country or Currency here","other_payment_option":"Other payment option","manage_payments_button_text":"Manage your payments","thank_you_message":"Thank you for supporting the work of Office 365 for IT Pros!","payment_confirmation_title":"Office 365 for IT Pros","receipt_title":"Your Receipt","print_receipt":"Print Receipt","email_receipt":"Email Receipt","email_receipt_sending":"Sending receipt...","email_receipt_success":"Email receipt successfully sent","email_receipt_failed":"Email receipt failed to send. Please try again.","receipt_payee":"Paid to","receipt_statement_descriptor":"This will show up on your statement as","receipt_date":"Date","receipt_transaction_id":"Transaction ID","receipt_transaction_amount":"Amount","refund_payer":"Refund from","login":"Log in to manage your payments","manage_payments":"Manage Payments","transactions_title":"Your Transactions","transaction_title":"Transaction Receipt","transaction_period":"Plan Period","arrangements_title":"Your Plans","arrangement_title":"Manage Plan","arrangement_details":"Plan Details","arrangement_id_title":"Plan ID","arrangement_payment_method_title":"Payment Method","arrangement_amount_title":"Plan Amount","arrangement_renewal_title":"Next renewal date","arrangement_action_cancel":"Cancel Plan","arrangement_action_cant_cancel":"Cancelling is currently not available.","arrangement_action_cancel_double":"Are you sure you'd like to cancel?","arrangement_cancelling":"Cancelling Plan...","arrangement_cancelled":"Plan Cancelled","arrangement_failed_to_cancel":"Failed to cancel plan","back_to_plans":"\u2190 Back to Plans","update_payment_method_verb":"Update","sca_auth_description":"Your have a pending renewal payment which requires authorization.","sca_auth_verb":"Authorize renewal payment","sca_authing_verb":"Authorizing payment","sca_authed_verb":"Payment successfully authorized!","sca_auth_failed":"Unable to authorize! Please try again.","login_button_text":"Log in","login_form_has_an_error":"Please check and fix the errors above","uppercase_search":"Search","lowercase_search":"search","uppercase_page":"Page","lowercase_page":"page","uppercase_items":"Items","lowercase_items":"items","uppercase_per":"Per","lowercase_per":"per","uppercase_of":"Of","lowercase_of":"of","back":"Back to plans","zip_code_placeholder":"Zip\/Postal Code","download_file_button_text":"Download File","input_field_instructions":{"tip_amount":{"placeholder_text":"How much would you like to tip?","initial":{"instruction_type":"normal","instruction_message":"How much would you like to tip? Choose any currency."},"empty":{"instruction_type":"error","instruction_message":"How much would you like to tip? Choose any currency."},"invalid_curency":{"instruction_type":"error","instruction_message":"Please choose a valid currency."}},"recurring":{"placeholder_text":"Recurring","initial":{"instruction_type":"normal","instruction_message":"How often would you like to give this?"},"success":{"instruction_type":"success","instruction_message":"How often would you like to give this?"},"empty":{"instruction_type":"error","instruction_message":"How often would you like to give this?"}},"name":{"placeholder_text":"Name on Credit Card","initial":{"instruction_type":"normal","instruction_message":"Enter the name on your card."},"success":{"instruction_type":"success","instruction_message":"Enter the name on your card."},"empty":{"instruction_type":"error","instruction_message":"Please enter the name on your card."}},"privacy_policy":{"terms_title":"Terms and conditions","terms_body":null,"terms_show_text":"View Terms","terms_hide_text":"Hide Terms","initial":{"instruction_type":"normal","instruction_message":"I agree to the terms."},"unchecked":{"instruction_type":"error","instruction_message":"Please agree to the terms."},"checked":{"instruction_type":"success","instruction_message":"I agree to the terms."}},"email":{"placeholder_text":"Your email address","initial":{"instruction_type":"normal","instruction_message":"Enter your email address"},"success":{"instruction_type":"success","instruction_message":"Enter your email address"},"blank":{"instruction_type":"error","instruction_message":"Enter your email address"},"not_an_email_address":{"instruction_type":"error","instruction_message":"Make sure you have entered a valid email address"}},"note_with_tip":{"placeholder_text":"Your note here...","initial":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"empty":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"not_empty_initial":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"saving":{"instruction_type":"normal","instruction_message":"Saving note..."},"success":{"instruction_type":"success","instruction_message":"Note successfully saved!"},"error":{"instruction_type":"error","instruction_message":"Unable to save note note at this time. Please try again."}},"email_for_login_code":{"placeholder_text":"Your email address","initial":{"instruction_type":"normal","instruction_message":"Enter your email to log in."},"success":{"instruction_type":"success","instruction_message":"Enter your email to log in."},"blank":{"instruction_type":"error","instruction_message":"Enter your email to log in."},"empty":{"instruction_type":"error","instruction_message":"Enter your email to log in."}},"login_code":{"initial":{"instruction_type":"normal","instruction_message":"Check your email and enter the login code."},"success":{"instruction_type":"success","instruction_message":"Check your email and enter the login code."},"blank":{"instruction_type":"error","instruction_message":"Check your email and enter the login code."},"empty":{"instruction_type":"error","instruction_message":"Check your email and enter the login code."}},"stripe_all_in_one":{"initial":{"instruction_type":"normal","instruction_message":"Enter your credit card details here."},"empty":{"instruction_type":"error","instruction_message":"Enter your credit card details here."},"success":{"instruction_type":"normal","instruction_message":"Enter your credit card details here."},"invalid_number":{"instruction_type":"error","instruction_message":"The card number is not a valid credit card number."},"invalid_expiry_month":{"instruction_type":"error","instruction_message":"The card's expiration month is invalid."},"invalid_expiry_year":{"instruction_type":"error","instruction_message":"The card's expiration year is invalid."},"invalid_cvc":{"instruction_type":"error","instruction_message":"The card's security code is invalid."},"incorrect_number":{"instruction_type":"error","instruction_message":"The card number is incorrect."},"incomplete_number":{"instruction_type":"error","instruction_message":"The card number is incomplete."},"incomplete_cvc":{"instruction_type":"error","instruction_message":"The card's security code is incomplete."},"incomplete_expiry":{"instruction_type":"error","instruction_message":"The card's expiration date is incomplete."},"incomplete_zip":{"instruction_type":"error","instruction_message":"The card's zip code is incomplete."},"expired_card":{"instruction_type":"error","instruction_message":"The card has expired."},"incorrect_cvc":{"instruction_type":"error","instruction_message":"The card's security code is incorrect."},"incorrect_zip":{"instruction_type":"error","instruction_message":"The card's zip code failed validation."},"invalid_expiry_year_past":{"instruction_type":"error","instruction_message":"The card's expiration year is in the past"},"card_declined":{"instruction_type":"error","instruction_message":"The card was declined."},"missing":{"instruction_type":"error","instruction_message":"There is no card on a customer that is being charged."},"processing_error":{"instruction_type":"error","instruction_message":"An error occurred while processing the card."},"invalid_request_error":{"instruction_type":"error","instruction_message":"Unable to process this payment, please try again or use alternative method."},"invalid_sofort_country":{"instruction_type":"error","instruction_message":"The billing country is not accepted by SOFORT. Please try another country."}}}},"fetched_oembed_html":false}
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.
“managers are not real humans”? Gosh, that’s a big statement to make…
“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.
The point about Teams rooms devices and licensing is true. https://office365itpros.com/2023/03/29/teams-rooms-devices-licenses/
I shall think about how to filter those room mailboxes out…
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)