Use Filters to Target Mailboxes and Entra ID User Accounts

PowerShell scripts often begin by finding a set of Entra ID 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 user accounts assigned licenses to use Microsoft 365 services).

The first set is easily found with:

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

The second with:

[array]$Users = Get-MgUser -Filter "assignedLicenses/`$count ne 0 and userType eq 'Member'" -ConsistencyLevel eventual -CountVariable Records -All -Property Id, displayName, AssignedLicenses

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 Entra ID objects, which is why the consistency level parameter is present.

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 user accounts belonging to humans. I use this technique in the script to create an HTML report of managers and their direct reports.

Find User Accounts for Employees

Entra ID 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 user accounts.

Find User Accounts with Exchange Custom Attributes

Using any query against Entra ID depends on accurate data being in the queried attributes. My experience is that relatively few Microsoft 365 tenants populate the employee attributes available in Entra ID. 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 user 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 Entra ID 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 Entra ID, so it’s straightforward. Here’s some simple code to illustrate fetching employee details from Exchange Online and writing it into the user’s 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 licensed user 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)
 }
}
Processing Azure AD user accounts by surname
Figure 1: Find users based on the first character of their surname

Part of a Transition

Making effective calls to find mailboxes and/or user 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.

About the Author

Tony Redmond

Tony Redmond has written thousands of articles about Microsoft technology since 1996. He is the lead author for the Office 365 for IT Pros eBook, the only book covering Office 365 that is updated monthly to keep pace with change in the cloud. Apart from contributing to Practical365.com, Tony also writes at Office365itpros.com to support the development of the eBook. He has been a Microsoft MVP since 2004.

Comments

  1. Dennis Kast

    Hi,

    thanks for this good tp!
    I want to ask, If it is possible to show also a way how to count “employeeHireDate” so that I can assign license after employeeHireDate is exceeding 6 months for example.

    1. Avatar photo

      The article says:

      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:

      1. Broonie

        That doesn’t work me. I can see a value for hire date in both the Azure portal and via graph explorer. But no dice with Get-MgUser.

  2. Dinesh

    Thank you for generously sharing your knowledge here. The information provided is truly informative.

Leave a Reply