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:

    • 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 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. Does not require advanced query parameters. The $filter operator works by default for that property.
    • 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 Default Advanced
    isMemberManagementRestricted Default NotSupported
    membershipRule Default Default NotSupported
    membershipRuleProcessingState Default NotSupported
    scopedRoleMembers/any(s:s/id) Default

    Application properties

    Property eq startsWith ge/le eq Null
    appId Default
    createdDateTime Default Default Advanced
    createdOnBehalfOf/id Default
    description Advanced Advanced Advanced
    disabledByMicrosoftStatus Default NotSupported
    displayName Default Default 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 Advanced NotSupported
    notes Advanced Advanced Advanced
    publicClient/redirectUris/any(p:p) Advanced Advanced
    publisherDomain Default Default NotSupported
    requiredResourceAccess/any(r:r/resourceAppId) Advanced
    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 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
    defaultDomainName Default Default
    displayName Default Default

    Device properties

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

    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
    roleTemplateId Default NotSupported

    Group properties

    Property eq startsWith ge/le eq Null
    appRoleAssignments/any(a:a/id) Default
    assignedLicenses/any(a:a/skuId) Default
    classification Default Default NotSupported
    createdByAppId Default
    createdOnBehalfOf/id Default
    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
    membershipRuleProcessingState Default NotSupported
    onPremisesLastSyncDateTime Default Default NotSupported
    onPremisesProvisioningErrors/any(o:o/category) Default NotSupported
    onPremisesProvisioningErrors/any(o:o/propertyCausingError) Default NotSupported
    onPremisesSamAccountName Advanced Advanced NotSupported
    onPremisesSecurityIdentifier Default Advanced
    onPremisesSyncEnabled Default Advanced
    preferredLanguage Advanced Advanced Advanced
    proxyAddresses/any(p:p) Default Default
    renewedDateTime Default Default NotSupported
    resourceBehaviorOptions/any(p:p) Default
    resourceProvisioningOptions/any(p:p) Default
    securityEnabled Default NotSupported
    settings/any(s:s/displayName) Default Default NotSupported
    settings/any(s:s/id) Default
    uniqueName Default Default NotSupported

    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 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
    onPremisesLastSyncDateTime Default Default NotSupported
    onPremisesProvisioningErrors/any(o:o/category) Default NotSupported
    onPremisesProvisioningErrors/any(o:o/propertyCausingError) Default NotSupported
    onPremisesSyncEnabled Default Advanced
    proxyAddresses/any(p:p) Default Default
    surname Default Default 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 NotSupported
    alternativeNames/any(p:p) Default Default
    appId Default
    appOwnerOrganizationId Advanced
    appRoleAssignedTo/any(a:a/id) Default
    appRoleAssignmentRequired Advanced NotSupported
    appRoleAssignments/any(a:a/id) Default
    applicationTemplateId Default
    createdObjects/any(c:c/id) Advanced
    delegatedPermissionClassifications/any(d:d/id) Default
    description Advanced Advanced Advanced
    displayName Default Default 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 Advanced NotSupported
    notes Advanced Advanced Advanced
    oauth2PermissionGrants/any(o:o/id) Default
    preferredSingleSignOnMode Default NotSupported
    preferredTokenSigningKeyEndDateTime Default Default NotSupported
    publisherName Default Default NotSupported
    remoteDesktopSecurityConfiguration/id Default
    remoteDesktopSecurityConfiguration/targetDeviceGroups/any(t:t/displayName) Default Default NotSupported
    remoteDesktopSecurityConfiguration/targetDeviceGroups/any(t:t/id) Default
    servicePrincipalNames/any(p:p) Default Default
    servicePrincipalType Default NotSupported
    tags/any(p:p) Default Default
    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
    ownedObjects/$count Advanced Advanced

    User properties

    Property eq startsWith ge/le eq Null
    accountEnabled Default NotSupported
    ageGroup Default NotSupported
    appRoleAssignments/any(a:a/id) Default
    assignedLicenses/any(a:a/skuId) Default
    assignedPlans/any(a:a/capabilityStatus) Advanced NotSupported
    assignedPlans/any(a:a/service) Advanced Advanced NotSupported
    assignedPlans/any(a:a/servicePlanId) Advanced
    authorizationInfo/certificateUserIds/any(p:p) Advanced
    businessPhones/any(p:p) Advanced Advanced
    city Default Default Advanced
    cloudRealtimeCommunicationInfo/isSipEnabled Default NotSupported
    companyName Advanced Advanced Advanced
    consentProvidedForMinor Default NotSupported
    country Default Default Advanced
    createdDateTime Default Default Advanced
    createdObjects/any(c:c/id) Advanced
    creationType Default NotSupported
    department Default Default Advanced
    displayName Default Default Advanced
    employeeHireDate Advanced Advanced NotSupported
    employeeId Default Advanced
    employeeOrgData/costCenter Advanced Advanced NotSupported
    employeeOrgData/division Advanced Advanced 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
    isLicenseReconciliationNeeded Default NotSupported
    isResourceAccount Default NotSupported
    jobTitle Default Default Advanced
    licenseDetails/any(l:l/id) Default
    mail Default Default Advanced
    mailNickname Default Default Advanced
    manager/id NotSupported
    mobilePhone Advanced Advanced Advanced
    oauth2PermissionGrants/any(o:o/id) Default
    officeLocation Advanced Advanced Advanced
    onPremisesDistinguishedName Advanced Advanced Advanced
    onPremisesExtensionAttributes/extensionAttribute1-15 Advanced Advanced Advanced
    onPremisesImmutableId Default
    onPremisesLastSyncDateTime Default Default NotSupported
    onPremisesProvisioningErrors/any(o:o/category) Default NotSupported
    onPremisesProvisioningErrors/any(o:o/propertyCausingError) Default NotSupported
    onPremisesSamAccountName Advanced Advanced NotSupported
    onPremisesSecurityIdentifier Default Advanced
    onPremisesSipInfo/isSipEnabled Advanced NotSupported
    onPremisesSyncEnabled Default Advanced
    otherMails/any(p:p) Default Default
    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/any(p:p) Default Default
    scopedRoleMemberOf/any(s:s/id) Default
    showInAddressList Advanced NotSupported
    state Default Advanced
    streetAddress Advanced Advanced Advanced
    surname Default Default Advanced
    usageLocation Default Default Advanced
    userPrincipalName Default Default NotSupported
    userType Default 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

    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 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%23@contoso.com')
    
    {
        "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":"oscarward@contoso.com",
          "userPrincipalName":"oscarward@contoso.com"
        }
      ]
    }