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:

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

Nota

  • 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%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"
    }
  ]
}