How to Find the Right Set of Azure AD User Objects or Mailboxes to Process with PowerShell

Use Filters to Target Mailboxes and Azure AD Accounts

PowerShell scripts often begin by finding a set of Azure AD user accounts or Exchange mailboxes to process. The classic approach is to run a cmdlet like Get-ExoMailbox or Get-MgUser to find the desired objects. However, things can become a little complicated when you try to retrieve the optimum set. For example:

• Only user mailboxes (exclude shared and room mailboxes).
• Only licensed accounts (focus on Azure AD accounts with licenses to use Microsoft 365 services).

The first set is easily found with:

[array]$Mailboxes = Get-ExoMailbox -RecipientTypeDetails UserMailbox -ResultSize Unlimited

The second with:

Select-MgProfile Beta
[array]$Users = Get-MgUser -Filter "assignedLicenses/`$count ne 0 and userType eq 'Member'" -ConsistencyLevel eventual -CountVariable Records -All

The syntax for the latter example is more complex because it uses a Graph filter to find accounts with at least one license (count is greater than 0) that are members (not guests) of the tenant. It’s also what Microsoft refers to as an advanced query against Azure AD objects, which is why the consistency level parameter is present. The Select-MgProfile command tells the SDK to use the beta endpoint, which is the only way currently available to retrieve license information for Azure AD accounts.

The problem is that accounts used by shared mailboxes and room mailboxes can have licenses. Shared mailboxes need licenses to use an archive or have an increased mailbox quota, while room mailboxes have licenses when they’re used by Teams Rooms devices. Your script might not necessarily want to process these accounts because the intention is to deal with accounts owned by humans rather than rooms or devices.

After creating the array of shared and room mailboxes, it’s easy to filter the users’ array to remove accounts that are not in that array:

[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}

The result is an array holding licensed Azure AD accounts belonging to humans. I use this technique in the script to create an HTML report of managers and their direct reports.

Find Azure AD User Accounts for Employees

Azure AD includes several attributes for employee data. Three important attributes are:

• EmployeeId: A string value containing the employee identifier assigned by the organization. Often this is a number.
• EmployeeHireDate: A date value for when the employee joined the organization.
• EmployeeType: A string value to indicate the type of employee. For instance, you could store values like “Temporary”, “Permanent,” and “Part-time” in this attribute.

The employee attributes are not part of the default set returned by the Graph. You can filter against the attributes as normal, but if you want to see the data, you must specify the attributes in the Graph request. For example, this Get-MgUser command finds member accounts with some value in the EmployeeId attribute without including the value of the attribute in the data returned by the Graph. The comparison against a space is one of the foibles to know about when working with the Graph.

[array]$Employees = Get-MgUser -filter "userType eq 'Member' and EmployeeId ge ' '"
$Employees | Format-Table DisplayName, EmployeeId

DisplayName  EmployeeId
-----------  ----------
Rene Artois
Tony Redmond

To see the employee data, specify the properties for the call to return:

[array]$Employees = Get-MgUser -filter "userType eq 'Member' and EmployeeId ge ' '" -Property Id, displayname, userprincipalname, employeeid, employeehiredate, employeetype

$Employees | Format-Table DisplayName, EmployeeId, EmployeeType, EmployeeHireDate

DisplayName  EmployeeId EmployeeType EmployeeHireDate
-----------  ---------- ------------ ----------------
Rene Artois  111888     Permanent    08/03/2018 00:00:00
Tony Redmond 150847     Permanent    01/01/2011 00:00:00

Unfortunately, the Graph does not support filtering against the employee type or employee hire date properties (see this page for reference). If you want to filter based on the hire date, create the array of employees as shown above, and use a client-side filter. For example, this code finds employees hired within the last ten years:

$CheckDate = (Get-Date).AddDays(-3650)
$Employees | Where-Object {$CheckDate -as [datetime] -lt $_.EmployeeHireDate}

While this command finds accounts with the employee type marked as permanent.

$Employees | Where-Object {$_.EmployeeType -eq "Permanent"}

You’ll also need a client-side filter to use the like, match, and other comparison operators available in PowerShell. Graph requests are limited to eq, and, or, and startswith when evaluating Azure AD user accounts.

Find Azure AD Users with Exchange Custom Attributes

Using any query against Azure AD depends on accurate data being in the queried attributes. My experience is that relatively few Microsoft 365 tenants populate the employee attributes available in Azure AD. This might be because many organizations are hybrid or have other reasons not to use the employee attributes (like not knowing that they’re available), or that other schemes are in use. For instance, organizations that use Active Directory and Exchange Server sometimes mark “human” accounts by storing a value in a custom (extension) attribute. The attribute might store values like Employee, Temporary, Consultant, and Service Account to indicate the purpose of the account. This example looks for licensed Azure AD accounts where ExtensionAttribute2 stores “Employee” to mark accounts belonging to humans.

[array]$EmployeeAccounts = Get-MgUser -Filter "onPremisesExtensionAttributes/extensionAttribute2 eq 'Employee' and assignedLicenses/`$count ne 0 and userType eq 'Member'" -ConsistencyLevel eventual -CountVariable Records -All

If your organization uses Exchange custom attributes to store employee information, there’s no good reason to switch to using the Azure AD attributes, unless you want to or need to use the Exchange attributes for a different purpose. Switching is a matter of reading the attributes from Exchange Online and writing them to Azure AD, so it’s straightforward. Here’s some simple code to illustrate fetching employee details from Exchange Online and writing it into the user’s Azure AD account. The value for EmployeeType is taken from CustomAttribute2, while the value for the EmployeeHireDate comes from the creation date for the mailbox.

[array]$Mailboxes = Get-ExoMailbox -RecipientTypeDetails UserMailbox -ResultSize Unlimited -Properties CustomAttribute2, WhenCreated
ForEach ($Mbx in $Mailboxes) {
   Update-MgUser -UserId $Mbx.ExternalDirectoryObjectId -EmployeeType $Mbx.CustomAttribute2 -EmployeeHireDate (Get-Date($Mbx.WhenCreated))
}

Splitting Up Processing

Sometimes an organization spans so many accounts that it takes a long time to fetch all accounts. In these circumstances, you can split processing by dividing up the accounts into convenient sets. Some people use departments as the basis for processing, and others use countries. In this example, we fetch Azure AD licensed accounts based on surname. Figure 1 shows the result.

[array]$Surnames = "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "W", "X", "Y", "Z"
ForEach ($S in $Surnames) {
  $Filter = "assignedLicenses/`$count ne 0 and userType eq 'Member' and startsWith(surname,('$S'))"
  [array]$Users = Get-MgUser -Filter $Filter -ConsistencyLevel eventual -CountVariable Records -All
  If ($Users) {
     Write-Host ("{0} users have surname starting with {1}" -f $Users.count, $S)
     Write-Host  "---------------------------------------------"
     $Users | Format-Table DisplayName, Surname
     Write-Host ""
 } Else {
     Write-Host ("No users found with surname starting with {0}" -f $S)
 }
}

Part of a Transition

Making effective calls to find mailboxes and/or Azure AD accounts can be the making or breaking of a PowerShell script. The changeover from the deprecated Azure AD module to Graph API or Graph SDK commands introduces a new filter format that is harder to work with and lacks some of the flexibility available through standard PowerShell comparison operators. Updating scripts to use the Graph can be challenging at times, but the positive way to view things is that it offers a chance to improve the efficiency of the code. That’s not something important for small to medium organizations, but it makes a huge difference when managing tens of thousands of accounts or mailboxes.