Filtering users and groups with the Azure AD (Graph) ODATA syntax

Regardless of the fact that the Azure AD PowerShell module hasn’t gotten any love from Microsoft in the past few months, Office 365 administrators should start embracing it and replacing their old MSOL-based scripts. It is the only module Microsoft will support in the future, so there’s no way going around that.

One of the biggest issues with the Azure AD module however is it’s poor ‘usability’ or ‘friendliness’. The module is not designed with the regular Joe in mind, it’s more of a simple exercise of wrapping up some Graph API queries with PowerShell syntax. Thus, we are forced to live with GUIDs, JSON formatting and most importantly, the lack of  proper filter syntax.

Yes, the Graph does offer filtering capabilities. Yes, they suck. No other way of putting that. Still, they do exist and can be used in some cases, with mixed results. In this article I will provide few examples, for future references.

First of all, there is some documentation on the filtering syntax here. Only a handful of examples are provided however, and no detailed explanations are given.

Let’s start with some simple examples. To list all users from a particular department or country, use the following syntax:

Get-AzureADUser -Filter "Department eq 'HP'"
Get-AzureADUser -Filter "Country eq 'BG'"

The eq operator was used for string comparison, and the corresponding string was enclosed in single quotes. If you want to compare against a Boolean property, no quotes should be specified, instead use true or false. For example, to list all disabled accounts:

Get-AzureADUser -Filter "AccountEnabled eq false"

 

Note that while property names (such as AccountEnabled) and string values are NOT case-sensitive, the Booleans are, so make sure to enter them exactly as lowercase true or false. Null values are NOT supported, which is a very unfortunate limitation. Neither is the not equal statement. All those lovely limitations make impossible to simply filter out any synchronized accounts or similar. Annoying!

But wait, it gets even better. The startswith operator can be used to do a wildcard searches, so for example list all accounts that have Vasil in their display name:

Get-AzureADUser -Filter "startswith(displayName,'vasil')"

Cool. Now try any of the below:

Get-AzureADUser -Filter "startswith(DisplayName,'vasil')"
Get-AzureADUser -Filter "Startswith(displayName,'vasil')"

Yes, now things are starting to get case-sensitive. Don’t you just LOVE programmers? 🙂 Oh, and moreover, endswith is NOT supported.

Anyway, off to some more useful examples. We can combine filters using the and and or operators. For example, list all groups that are both mail-enabled and security-enabled:

Get-AzureADGroup -Filter "SecurityEnabled eq true and MailEnabled eq true"
Get-AzureADUser -Filter "Department eq 'Finance' or Department eq 'Marketing'"

Lastly, there’s the any of operator, which is used to compare against multi-valued properties. For example, if I want to check which user has a particular proxyaddress configured, I could use:

Get-AzureADUser -Filter "proxyAddresses/any(c:c eq 'smtp:user@domain.com')"

In case you are wondering about the funky capitalization of proxyAddresses – you’ve guessed it, it’s the only way it will actually work. As another example, we can list any proxy addresses starting with particular string:

Get-AzureADUser -Filter "proxyAddresses/any(y:startswith(y,'smtp:gosho'))"

This query will return all users that have any of their proxyaddresses starting with ‘gosho’. The next logical thing to try is to filter them by domain. Not possible sadly, as we don’t yet have support for the endswith operator…

While the “lambda” operators any/all are certainly useful, there are other limitations of the Graph that make them irrelevant:

Code: Request_UnsupportedQuery
Message: Complex query on property provisionedPlans is not supported.
Message: Complex query on property assignedPlans is not supported.

Anyway, I think I had enough of crappy syntaxes for now. Let me know if this was useful and if you have worked out some fancier examples.

Additional information about the OData syntax can be found here.

This entry was posted in Azure AD, Office 365, PowerShell. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *