Adding Detailed License Assignment Reporting

The Microsoft 365 licensing report script is possibly the most popular PowerShell project I’ve published. Given the amount of money that organizations can lay out on Microsoft 365 licenses, I guess people like keeping an eye on where the pennies get spent.

Over the past few months, I’ve responded to several requests for enhancements, such as highlighting license costs for disabled accounts, adding support for a cost center analysis, and so on. More requests keep on coming in, the latest being the desire to be able to report license costs per company within a tenant that supports several operating companies.

Previously, I’d been asked to include a line-by-line (or rather license-by-license) report per user. At the time, I didn’t see much point in doing this, but further reflection made me think that it would be good to output a list of individual license assignments that administrators could slice and dice to meet their needs.

Detailed License Assignment Report

V1.94 of the Microsoft 365 licensing report script does just that. Each license assignment, direct or via group-based licensing, is captured in an array called $DetailedLicenseReport. The report script creates the array automatically. To illustrate how the information can be used, if the $DetailedCompanyAnalyis variable is $true (the variable is set to $true in the script), the report script generates a detailed report about license assignments for each company found in the tenant (Figure 1). Each license is listed along with its assignees and the monthly cost of the licenses.

Obviously, creating such a report depends on accurate values in the company property of user accounts. If you didn’t want to report by company, it would be easy to change the code to create a detailed report of license assignments by department or cost center.

Removing Expired Licenses

The new version of the Microsoft 365 Licensing Report script also addresses expired licenses. When a subscription for a license comes to an end, it’s possible that some accounts have assignments for expired licenses. Eventually, Microsoft 365 removes the expired subscription and the expired licenses, but until this happens it’s possible that the report would include detailed of expired licenses, which is not what anyone would want.

I discovered this possibility when my tenant replaced some Office 365 E5 licenses with Office 365 with Office 365 E5 EEA (No Teams) licenses. This is one of the licenses created without a Teams service plan in an attempt to satisfy anti-competition concerns of the European Union. Microsoft subsequently decided to decouple Teams from all Office 365 and Microsoft 365 products, and new customers can no longer buy licenses that include Teams.

In any case, my tenant has some of the Office 365 E5 EEA (No Teams) licenses and separate Microsoft Teams EEA licenses, but some of the licenses from the older Office 365 E5 subscription still turned up on the report. This doesn’t happen anymore because the script now checks licenses against current subscriptions to remove any trace of expired subscriptions.

Downloading and Using the Microsoft 365 Licensing Report Script

V1.94 of the Microsoft 365 licensing report script is available from GitHub. If you haven’t used the script before, I encourage you to read the original article that launched me on this path and another article describing how the script learns about the costs of individual licenses. Things will become much clearer when you understand the basics of Microsoft 365 licensing, SKUs, products, service plans, and how to generate the CSV files used by the script.

Remember, this is all done with PowerShell and some calls to the Microsoft Graph to find information about the licenses assigned to users. No black magic is used. A script that’s over 700 lines long might seem intimidating, but many of the lines are blank or comments. Like any other PowerShell script, have fun amending the code to meet your needs!

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 (and write scripts like the Microsoft 365 Licensing report).

I’m always on the lookout for tools that might help tenant administrators understand more about the technology they manage. The EntraExporter tool is an example of the kind of utility that I consider to be both valuable and interesting.

EntraExporter is a community-developed PowerShell module designed to export information about the objects and policies in an Entra ID instance for a tenant to JSON files. It’s a way of capturing information about objects like user accounts, groups, administrative units, organization branding, subscriptions, and policies to record of current settings. This is not a backup product, but it is an excellent way of noting the exact configuration of an Entra ID tenant at a point in time.

Installing EntraExporter

To install EntraExporter, run the Install-Module command (this assumes that the PowerShell gallery is a trusted repository). I used this command rather than the example in the documentation:

Install-Module EntraExporter -Scope Allusers

I always install PowerShell modules with Scope AllUsers to force PowerShell to put the module files in $env:ProgramFiles\PowerShell\Modules. From PowerShell 6 onward, Install-Module installs modules in $HOME\Documents\PowerShell\Modules if no scope is defined. This is fine unless you redirect Windows known folders to OneDrive, in which case you end up with module files in OneDrive. The script I wrote to update PowerShell modules used by Office 365/Microsoft 365 installs and updates modules in $env:ProgramFiles\PowerShell\Modules.

The EntraExporter team recommends that you use PowerShell 7 to run the tool.

Running EntraExporter

EntraExporter uses the Microsoft Graph PowerShell SDK to extract information from Entra ID. As the tool runs interactively, it uses delegate permissions, which is fine because the tool only exports information. However, EntraExporter needs a bunch of permissions to access the different objects and policies it processes, so the connect command is:

Connect-MgGraph -Scopes 'Directory.Read.All', 'Policy.Read.All', 'IdentityProvider.Read.All', 'Organization.Read.All', 'User.Read.All', 'EntitlementManagement.Read.All', 'UserAuthenticationMethod.Read.All', 'IdentityUserFlow.Read.All', 'APIConnectors.Read.All', 'AccessReview.Read.All', 'Agreement.Read.All', 'Policy.Read.PermissionGrant', 'PrivilegedAccess.Read.AzureResources', 'PrivilegedAccess.Read.AzureAD', 'Application.Read.All'

The SDK seeks consent for the permissions when you run the command to connect:

Connect-EntraExporter

The signed in user running EntraExporter must grant consent to the requested permissions to access the data (Figure 1). Again, consenting to the requested set of permissions is fine, if you remember that the service principal for the SDK retains consent to use those permissions in future. I’ve written about the way that the SDK accrues Graph permissions over time and possible solutions.

One thing I do not like about the Microsoft Graph PowerShell SDK is the way that its enterprise app proclaims itself to be “unverified.” Any Microsoft app in widespread use should be verified to give tenant administrators more confidence about the app’s provenance.

EntraExporter can run in an Azure Automation runbook. To make this possible, make sure that:

• The Azure Automation account has the necessary permissions (you can use a managed identity to connect to the Graph SDK).
• The EntraExporter module is a resource available to the Azure Automation account.
• The export location is accessible on the network.

Exporting Entra ID Information

With all the necessary permissions in place, I ran the Export-Entra script with the All parameter to export as much directory information as possible. The documentation notes that “B2C, B2B, Static Groups and group memberships, Applications, Service Principals, Users, Privileged Identity Management (built in roles, default roles settings, non-permanent role assignments)” are not exported by default.

Export-Entra -Path 'C:\EntraID\' -All

Various filters are available to select the exact directory information to export, but I wanted to see everything!

How EntraExporter Works

All the EntraExporter code is available in GitHub for your perusal. A quick review identified that the driving force behind the export is the schema defined in Get-EEDefaultSchema.ps1, which tells the exporter the types of objects to export and how to export them. For instance, here’s the definition for user accounts:

# Users
        @{
            GraphUri = 'users'
            Path = 'Users'
            Filter = $null
            QueryParameters = @{ '$count' = 'true'; expand = "extensions" }
            ApiVersion = 'beta'
            Tag = @('All', 'Users')
            DelegatedPermission = 'Directory.Read.All'
            ApplicationPermission = 'Directory.Read.All'
        }

Apart from the slight glitch obvious in Figure 1 (reproducible in the Graph Explorer), everything went smoothly when running an export. The time taken to process an export depends on how many objects are in a tenant directory, particularly groups and users (because they tend to be most numerous). Running a full export can take time because of the need to enumerate group memberships and details of service principals. For a small to medium tenant, expect that everything will be done in 10-15 minutes.

The export results in a set of folders in the target location. In Figure 3, you can set the set of folders (one for each type defined in the schema). The content of each folder are the JSON files generated by EntraExporter. If there are many objects, the JSON output for individual objects are in their own folder. This is what you see in Figure 3, where each user object has a folder named after the user account object identifier.

Opening a JSON file reveals the properties of an object. Figure 4 shows the JSON file for a user object viewed through Visual Studio Code.

Not Perfect But Entra Exporter’s a Nice Tool to Have

No doubt some will consider Entra Exporter a simple tool of little use because it doesn’t come with features like the ability to reconstruct an object from the exported data. But that’s missing the point. Many organizations have written their own versions of Entra Exporter to capture configurations because they need this data for different reasons (auditing, change control, etc.). The advantage of Entra Exporter is that a tool is available for free that is written in PowerShell and therefore very customizable if it doesn’t meet your exact needs.

Insight like this doesn’t come easily. You’ve got to know the technology and understand how to look behind the scenes. Benefit from the knowledge and experience of the Office 365 for IT Pros team by subscribing to the best eBook covering Office 365 and the wider Microsoft 365 ecosystem.

Creating Teams from Exchange Online DDLs

After writing about the recent revamp of Exchange Online dynamic distribution lists, I was asked if it was possible to create a team from the membership of a dynamic distribution list. The answer is that the steps are straightforward to create a static Microsoft 365 group. Things get more complicated if you contemplate using a dynamic Microsoft 365 group.

Available in both Exchange Online and Exchange Server, dynamic distribution lists are very powerful. That is, if the organization directory is well-maintained with details about people, job titles, department names, offices, country, and so on. The membership of dynamic distribution lists can include any kind of mail-enabled recipient, including other groups. And that’s the first challenge to face: the Microsoft 365 groups used by Teams support a flat membership (no nested groups) composed solely of accounts belonging to the host organization (members and guests): only user mailboxes can migrate to become members of a target Microsoft 365 group.

The second challenge comes into play if you decide that the target Microsoft 365 group should have dynamic membership. The issue here is that dynamic distribution lists use filters executed against Exchange Online’s directory while dynamic Microsoft 365 groups use filters based on Azure AD. Different filters, different syntax, and different properties. More on this later.

Converting a Dynamic Distribution List to a Team with Static Membership

Starting with the simple issue of finding the members of a dynamic distribution list and using this information to create a new Microsoft 365 group, the steps are straightforward:

• Identify the source dynamic distribution list.
• Get the members of the dynamic distribution list and throw away any that can’t be members of a Microsoft 365 group.
• Check that the owner of the source dynamic distribution list is a valid mailbox.
• Create the new Microsoft 365 group using properties like name and description inherited from the source dynamic distribution group. The person who manages the dynamic distribution list becomes the owner of the Microsoft 365 group.
• Add the members to the new Microsoft 365 group to the membership.
• Team-enabled the new Microsoft 365 group.

The script I created is available in GitHub. Normal caveats apply: the code works but it doesn’t have much error checking. It’s there to prove a principle, not be an off-the-shelf solution.

Finding the Source

Multiple ways exist to identify a source dynamic distribution list. This example prompts the user to select one. The code could become a lot more complex to allow the user to make a mistake and select from a numbered list, and so on, but for the purpose of the example all we want is the object identifier for a valid dynamic distribution list:

$InputDDL = Read-Host "Enter the name of the Dynamic Distribution List to convert to a Microsoft 365 Group"
[array]$SourceDDL = Get-DynamicDistributionGroup -Identity $InputDDL -ErrorAction SilentlyContinue

If (!($SourceDDL)) {Write-Host ("Sorry! We can't find the {0} dynamic distribution list" -f $InputDDL); break}
If ($SourceDDL.Count -gt 1) {
   CLS
   Write-Host "We found multiple matching dynamic distribution lists"
   Write-Host "-----------------------------------------------------"
   Write-Host " "
   $SourceDDL | Format-Table DisplayName, Alias, PrimarySMTPAddress
   Write-Host " "
   Write-Host "Please try again..."; break }

[string]$SourceDDLId = $SourceDDL.ExternalDirectoryObjectId

Two methods exist to return the membership of the dynamic distribution list:

• Run Get-Recipient using the filter stored in the dynamic distribution list.
• Use the new Get-DynamicDistributionGroupMember cmdlet.

The first method resolves against the Exchange directory and its results are up to date. The second fetches membership data as at the last time Exchange processed the list (more information here). After retrieving the membership using the chosen method, we apply a filter to extract mailboxes.

# Now that we have a source DDL, let's get its membership
[array]$SourceMembers = Get-Recipient -RecipientPreviewFilter (Get-DynamicDistributionGroup -Identity $SourceDDLId).RecipientFilter
# could also be 
# [array]$SourceMembers = Get-DynamicDistributionGroupMember -Identity $SourceDDL.Id
# Throw away anything but user mailboxes because that's all a Microsoft 365 group supports
[array]$ValidMembers = $SourceMembers | ? {$_.RecipientTypeDetails -eq "UserMailbox"}

The next piece of code establishes the owner of the new group. Microsoft 365 groups must have an owner, so if the ManagedBy property of the source list results in an invalid result (for instance, it’s empty), we need to assign ownership to a default account. One way of doing this is to find the set of Exchange administrators for the organization and select one of them, which is done here using the Get-MgDirectoryRoleMember cmdlet from the Microsoft Graph PowerShell SDK and filtering out any service principals assigned the Exchange administrator role. You could simplify the script by hardcoding a default group member.

# We've got to assign an owner to the new Microsoft 365 group, so we need to have a default in case the source DDL doesn't have an owner
# Find the set of accounts that are Exchange admins (you can also use Get-AzureADDirectoryRoleMember here)
[array]$ExoAdmins = Get-MgDirectoryRoleMember -DirectoryRoleId "53add08e-5b0c-4276-a582-9ce02fb6c947" | Select Id, AdditionalProperties 
# Throw away any service principals which might have the Exchange Admin role
$ExoAdmins = $ExoAdmins | ? {$_.AdditionalProperties.'@odata.type' -eq '#microsoft.graph.user'} | Select -ExpandProperty Id
# Select the first and use them as the default owner
$ExoDefaultAdmin = Get-MgUser -UserId $ExoAdmins[0] | Select -ExpandProperty UserPrincipalName
# Check that the group owner is a mailbox
$GroupOwner = Get-ExoMailbox -Identity $SourceDDL.Managedby -ErrorAction SilentlyContinue
# If it's null or something weird like a shared mailbox, use the default owner
If (($GroupOwner -eq $Null) -or ($GroupOwner.RecipientTypeDetails -ne "UserMailbox")) {
   $GroupOwner = $ExoDefaultAdmin }
Else {
   $GroupOwner = $GroupOwner.PrimarySmtpAddress
  }

# Populate other group properties
$AliasDDL = $SourceDDL.Alias + "M365"
$GroupDisplayName = $SourceDDL.DisplayName + " (Group)"

Creating the New Group and Team

With everything ready, we can go ahead and create the new Microsoft 365 Group, add the members, and team-enable the group. All the members can be added with a single Add-UnifiedGroupLinks command because we have an array of email addresses. Exchange processes each item in the array and adds it as a member.

# Create the new Microsoft 365 Group
Write-Host "Creating the new Microsoft 365 group..."
$Description = "Created from the " + $SourceDDL.DisplayName + " dynamic distribution list on " + (Get-Date -Format g)
$NewGroup = New-UnifiedGroup -DisplayName $GroupDisplayName –AccessType Private -Alias $AliasDDL -RequireSenderAuthenticationEnabled $True -Owner $SourceDDL.ManagedBy -AutoSubscribeNewMembers -Notes $Description
# Add the members to the group
Write-Host "Adding members from the dynamic distribution list to the Microsoft 365 group..."
Add-UnifiedGroupLinks -Identity $NewGroup.ExternalDirectoryObjectId -LinkType Members -Links $ValidMembers.PrimarySmtpAddress
Write-Host "Enabing Microsoft Teams for the Microsoft 365 group..."
New-Team -Group $NewGroup.ExternalDirectoryObjectId

The code doesn’t add a sensitivity label, so if you use these to apply container settings to groups and teams, you should add the label when creating the new group by passing the identifier for the selected label in the SensitivityLabel parameter.

That’s it. We have a new team built from the membership of a dynamic distribution list. The code is straightforward and works without a hitch, but if we throw dynamic membership for the Microsoft 365 group/team into the equation, things become much more complex. I’ll cover that subject in another post.

Learn about Teams, Exchange Online, and the rest of Office 365 by subscribing to the Office 365 for IT Pros eBook. Use our experience to understand what’s importance and how best to protect your tenant.

Serving the PF Hierarchy

An Office 365 tenant can support up to 1,000 public folder mailboxes, but only 100 of the mailboxes can serve the public folder hierarchy to clients. Each of those mailboxes can support connections from up to 2,000 active users, meaning that the maximum supported connected population is 200,000 users. This is enough for most organizations but might become an issue in the largest Office 365 tenants.

Exchange Online Reduces the Load

When Outlook desktop connects to Exchange, AutoDiscover returns a list of resources to which the mailbox can connect. Among these resources is a public folder mailbox (see below)

Controlled Connections

A recent change in Exchange Online allows you to reduce the overall load on public folders, you can do so by removing public folders from the view of mailboxes that don’t need to access them. This can be controlled at an organizational or mailbox level.

By default, every mailbox in a tenant can access public folders, so the PublicFolderShowClientControl setting in the organization configuration is $False. In other words, you’re not interested in controlling public folder access on a per-mailbox level. If you update the configuration and change the setting to $True, AutoDiscover checks the PublicFolderClientAccess setting for the mailbox to know if it should include public folder information in the data returned to Outlook. If the setting is $True, AutoDiscover returns public folder information. If $False, it does not.

To change the setting for a mailbox, run the Set-CASMailbox cmdlet. For example:

Set-CASMailbox -Identity James.Ryan -PublicFolderClientAccess $True

To set the organization configuration, run the Set-OrganizationConfig cmdlet. For example:

Set-OrganizationConfig -PublicFolderShowClientControl $True

After changing the setting, it takes a little time for cached information to clear and the new setting to be effective. You should expect to see public folders appear or disappear (depending on the setting) from Outlook within an hour of an update. Because these settings control how AutoDiscover behaves, it is not dependent on any specific version of Outlook. However, the settings do not affect OWA as it does not use AutoDiscover to retrieve resource information.

The default value for PublicFolderClientAccess is $False, so it is not a good idea to update the organization configuration and set PublicFolderShowClientControl to $True unless you first update the mailboxes that you want to continue accessing public folders. For instance, you might decide that only users in the U.K. should use public folders, you can run some PowerShell to find those mailboxes and update the setting. For instance:

Get-ExoMailbox -RecipientTypeDetails UserMailbox -Filter {UsageLocation -eq "United Kingdom"} | Set-CASMailbox -PublicFolderClientAccess $True

Microsoft has not said yet if this change will be ported to Exchange 2016 or Exchange 2019. I expect it to be, but you never know. Their official post about this topic doesn’t mention this point.

Public folders have a home in Chapter 8 of the companion volume for the Office 365 for IT Pros eBook. We bundle the companion volume with the main book if you buy from Gumroad.com, but you have to buy it separately for Kindle.