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 parameter.

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, to retrieve only inactive user accounts, you can run either of these queries that use the $filter query parameter.

  • Option 1: 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
    
  • Option 2: 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 Azure AD directory objects and their relationships, including the following frequently used objects:

Object Relationships
administrativeUnit
  • members
  • application
  • owners
  • appRoleAssignment -
    device
  • memberOf
  • transitiveMemberOf
  • registeredUsers
  • registeredOwners
  • group
  • members
  • transitiveMembers
  • memberOf
  • transitiveMemberOf
  • owners
  • appRoleAssignments
  • oAuth2PermissionGrant (delegated permission grants) -
    orgContact
  • memberOf
  • transitiveMemberOf
  • servicePrincipal
  • 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 $count in a $filter expression GET ~/users?$filter=assignedLicenses/$count eq 0&$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 $filter on a collection with endsWith operator GET ~/users?$count=true&$filter=proxyAddresses/any (p:endsWith(p, 'OnMicrosoft.com'))&$select=id,displayName,proxyaddresses
    Use of OData cast with transitive members list 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.
    • To use advanced query capabilities in batch requests, specify the ConsistencyLevel header in the JSON body of the POST request.

    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 with advanced query parameters, 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 query parameters by mail, otherMails, userPrincipalName, and proxyAddresses properties.
    • Getting empty collections (/$count eq 0, /$count ne 0) and collections with less than one object (/$count eq 1, /$count ne 1) is supported only with advanced query parameters.
    • The not and ne negation operators are supported only with advanced query parameters.
      • 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 eq startsWith eq Null
    description Advanced Advanced Advanced
    displayName Default Default Advanced
    isMemberManagementRestricted Default NotSupported
    scopedRoleMembers/any(s:s/id) Default NotSupported

    Application properties

    Property eq startsWith ge/le eq Null eq Count 0
    appId Default NotSupported
    createdDateTime Default Default Advanced
    createdOnBehalfOf/id Default NotSupported
    description Advanced Advanced Advanced
    disabledByMicrosoftStatus Default NotSupported
    displayName Default Default Advanced
    extensionProperties/$count Advanced
    federatedIdentityCredentials/$count Advanced
    federatedIdentityCredentials/any(f:f/issuer) Advanced Advanced NotSupported
    federatedIdentityCredentials/any(f:f/name) Advanced Advanced NotSupported
    federatedIdentityCredentials/any(f:f/subject) Advanced Advanced NotSupported
    identifierUris/any(p:p) Default Default
    info/logoUrl NotSupported NotSupported Advanced
    info/termsOfServiceUrl Advanced NotSupported NotSupported
    owners/$count Advanced
    publicClient/redirectUris/any(p:p) Advanced Advanced
    publisherDomain Default Default NotSupported
    requiredResourceAccess/any(r:r/resourceAppId) Advanced NotSupported
    serviceManagementReference Advanced Advanced Advanced
    signInAudience Default NotSupported
    spa/redirectUris/any(p:p) Advanced Advanced
    tags/any(p:p) Default Default
    uniqueName Default Default NotSupported
    verifiedPublisher/displayName Advanced NotSupported Advanced
    web/homePageUrl Advanced NotSupported Advanced
    web/redirectUris/any(p:p) Advanced Advanced

    Contract properties

    Property eq startsWith
    customerId Default
    defaultDomainName Default Default
    displayName Default Default

    Device properties

    FormattedProperty eq startsWith ge/le eq Null eq Count 0
    accountEnabled Default NotSupported
    alternativeSecurityIds/any(a:a/identityProvider) Advanced Advanced NotSupported
    alternativeSecurityIds/any(a:a/type) Default Advanced NotSupported
    approximateLastSignInDateTime Default Default Advanced
    deviceId Default NotSupported
    displayName Default Default Advanced
    extensionAttributes/extensionAttribute1 Advanced NotSupported Advanced
    extensionAttributes/extensionAttribute10 Advanced NotSupported Advanced
    extensionAttributes/extensionAttribute11 Advanced NotSupported Advanced
    extensionAttributes/extensionAttribute12 Advanced NotSupported Advanced
    extensionAttributes/extensionAttribute13 Advanced NotSupported Advanced
    extensionAttributes/extensionAttribute14 Advanced NotSupported Advanced
    extensionAttributes/extensionAttribute15 Advanced NotSupported Advanced
    extensionAttributes/extensionAttribute2 Advanced NotSupported Advanced
    extensionAttributes/extensionAttribute3 Advanced NotSupported Advanced
    extensionAttributes/extensionAttribute4 Advanced NotSupported Advanced
    extensionAttributes/extensionAttribute5 Advanced NotSupported Advanced
    extensionAttributes/extensionAttribute6 Advanced NotSupported Advanced
    extensionAttributes/extensionAttribute7 Advanced NotSupported Advanced
    extensionAttributes/extensionAttribute8 Advanced NotSupported Advanced
    extensionAttributes/extensionAttribute9 Advanced NotSupported Advanced
    hostnames/any(p:p) Default Default
    isCompliant Default NotSupported
    isManaged Default NotSupported
    mdmAppId Default Advanced
    onPremisesLastSyncDateTime Default Default NotSupported
    onPremisesSyncEnabled Default Advanced
    operatingSystem Default Default Advanced
    operatingSystemVersion Default Default Advanced
    physicalIds/$count Advanced
    physicalIds/any(p:p) Default
    registrationDateTime Advanced Advanced Advanced
    systemLabels/$count Advanced

    Directory role properties

    Property eq startsWith eq Null
    description Advanced Advanced Advanced
    displayName Default Advanced Advanced
    roleTemplateId Default NotSupported

    Group properties

    Property eq startsWith ge/le eq Null eq Count 0
    appRoleAssignments/any(a:a/id) Default NotSupported
    assignedLicenses/any(a:a/skuId) Default NotSupported
    classification Default Default NotSupported
    createdByAppId Default NotSupported
    createdOnBehalfOf/id Default NotSupported
    description Advanced Advanced Advanced
    displayName Default Default Advanced
    expirationDateTime Advanced Advanced NotSupported
    hasMembersWithLicenseErrors Default Default
    infoCatalogs/any(p:p) Default Default
    isAssignableToRole Default NotSupported
    mail Default Default Advanced
    mailEnabled Default NotSupported
    mailNickname Default Default Advanced
    membershipRule Default Default NotSupported
    onPremisesLastSyncDateTime Default Default NotSupported
    onPremisesProvisioningErrors/$count Advanced
    onPremisesProvisioningErrors/any(o:o/category) Default NotSupported
    onPremisesProvisioningErrors/any(o:o/propertyCausingError) Default NotSupported
    onPremisesSamAccountName Advanced Advanced NotSupported
    onPremisesSecurityIdentifier Default Advanced
    onPremisesSyncEnabled Default Advanced
    owners/$count Advanced
    preferredLanguage Advanced Advanced Advanced
    proxyAddresses/$count Advanced
    proxyAddresses/any(p:p) Default Default
    renewedDateTime Default Default NotSupported
    resourceBehaviorOptions/any(p:p) Default Default
    resourceProvisioningOptions/any(p:p) Default Default
    securityEnabled Default NotSupported
    settings/any(s:s/displayName) Default Default NotSupported
    settings/any(s:s/id) Default NotSupported

    Organizational contacts properties

    Property eq startsWith ge/le eq Null eq Count 0
    companyName Advanced Advanced Advanced
    department Default Default Advanced
    displayName Default Default Advanced
    givenName Default Default Advanced
    jobTitle Default Default Advanced
    mail Default Default Advanced
    mailNickname Default Default Advanced
    manager/id Default NotSupported
    onPremisesLastSyncDateTime Default Default NotSupported
    onPremisesProvisioningErrors/$count Advanced
    onPremisesProvisioningErrors/any(o:o/category) Default NotSupported
    onPremisesProvisioningErrors/any(o:o/propertyCausingError) Default NotSupported
    onPremisesSyncEnabled Default Advanced
    proxyAddresses/$count Advanced
    proxyAddresses/any(p:p) Default Default
    surname Default Default Advanced

    Service principal properties

    Property eq startsWith ge/le eq Null eq Count 0
    accountEnabled Default NotSupported
    alternativeNames/any(p:p) Default Default
    appId Default NotSupported
    appOwnerOrganizationId Advanced NotSupported
    appRoleAssignedTo/any(a:a/id) Default NotSupported
    appRoleAssignmentRequired Advanced NotSupported
    appRoleAssignments/any(a:a/id) Default NotSupported
    applicationTemplateId Default NotSupported
    createdObjects/any(c:c/id) Advanced NotSupported
    delegatedPermissionClassifications/any(d:d/id) Default NotSupported
    description Advanced Advanced Advanced
    displayName Default Default Advanced
    federatedIdentityCredentials/$count Advanced
    federatedIdentityCredentials/any(f:f/issuer) Advanced Advanced NotSupported
    federatedIdentityCredentials/any(f:f/name) Advanced Advanced NotSupported
    federatedIdentityCredentials/any(f:f/subject) Advanced Advanced NotSupported
    homepage Advanced Advanced Advanced
    info/logoUrl NotSupported NotSupported Advanced
    info/termsOfServiceUrl Advanced NotSupported NotSupported
    oauth2PermissionGrants/any(o:o/id) Default NotSupported
    ownedObjects/$count Advanced
    owners/$count Advanced
    preferredTokenSigningKeyEndDateTime Default Default NotSupported
    publisherName Default Default NotSupported
    servicePrincipalNames/any(p:p) Default Default
    servicePrincipalType Default NotSupported
    tags/any(p:p) Default Default
    verifiedPublisher/displayName Advanced NotSupported Advanced

    User properties

    FormattedProperty eq startsWith ge/le eq Null eq Count 0
    accountEnabled Default NotSupported
    ageGroup Default NotSupported
    appRoleAssignments/any(a:a/id) Default NotSupported
    assignedLicenses/$count Advanced
    assignedLicenses/any(a:a/skuId) Default NotSupported
    assignedPlans/any(a:a/capabilityStatus) Advanced NotSupported
    assignedPlans/any(a:a/service) Advanced Advanced NotSupported
    assignedPlans/any(a:a/servicePlanId) Advanced NotSupported
    authorizationInfo/certificateUserIds/any(p:p) Advanced
    businessPhones/any(p:p) Advanced Advanced
    city Default Default Advanced
    companyName Advanced Advanced Advanced
    consentProvidedForMinor Default NotSupported
    country Default Default Advanced
    createdDateTime Default Default Advanced
    createdObjects/any(c:c/id) Advanced NotSupported
    creationType Default NotSupported
    department Default Default Advanced
    displayName Default Default Advanced
    employeeHireDate Advanced Advanced NotSupported
    employeeId Default Advanced
    employeeOrgData/costCenter Advanced NotSupported NotSupported
    employeeOrgData/division Advanced NotSupported NotSupported
    employeeType Advanced NotSupported
    externalUserState Default NotSupported
    faxNumber Advanced Advanced Advanced
    givenName Default Default Advanced
    identities/any(i:i/issuer) Default NotSupported Default
    imAddresses/any(p:p) Default Default
    infoCatalogs/any(p:p) Default Default
    isResourceAccount Default NotSupported
    jobTitle Default Default Advanced
    licenseDetails/any(l:l/id) Default NotSupported
    mail Default Default Advanced
    mailNickname Default Default Advanced
    manager/id Default NotSupported
    mobilePhone Advanced Advanced Advanced
    oauth2PermissionGrants/any(o:o/id) Default NotSupported
    officeLocation Advanced Advanced Advanced
    onPremisesExtensionAttributes/extensionAttribute1 Advanced NotSupported Advanced
    onPremisesExtensionAttributes/extensionAttribute10 Advanced NotSupported Advanced
    onPremisesExtensionAttributes/extensionAttribute11 Advanced NotSupported Advanced
    onPremisesExtensionAttributes/extensionAttribute12 Advanced NotSupported Advanced
    onPremisesExtensionAttributes/extensionAttribute13 Advanced NotSupported Advanced
    onPremisesExtensionAttributes/extensionAttribute14 Advanced NotSupported Advanced
    onPremisesExtensionAttributes/extensionAttribute15 Advanced NotSupported Advanced
    onPremisesExtensionAttributes/extensionAttribute2 Advanced NotSupported Advanced
    onPremisesExtensionAttributes/extensionAttribute3 Advanced NotSupported Advanced
    onPremisesExtensionAttributes/extensionAttribute4 Advanced NotSupported Advanced
    onPremisesExtensionAttributes/extensionAttribute5 Advanced NotSupported Advanced
    onPremisesExtensionAttributes/extensionAttribute6 Advanced NotSupported Advanced
    onPremisesExtensionAttributes/extensionAttribute7 Advanced NotSupported Advanced
    onPremisesExtensionAttributes/extensionAttribute8 Advanced NotSupported Advanced
    onPremisesExtensionAttributes/extensionAttribute9 Advanced NotSupported Advanced
    onPremisesImmutableId Default NotSupported
    onPremisesLastSyncDateTime Default Default NotSupported
    onPremisesProvisioningErrors/$count Advanced
    onPremisesProvisioningErrors/any(o:o/category) Default NotSupported
    onPremisesProvisioningErrors/any(o:o/propertyCausingError) Default NotSupported
    onPremisesSamAccountName Advanced Advanced NotSupported
    onPremisesSecurityIdentifier Default Advanced
    onPremisesSyncEnabled Default Advanced
    otherMails/$count Advanced
    otherMails/any(p:p) Default Default
    ownedObjects/$count Advanced
    passwordPolicies NotSupported NotSupported Advanced
    passwordProfile/forceChangePasswordNextSignIn Advanced Advanced
    passwordProfile/forceChangePasswordNextSignInWithMfa Advanced Advanced
    postalCode Advanced Advanced Advanced
    preferredLanguage Advanced Advanced Advanced
    provisionedPlans/any(p:p/provisioningStatus) Advanced NotSupported
    provisionedPlans/any(p:p/service) Advanced Advanced NotSupported
    proxyAddresses/$count Advanced
    proxyAddresses/any(p:p) Default Default
    scopedRoleMemberOf/any(s:s/id) Default NotSupported
    showInAddressList Advanced NotSupported
    state Default Default Advanced
    streetAddress Advanced Advanced Advanced
    surname Default Default Advanced
    usageLocation Default Default Advanced
    userPrincipalName Default Default NotSupported
    userType Default Advanced

    The following table shows support for $filter by extension properties on the user object.

    Extension type eq startsWith eq null
    onPremisesExtensionAttributes 1-15 Advanced Advanced
    Schema extensions Advanced Advanced Advanced
    Open extensions
    Directory extensions Default Advanced

    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