Advanced query capabilities on Azure AD directory objects

As Azure AD continues to deliver more capabilities and improvements in stability, availability, and performance, Microsoft Graph also continues to evolve and scale to efficiently access the data. One way is through Microsoft Graph's increasing support for advanced query capabilities on various Azure AD objects and their properties. For example, the addition of Not (not), Not equals (ne), and Ends with (endsWith) operators on the $filter query parameters.

The Microsoft Graph query engine uses an index store to fulfill query requests. To add support for additional query capabilities on some properties, these properties are now indexed in a separate store. This separate indexing allows Azure AD to increase support and improve the performance of the query requests. However, these advanced query capabilities are not available by default but, the requestor must also set the ConsistencyLevel header to eventual and, with the exception of $search, use the $count query parameter. The ConsistencyLevel header and $count are referred to as advanced query parameters.

For example, if you wish to retrieve only inactive user accounts, you can run either of these queries that use the $filter query parameter.

  • Use the $filter query parameter with the eq operator. This request will work by default, that is, the request does not require the advanced query parameters.
GET https://graph.microsoft.com/v1.0/users?$filter=accountEnabled eq false
  • Use the $filter query parameter with the ne operator. This request is not supported by default because the ne operator is only supported in advanced queries. Therefore, you must add the ConsistencyLevel header set to eventual and use the $count=true query string.
GET https://graph.microsoft.com/v1.0/users?$filter=accountEnabled ne true&$count=true
ConsistencyLevel: eventual

These advanced query capabilities are supported only on the following subsets of Azure AD directory objects and their relationships:

Object Relationships
Administrative unit
  • members
  • Application
  • owners
  • Device
  • memberOf
  • transitiveMemberOf
  • registeredUsers
  • registeredOwners
  • Group
  • members
  • transitiveMembers
  • memberOf
  • transitiveMemberOf
  • owners
  • appRoleAssignments
  • Organizational contact
  • memberOf
  • transitiveMemberOf
  • Service principal
  • memberOf
  • transitiveMemberOf
  • appRoleAssignments
  • appRoleAssignmentsTo
  • oAuth2PermissionGrant
  • User
  • memberOf
  • transitiveMemberOf
  • ownedObjects
  • registeredDevices
  • ownedDevices
  • transitiveManagers
  • directReports
  • transitiveReports
  • appRoleAssignments
  • oAuth2PermissionGrant
  • The following table lists query scenarios on directory objects that are supported only in advanced queries:

    Description Example
    Use of $count as a URL segment GET ../groups/$count
    Use of $count as a query string parameter GET ../servicePrincipals?$count=true
    Use of $search GET ../applications?$search="displayName:Browser"
    Use of $orderby on select properties GET ../applications?$orderby=displayName&$count=true
    Use of $filter with the endsWith operator GET ../users?$count=true&$filter=endsWith(mail,'@outlook.com')
    Use of $filter and $orderby in the same query GET ../applications?$orderby=displayName&$filter=startsWith(displayName, 'Box')&$count=true
    Use of $filter with the startsWith operators on specific properties. GET ../users?$filter=startsWith(mobilePhone, '25478') OR startsWith(mobilePhone, '25473')&$count=true
    Use of $filter with ne and not operators GET ../users?$filter=companyName ne null and NOT(companyName eq 'Microsoft')&$count=true
    Use of $filter with not and startsWith operators GET ../users?$filter=NOT startsWith(displayName, 'Conf')&$count=true
    Use of OData cast with another query parameter GET ../me/transitiveMemberOf/microsoft.graph.group?$count=true

    Note

    • Using $filter and $orderBy together is supported only with advanced queries.
    • $expand is not currently supported with advanced queries.
    • The advanced query capabilities are currently not available for Azure AD B2C tenants.

    Support for filter on properties of Azure AD directory objects

    Properties of directory objects behave differently in their support for query parameters. The following are common scenarios for directory objects:

    • Queries that are supported by default will also work in advanced queries, but the response will be eventually consistent.
    • The in operator is supported by default whenever eq operator is supported by default.
    • The endsWith operator is supported only with advanced queries on mail and userPrincipalName properties.
    • The not and ne negation operators are supported only with advanced queries.
      • All properties that support the eq operator also support the ne or not operators.
      • For queries that use the any lambda operator, use the not operator. See Filter using lambda operators.

    The following tables summarizes support for $filter operators by properties of directory objects supported by the advanced query capabilities.

    Legend

    • Works by default. Does not require advanced query parameters. The $filter operator works by default for that property.
    • Requires advanced query parameters. The $filter operator requires advanced query parameters, which are:
      • ConsistencyLevel=eventual header
      • $count=true query string
    • Not supported. The $filter operator is not supported on that property. Send us feedback to request that this property support $filter for your scenarios.
    • Blank cells indicate that the query is not valid for that property.
    • The null value column indicates that the property is nullable and filterable using null.
    • Properties that are not listed here do not support $filter at all.

    Administrative unit properties

    Property Name eq startsWith ge le null value
    description Requires advanced query parameters Requires advanced query parameters Not Supported
    displayName Supported by Default Supported by Default Requires advanced query parameters

    Application properties

    Property Name eq startsWith ge le null value
    appId Supported by Default Not Supported
    applicationTemplateId Supported by Default Not Supported
    createdDateTime Supported by Default Supported by Default Supported by Default Requires advanced query parameters
    createdOnBehalfOf/id Supported by Default Not Supported
    description Requires advanced query parameters Requires advanced query parameters Not Supported
    disabledByMicrosoftStatus Supported by Default Not Supported
    displayName Supported by Default Supported by Default Requires advanced query parameters
    identifierUris/any(p:p) Supported by Default Supported by Default Supported by Default Supported by Default
    info/logoUrl Not Supported Not Supported Requires advanced query parameters
    info/termsOfServiceUrl Requires advanced query parameters Not Supported Not Supported
    keyCredentials/any(k:k/endDateTime) Requires advanced query parameters Requires advanced query parameters Requires advanced query parameters Not Supported
    publisherDomain Supported by Default Supported by Default Not Supported
    requiredResourceAccess/any(r:r/resourceAppId) Requires advanced query parameters Not Supported
    signInAudience Supported by Default Not Supported
    tags/any(p:p) Supported by Default Supported by Default Supported by Default Supported by Default
    verifiedPublisher/displayName Requires advanced query parameters Not Supported Requires advanced query parameters

    Device properties

    Property Name eq startsWith ge le null value
    accountEnabled Supported by Default Not Supported
    alternativeSecurityIds/any(a:a/identityProvider) Requires advanced query parameters Not Supported Not Supported
    alternativeSecurityIds/any(a:a/type) Supported by Default Requires advanced query parameters Requires advanced query parameters Not Supported
    approximateLastSignInDateTime Supported by Default Supported by Default Supported by Default Requires advanced query parameters
    deviceId Supported by Default Not Supported
    displayName Supported by Default Supported by Default Requires advanced query parameters
    extensionAttributes/extensionAttribute1-15 Requires advanced query parameters Not Supported Requires advanced query parameters
    isCompliant Supported by Default Not Supported
    isManaged Supported by Default Not Supported
    mdmAppId Supported by Default Not Supported
    onPremisesLastSyncDateTime Supported by Default Supported by Default Supported by Default Not Supported
    onPremisesSyncEnabled Supported by Default Requires advanced query parameters
    operatingSystem Supported by Default Supported by Default Requires advanced query parameters
    operatingSystemVersion Supported by Default Supported by Default Requires advanced query parameters
    physicalIds/any(p:p) Supported by Default

    Group properties

    Property Name eq startsWith ge le null value
    assignedLicenses/any(a:a/skuId) Supported by Default Not Supported
    classification Supported by Default Supported by Default Not Supported
    createdOnBehalfOf/id Supported by Default Not Supported
    description Requires advanced query parameters Requires advanced query parameters Not Supported
    displayName Supported by Default Supported by Default Requires advanced query parameters
    expirationDateTime Requires advanced query parameters Requires advanced query parameters Requires advanced query parameters Not Supported
    hasMembersWithLicenseErrors Supported by Default Supported by Default
    isAssignableToRole Supported by Default Not Supported
    mail Supported by Default Supported by Default Requires advanced query parameters
    mailEnabled Supported by Default Not Supported
    mailNickname Supported by Default Supported by Default Requires advanced query parameters
    membershipRule Supported by Default Supported by Default Not Supported
    onPremisesLastSyncDateTime Supported by Default Supported by Default Supported by Default Not Supported
    onPremisesProvisioningErrors/any(o:o/category) Supported by Default Not Supported
    onPremisesProvisioningErrors/any(o:o/propertyCausingError) Supported by Default Not Supported
    onPremisesSamAccountName Requires advanced query parameters Requires advanced query parameters Not Supported
    onPremisesSecurityIdentifier Not Supported Requires advanced query parameters
    onPremisesSyncEnabled Supported by Default Requires advanced query parameters
    preferredLanguage Requires advanced query parameters Requires advanced query parameters Requires advanced query parameters
    proxyAddresses/any(p:p) Supported by Default Supported by Default Supported by Default Supported by Default
    renewedDateTime Supported by Default Supported by Default Supported by Default Not Supported
    resourceBehaviorOptions/any(p:p) Supported by Default Supported by Default Supported by Default Supported by Default
    resourceProvisioningOptions/any(p:p) Supported by Default Supported by Default Supported by Default Supported by Default
    securityEnabled Supported by Default Not Supported

    Organizational contacts properties

    Property Name eq startsWith ge le null value
    companyName Requires advanced query parameters Requires advanced query parameters Requires advanced query parameters
    department Supported by Default Supported by Default Requires advanced query parameters
    displayName Supported by Default Supported by Default Requires advanced query parameters
    givenName Supported by Default Supported by Default Requires advanced query parameters
    jobTitle Supported by Default Supported by Default Requires advanced query parameters
    mail Supported by Default Supported by Default Requires advanced query parameters
    mailNickname Supported by Default Supported by Default Requires advanced query parameters
    manager/id Supported by Default Not Supported
    onPremisesLastSyncDateTime Supported by Default Supported by Default Supported by Default Not Supported
    onPremisesProvisioningErrors/any(o:o/category) Supported by Default Not Supported
    onPremisesProvisioningErrors/any(o:o/propertyCausingError) Supported by Default Not Supported
    onPremisesSyncEnabled Supported by Default Requires advanced query parameters
    proxyAddresses/any(p:p) Supported by Default Supported by Default Supported by Default Supported by Default
    surname Supported by Default Supported by Default Requires advanced query parameters

    Service principal properties

    Property Name eq startsWith ge le null value
    accountEnabled Supported by Default Not Supported
    alternativeNames/any(p:p) Supported by Default Supported by Default Supported by Default Supported by Default
    appId Supported by Default Not Supported
    appOwnerOrganizationId Requires advanced query parameters Not Supported
    appRoleAssignmentRequired Requires advanced query parameters Not Supported
    applicationTemplateId Supported by Default Not Supported
    createdObjects/any(c:c/id) Requires advanced query parameters Not Supported
    description Requires advanced query parameters Requires advanced query parameters Not Supported
    displayName Supported by Default Supported by Default Requires advanced query parameters
    info/logoUrl Not Supported Not Supported Requires advanced query parameters
    info/termsOfServiceUrl Requires advanced query parameters Not Supported Not Supported
    keyCredentials/any(k:k/endDateTime) Requires advanced query parameters Requires advanced query parameters Requires advanced query parameters Not Supported
    servicePrincipalNames/any(p:p) Supported by Default Supported by Default Supported by Default Supported by Default
    tags/any(p:p) Supported by Default Supported by Default Supported by Default Supported by Default

    User properties

    Property Name eq startsWith ge le null value
    accountEnabled Supported by Default Not Supported
    ageGroup Supported by Default Not Supported
    assignedLicenses/any(a:a/skuId) Supported by Default Not Supported
    assignedPlans/any(a:a/capabilityStatus) Requires advanced query parameters Not Supported
    assignedPlans/any(a:a/service) Requires advanced query parameters Not Supported Not Supported
    assignedPlans/any(a:a/servicePlanId) Requires advanced query parameters Not Supported
    businessPhones/any(p:p) Requires advanced query parameters Requires advanced query parameters Requires advanced query parameters Requires advanced query parameters
    city Supported by Default Supported by Default Requires advanced query parameters
    companyName Requires advanced query parameters Requires advanced query parameters Requires advanced query parameters
    consentProvidedForMinor Supported by Default Not Supported
    country Supported by Default Supported by Default Requires advanced query parameters
    createdDateTime Supported by Default Supported by Default Supported by Default Requires advanced query parameters
    createdObjects/any(c:c/id) Requires advanced query parameters Not Supported
    creationType Supported by Default Not Supported
    department Supported by Default Supported by Default Requires advanced query parameters
    displayName Supported by Default Supported by Default Requires advanced query parameters
    employeeHireDate Requires advanced query parameters Requires advanced query parameters Requires advanced query parameters Not Supported
    employeeId Supported by Default Requires advanced query parameters
    employeeOrgData/costCenter Requires advanced query parameters Not Supported Not Supported
    employeeOrgData/division Requires advanced query parameters Not Supported Not Supported
    employeeType Requires advanced query parameters Not Supported
    externalUserState Supported by Default Not Supported
    faxNumber Requires advanced query parameters Requires advanced query parameters Requires advanced query parameters
    givenName Supported by Default Supported by Default Requires advanced query parameters
    identities/any(i:i/issuer) Supported by Default Not Supported Supported by Default
    imAddresses/any(p:p) Supported by Default Supported by Default Supported by Default Supported by Default
    isResourceAccount Supported by Default Not Supported
    jobTitle Supported by Default Supported by Default Requires advanced query parameters
    mail Supported by Default Supported by Default Requires advanced query parameters
    mailNickname Supported by Default Supported by Default Requires advanced query parameters
    manager/id Supported by Default Not Supported
    mobilePhone Requires advanced query parameters Requires advanced query parameters Requires advanced query parameters
    officeLocation Requires advanced query parameters Requires advanced query parameters Requires advanced query parameters
    onPremisesExtensionAttributes/extensionAttribute1-15 Requires advanced query parameters Not Supported Requires advanced query parameters
    onPremisesImmutableId Supported by Default Not Supported
    onPremisesLastSyncDateTime Supported by Default Supported by Default Supported by Default Not Supported
    onPremisesProvisioningErrors/any(o:o/category) Supported by Default Not Supported
    onPremisesProvisioningErrors/any(o:o/propertyCausingError) Supported by Default Not Supported
    onPremisesSamAccountName Requires advanced query parameters Requires advanced query parameters Not Supported
    onPremisesSecurityIdentifier Not Supported Requires advanced query parameters
    onPremisesSyncEnabled Supported by Default Requires advanced query parameters
    otherMails/any(p:p) Supported by Default Supported by Default Supported by Default Supported by Default
    passwordPolicies Not Supported Not Supported Requires advanced query parameters
    passwordProfile/forceChangePasswordNextSignIn Requires advanced query parameters Requires advanced query parameters
    passwordProfile/forceChangePasswordNextSignInWithMfa Requires advanced query parameters Requires advanced query parameters
    postalCode Requires advanced query parameters Requires advanced query parameters Requires advanced query parameters
    preferredLanguage Requires advanced query parameters Requires advanced query parameters Requires advanced query parameters
    provisionedPlans/any(p:p/provisioningStatus) Requires advanced query parameters Not Supported
    provisionedPlans/any(p:p/service) Requires advanced query parameters Not Supported Not Supported
    proxyAddresses/any(p:p) Supported by Default Supported by Default Supported by Default Supported by Default
    showInAddressList Requires advanced query parameters Not Supported
    state Supported by Default Supported by Default Requires advanced query parameters
    streetAddress Requires advanced query parameters Requires advanced query parameters Requires advanced query parameters
    surname Supported by Default Supported by Default Requires advanced query parameters
    usageLocation Supported by Default Supported by Default Requires advanced query parameters
    userPrincipalName Supported by Default Supported by Default Not Supported
    userType Supported by Default Requires advanced query parameters

    Error handling for advanced queries on directory objects

    Counting directory objects is only supported using the advanced queries parameters. If the ConsistencyLevel=eventual header is not specified, the request returns an error when the $count URL segment is used or silently ignores the $count query parameter (?$count=true) if it's used.

    https://graph.microsoft.com/v1.0/users/$count
    
    {
        "error": {
            "code": "Request_BadRequest",
            "message": "$count is not currently supported.",
            "innerError": {
                "date": "2021-05-18T19:03:10",
                "request-id": "d9bbd4d8-bb2d-44e6-99a1-71a9516da744",
                "client-request-id": "539da3bd-942f-25db-636b-27f6f6e8eae4"
            }
        }
    }
    

    For directory objects, $search works only in advanced queries. If the ConsistencyLevel header is not specified, the request returns an error.

    https://graph.microsoft.com/v1.0/applications?$search="displayName:Browser"
    
    {
        "error": {
            "code": "Request_UnsupportedQuery",
            "message": "Request with $search query parameter only works through MSGraph with a special request header: 'ConsistencyLevel: eventual'",
            "innerError": {
                "date": "2021-05-27T14:26:47",
                "request-id": "9b600954-ba11-4899-8ce9-6abad341f299",
                "client-request-id": "7964ef27-13a3-6ca4-ed7b-73c271110867"
            }
        }
    }
    

    If a property or query parameter in the URL is supported only in advanced queries but either the ConsistencyLevel header or the $count=true query string is missing, the request returns an error.

    https://graph.microsoft.com/v1.0/users?$filter=endsWith(mail,'@outlook.com')
    
    {
        "error": {
            "code": "Request_UnsupportedQuery",
            "message": "Unsupported Query.",
            "innerError": {
                "date": "2021-05-18T19:12:36",
                "request-id": "63f2093c-399c-4350-9609-3ce9b62abe3c",
                "client-request-id": "e60ed0ba-df5d-e190-f056-f9c0318456d7"
            }
        }
    }
    

    If a property has not been indexed to support a query parameter, even if the advanced query parameters are specified, the request returns an error.

    https://graph.microsoft.com/beta/groups?$filter=createdDateTime ge 2021-11-01&$count=true
    ConsistencyLevel: eventual
    
    {
        "error": {
            "code": "Request_UnsupportedQuery",
            "message": "The request uses a filter property that is not indexed",
            "innerError": {
                "date": "2021-06-10T19:32:01",
                "request-id": "5625ba13-0c9f-4fce-a853-4b52f3e0bd09",
                "client-request-id": "76fe4cd8-df3a-f8c3-659b-594274b6bb31"
            }
        }
    }
    

    However, it is important to note that query parameters specified in a request might fail silently. This can be true for unsupported query parameters as well as for unsupported combinations of query parameters. In these cases, you should examine the data returned by the request to determine whether the query parameters you specified had the desired effect. For example, in the following example, the @odata.count parameter is missing even if the query is successful.

    https://graph.microsoft.com/v1.0/users?$count=true
    
    HTTP/1.1 200 OK
    Content-type: application/json
    
    {
      "@odata.context":"https://graph.microsoft.com/v1.0/$metadata#users",
      "value":[
        {
          "displayName":"Oscar Ward",
          "mail":"oscarward@contoso.com",
          "userPrincipalName":"oscarward@contoso.com"
        },
      ]
    }
    

    See also