Advanced query capabilities on Microsoft Entra ID objects

Microsoft Graph supports advanced query capabilities on various Microsoft Entra ID objects, also called directory objects, to help you efficiently access data. 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, those properties might be indexed in a separate store. This separate indexing improves query performance. However, these advanced query capabilities aren't available by default but, the requestor must set the ConsistencyLevel header to eventual and, except for $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 works by default and doesn't 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 isn't 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

Microsoft Entra ID (directory) objects that support advanced query capabilities

Advanced query capabilities are supported only on directory objects and their relationships, including the following 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
  • Query scenarios that require advanced query capabilities

    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, 'contoso.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 by properties of Microsoft Entra ID (directory) objects

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

    • The in operator is supported by default whenever eq operator is supported by default.
    • The endswith operator is supported only with advanced query parameters and only 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 supports 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 summarize support for $filter operators by properties of directory objects, and indicates where querying is supported through advanced query capabilities.

    Legend

    • Filter works by default. Doesn't require advanced query parameters but still works with advanced query parameters. The $filter operator works by default for that property.
    • Filter only works without advanced query parameters. The $filter operator only works without advanced query parameters.
    • Filter 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 isn't supported on that property. Send us feedback to request that this property support $filter for your scenarios.
    • Blank cells indicate that the query isn't valid for that property.
    • The null value column indicates that the property is nullable and filterable using null.
    • Properties that aren't listed here don't support $filter at all.

    Administrative unit properties

    Property eq startsWith eq Null
    description Advanced Advanced Advanced
    displayName Default+Advanced Default+Advanced Advanced
    isMemberManagementRestricted Default+Advanced
    membershipRule Default+Advanced Default+Advanced
    membershipRuleProcessingState Default+Advanced

    Application properties

    Property eq startsWith ge/le eq Null
    appId Default+Advanced
    createdDateTime Default+Advanced Advanced
    description Advanced Advanced Advanced
    disabledByMicrosoftStatus Default+Advanced
    displayName Default+Advanced Default+Advanced Advanced
    federatedIdentityCredentials/any(f:f/issuer) Advanced Advanced
    federatedIdentityCredentials/any(f:f/name) Advanced Advanced
    federatedIdentityCredentials/any(f:f/subject) Advanced Advanced
    identifierUris/any(p:p) Default+Advanced Default+Advanced
    info/logoUrl Advanced
    info/termsOfServiceUrl Advanced Advanced
    notes Advanced Advanced Advanced
    publicClient/redirectUris/any(p:p) Advanced Advanced
    publisherDomain Default+Advanced Default+Advanced
    requiredResourceAccess/any(r:r/resourceAppId) Advanced
    serviceManagementReference Advanced Advanced Advanced
    signInAudience Default+Advanced
    spa/redirectUris/any(p:p) Advanced Advanced
    tags/any(p:p) Default+Advanced Default+Advanced
    uniqueName Default+Advanced Default+Advanced
    verifiedPublisher/displayName Advanced Advanced Advanced
    web/homePageUrl Advanced Advanced Advanced
    web/redirectUris/any(p:p) Advanced Advanced

    The following properties of the application entity support $count of a collection in a filter expression.

    Property eq Count 0 eq Count 1
    extensionProperties/$count Advanced NotSupported
    federatedIdentityCredentials/$count Advanced NotSupported

    Contract properties

    Property eq startsWith
    customerId Default+Advanced
    defaultDomainName Default+Advanced Default+Advanced
    displayName Default+Advanced Default+Advanced

    Device properties

    Property eq startsWith ge/le eq Null
    accountEnabled Default+Advanced
    alternativeSecurityIds/any(a:a/identityProvider) Advanced Advanced
    alternativeSecurityIds/any(a:a/type) Default+Advanced
    approximateLastSignInDateTime Default+Advanced Advanced
    deviceCategory Advanced Advanced Advanced
    deviceId Default+Advanced
    deviceOwnership Advanced Advanced
    displayName Default+Advanced Default+Advanced Advanced
    enrollmentProfileName Advanced Advanced Advanced
    extensionAttributes/extensionAttribute1-15 Advanced Advanced Advanced
    hostnames/any(p:p) Default+Advanced Default+Advanced
    isCompliant Default+Advanced
    isManaged Default+Advanced
    isRooted Advanced Advanced
    managementType Advanced Advanced
    manufacturer Advanced Advanced Advanced
    mdmAppId Default+Advanced Advanced
    model Advanced Advanced Advanced
    onPremisesLastSyncDateTime Default+Advanced
    onPremisesSecurityIdentifier Default+Advanced Advanced
    onPremisesSyncEnabled Default+Advanced Advanced
    operatingSystem Default+Advanced Default+Advanced Advanced
    operatingSystemVersion Default+Advanced Default+Advanced Advanced
    physicalIds/any(p:p) Default+Advanced
    profileType Default+Advanced
    registrationDateTime Advanced Advanced
    trustType Default+Advanced

    The following properties of the device entity support $count of a collection in a filter expression.

    Property eq Count 0 eq Count 1
    physicalIds/$count Advanced NotSupported
    systemLabels/$count Advanced NotSupported

    Directory role properties

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

    Group properties

    Property eq startsWith ge/le eq Null
    assignedLicenses/any(a:a/skuId) Default+Advanced
    classification Default+Advanced Default+Advanced
    createdByAppId Default+Advanced
    createdDateTime Advanced Advanced
    description Advanced Advanced Advanced
    displayName Default+Advanced Default+Advanced Advanced
    expirationDateTime Advanced
    groupTypes/any(p:p) Default+Advanced
    hasMembersWithLicenseErrors DefaultOnly DefaultOnly
    infoCatalogs/any(p:p) Default+Advanced Default+Advanced
    isAssignableToRole Default+Advanced
    mail Default+Advanced Default+Advanced Advanced
    mailEnabled Default+Advanced
    mailNickname Default+Advanced Default+Advanced Advanced
    membershipRule Default+Advanced Default+Advanced
    membershipRuleProcessingState Default+Advanced
    onPremisesLastSyncDateTime Default+Advanced
    onPremisesProvisioningErrors/any(o:o/category) Default+Advanced
    onPremisesProvisioningErrors/any(o:o/propertyCausingError) Default+Advanced
    onPremisesSamAccountName Advanced Advanced
    onPremisesSecurityIdentifier Default+Advanced Advanced
    onPremisesSyncEnabled Default+Advanced Advanced
    preferredLanguage Advanced Advanced
    proxyAddresses/any(p:p) Default+Advanced Default+Advanced
    renewedDateTime Default+Advanced
    resourceBehaviorOptions/any(p:p) Default+Advanced
    resourceProvisioningOptions/any(p:p) Default+Advanced
    securityEnabled Default+Advanced
    uniqueName Default+Advanced Default+Advanced

    The following properties of the group entity support $count of a collection in a filter expression.

    Property eq Count 0 eq Count 1
    assignedLicenses/$count Advanced NotSupported
    onPremisesProvisioningErrors/$count Advanced NotSupported
    proxyAddresses/$count Advanced NotSupported

    Organizational contact properties

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

    The following properties of the orgContact entity support $count of a collection in a filter expression.

    Property eq Count 0 eq Count 1
    onPremisesProvisioningErrors/$count Advanced NotSupported
    proxyAddresses/$count Advanced NotSupported

    Service principal properties

    Property eq startsWith ge/le eq Null
    accountEnabled Default+Advanced
    alternativeNames/any(p:p) Default+Advanced Default+Advanced
    appId Default+Advanced
    appOwnerOrganizationId Advanced
    appRoleAssignmentRequired Advanced
    applicationTemplateId Default+Advanced
    claimsPolicy/id Default+Advanced
    createdObjects/any(c:c/id) Advanced
    description Advanced Advanced Advanced
    displayName Default+Advanced Default+Advanced Advanced
    federatedIdentityCredentials/any(f:f/issuer) Advanced Advanced
    federatedIdentityCredentials/any(f:f/name) Advanced Advanced
    federatedIdentityCredentials/any(f:f/subject) Advanced Advanced
    homepage Advanced Advanced Advanced
    info/logoUrl Advanced
    info/termsOfServiceUrl Advanced Advanced
    notes Advanced Advanced Advanced
    preferredSingleSignOnMode Default+Advanced
    preferredTokenSigningKeyEndDateTime Default+Advanced
    publisherName Default+Advanced Default+Advanced
    remoteDesktopSecurityConfiguration/id Default+Advanced
    servicePrincipalNames/any(p:p) Default+Advanced Default+Advanced
    servicePrincipalType Default+Advanced
    tags/any(p:p) Default+Advanced Default+Advanced
    verifiedPublisher/displayName Advanced Advanced Advanced

    The following properties of the servicePrincipal entity support $count of a collection in a filter expression.

    Property eq Count 0 eq Count 1
    federatedIdentityCredentials/$count Advanced NotSupported

    User properties

    Property eq startsWith ge/le eq Null
    accountEnabled Default+Advanced
    ageGroup Default+Advanced
    assignedLicenses/any(a:a/skuId) Default+Advanced
    assignedPlans/any(a:a/capabilityStatus) Advanced
    assignedPlans/any(a:a/service) Advanced Advanced
    assignedPlans/any(a:a/servicePlanId) Advanced
    authorizationInfo/certificateUserIds/any(p:p) Advanced
    businessPhones/any(p:p) Advanced Advanced
    city Default+Advanced Default+Advanced Advanced
    cloudRealtimeCommunicationInfo/isSipEnabled Default+Advanced
    companyName Advanced Advanced Advanced
    consentProvidedForMinor Default+Advanced
    country Default+Advanced Default+Advanced Advanced
    createdDateTime Default+Advanced Advanced
    createdObjects/any(c:c/id) Advanced
    creationType Default+Advanced
    department Default+Advanced Default+Advanced Advanced
    displayName Default+Advanced Default+Advanced Advanced
    employeeHireDate Advanced
    employeeId Default+Advanced Advanced
    employeeOrgData/costCenter Advanced Advanced
    employeeOrgData/division Advanced Advanced
    employeeType Advanced
    externalUserState Default+Advanced
    faxNumber Advanced Advanced Advanced
    givenName Default+Advanced Default+Advanced Advanced
    identities/any(i:i/issuer) DefaultOnly DefaultOnly
    imAddresses/any(p:p) Default+Advanced Default+Advanced
    infoCatalogs/any(p:p) Default+Advanced Default+Advanced
    isLicenseReconciliationNeeded DefaultOnly
    isResourceAccount Default+Advanced
    jobTitle Default+Advanced Default+Advanced Advanced
    mail Default+Advanced Default+Advanced Advanced
    mailNickname Default+Advanced Default+Advanced Advanced
    mobilePhone Advanced Advanced Advanced
    officeLocation Advanced Advanced Advanced
    onPremisesDistinguishedName Advanced Advanced Advanced
    onPremisesExtensionAttributes/extensionAttribute1-15 Advanced Advanced Advanced
    onPremisesImmutableId Default+Advanced
    onPremisesLastSyncDateTime Default+Advanced
    onPremisesProvisioningErrors/any(o:o/category) Default+Advanced
    onPremisesProvisioningErrors/any(o:o/propertyCausingError) Default+Advanced
    onPremisesSamAccountName Advanced Advanced
    onPremisesSecurityIdentifier Default+Advanced Advanced
    onPremisesSipInfo/isSipEnabled Advanced
    onPremisesSyncEnabled Default+Advanced Advanced
    otherMails/any(p:p) Default+Advanced Default+Advanced
    passwordPolicies Advanced
    passwordProfile/forceChangePasswordNextSignIn Advanced Advanced
    passwordProfile/forceChangePasswordNextSignInWithMfa Advanced Advanced
    postalCode Advanced Advanced Advanced
    preferredLanguage Advanced Advanced
    provisionedPlans/any(p:p/provisioningStatus) Advanced
    provisionedPlans/any(p:p/service) Advanced Advanced
    proxyAddresses/any(p:p) Default+Advanced Default+Advanced
    state Default+Advanced Advanced
    streetAddress Advanced Advanced Advanced
    surname Default+Advanced Default+Advanced Advanced
    usageLocation Default+Advanced Default+Advanced Advanced
    userPrincipalName Default+Advanced Default+Advanced
    userType Default+Advanced Advanced

    The following properties of the user entity support $count of a collection in a filter expression.

    Property eq Count 0 eq Count 1
    assignedLicenses/$count Advanced NotSupported
    onPremisesProvisioningErrors/$count Advanced NotSupported
    otherMails/$count Advanced NotSupported
    ownedObjects/$count Advanced Advanced
    proxyAddresses/$count Advanced NotSupported

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

    Extension type eq startsWith eq null
    Schema extensions Advanced Advanced Advanced
    Open extensions NotSupported NotSupported NotSupported
    Directory extensions Default+Advanced Advanced Advanced

    Support for sorting by properties of Microsoft Entra ID (directory) objects

    The following table summarizes support for $orderby by properties of directory objects and indicates where sorting is supported through advanced query capabilities.

    Legend

    • Sorting works by default. Does not require advanced query parameters. The $orderby operator works by default for that property.
    • Sorting requires advanced query parameters. The $orderby operator requires advanced query parameters, which are:
      • ConsistencyLevel=eventual header
      • $count=true query string
    • Use of $filter and $orderby in the same query for directory objects always requires advanced query parameters. For more information, see Query scenarios that require advanced query capabilities.
    Directory object Property name $orderby
    administrativeUnit createdDateTime Advanced
    administrativeUnit deletedDateTime Advanced
    administrativeUnit displayName Advanced
    application createdDateTime Advanced
    application deletedDateTime Advanced
    application displayName Advanced
    orgContact createdDateTime Advanced
    orgContact displayName Advanced
    device approximateLastSignInDateTime Advanced
    device createdDateTime Advanced
    device deletedDateTime Advanced
    device displayName Advanced
    group createdDateTime Advanced
    group deletedDateTime Advanced
    group displayName Default
    servicePrincipal createdDateTime Advanced
    servicePrincipal deletedDateTime Advanced
    servicePrincipal displayName Advanced
    user createdDateTime Advanced
    user deletedDateTime Advanced
    user displayName Default
    user userPrincipalName Default
    [all others] [all others] NotSupported

    Error handling for advanced queries on directory objects

    The following section provides examples of common error scenarios when you don't use advanced query parameters where required.

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

    GET 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 isn't specified, the request returns an error.

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

    GET https://graph.microsoft.com/beta/users?$filter=endsWith(userPrincipalName,'%23EXT%[email protected]')
    
    {
        "error": {
            "code": "Request_UnsupportedQuery",
            "message": "Operator 'endsWith' is not supported because the required parameters might be missing. Try adding $count=true query parameter and ConsistencyLevel:eventual header. Refer to https://aka.ms/graph-docs/advanced-queries for more information",
            "innerError": {
                "date": "2023-07-14T08:43:39",
                "request-id": "b3731da7-5c46-4c37-a8e5-b190124d2531",
                "client-request-id": "a1556ddf-4794-929d-0105-b753a78b4c68"
            }
        }
    }
    

    If a property isn't indexed to support a query parameter, the request returns an error even if advanced query parameters are specified. For example, the createdDateTime property of the group resource isn't indexed for query capabilities.

    GET https://graph.microsoft.com/beta/groups?$filter=createdDateTime ge 2021-11-01&$count=true
    ConsistencyLevel: eventual
    
    {
        "error": {
            "code": "Request_UnsupportedQuery",
            "message": "Unsupported or invalid query filter clause specified for property 'createdDateTime' of resource 'Group'.",
            "innerError": {
                "date": "2023-07-14T08:42:44",
                "request-id": "b6a5f998-94c8-430d-846d-2eaae3031492",
                "client-request-id": "2be83e05-649e-2508-bcd9-62e666168fc8"
            }
        }
    }
    

    However, a request that includes query parameters might fail silently. For example, for unsupported query parameters and for unsupported combinations of query parameters. In these cases, 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.

    GET 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":"[email protected]",
          "userPrincipalName":"[email protected]"
        }
      ]
    }