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.

11 thoughts on “Filtering users and groups with the Azure AD (Graph) ODATA syntax

  1. hema says:

    or may be usertype = or not eq to guest this also works

    Reply
  2. hema says:

    use filter like this | where {$_.dirsyncenabled -eq $null} to get all guest users and negate this to get all domain users

    Reply
  3. Ron says:

    What are the parameters exactly doing? The c:c and y: in
    “proxyAddresses/any(c:c eq ‘smtp:user@domain.com’)”
    “proxyAddresses/any(y:startswith(y,’smtp:gosho’))”

    I’m missing the logic and can’t find it when search for these parameters.

    Reply
    1. Vasil Michev says:

      Those are variables, not operators, you can name them as you please.

      Reply
  4. ae says:

    Hello,
    They are something to have negation of those filter ?
    I try to do
    Get-AzureADUser -Filter “DisplayName ne ‘Chris OnPrem'”
    Get-AzureADUser -Filter “DisplayName notlike ‘Chris OnPrem'”
    Get-AzureADUser -Filter “not startswith(DisplayName,’vasil’)”

    But nothing works.
    Someone have an idea ?
    Thanks

    Reply
  5. aj says:

    Hi,
    Is there any way to use extensionattributes in filters?

    Reply
    1. Vasil Michev says:

      You mean in the Graph? Not sure, haven’t really bothered to check as filtering in the Graph is beyond crap.

      Reply
      1. aj says:

        Yes in the graph, I tried using onPremisesExtensionAttributes.extensionAttribute8 and its not working.

        Reply
        1. Vasil Michev says:

          Yeah, I don’t believe it’s supported.

  6. Kendal Friesen says:

    Thanks for this, I too, find it frustrating that it doesn’t fully support Odata 3 and allow for substringof, or any of the usable substring formats.

    Reply

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.