Advanced query capabilities on Azure AD objects
As Azure Active Directory (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, also called directory 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 theeq
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 thene
operator. This request is not supported by default because thene
operator is only supported in advanced queries. Therefore, you must add the ConsistencyLevel header set toeventual
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 directory objects and their relationships, including the following frequently used objects:
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 whenevereq
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
andne
negation operators are supported only with advanced query parameters.- All properties that support the
eq
operator also support thene
ornot
operators. - For queries that use the
any
lambda operator, use thenot
operator. See Filter using lambda operators.
- All properties that support the
The following tables summarizes support for $filter
operators by properties of directory objects supported by the advanced query capabilities.
Legend
The
$filter
operator works by default for that property.The
$filter
operator requires advanced query parameters, which are:ConsistencyLevel=eventual
header$count=true
query string
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 | |||
displayName | |||
isMemberManagementRestricted | |||
scopedRoleMembers/any(s:s/id) |
Application properties
Property | eq | startsWith | ge/le | eq Null | eq Count 0 |
---|---|---|---|---|---|
appId | |||||
createdDateTime | |||||
createdOnBehalfOf/id | |||||
description | |||||
disabledByMicrosoftStatus | |||||
displayName | |||||
extensionProperties/$count | |||||
federatedIdentityCredentials/$count | |||||
federatedIdentityCredentials/any(f:f/issuer) | |||||
federatedIdentityCredentials/any(f:f/name) | |||||
federatedIdentityCredentials/any(f:f/subject) | |||||
identifierUris/any(p:p) | |||||
info/logoUrl | |||||
info/termsOfServiceUrl | |||||
owners/$count | |||||
publicClient/redirectUris/any(p:p) | |||||
publisherDomain | |||||
requiredResourceAccess/any(r:r/resourceAppId) | |||||
serviceManagementReference | |||||
signInAudience | |||||
spa/redirectUris/any(p:p) | |||||
tags/any(p:p) | |||||
uniqueName | |||||
verifiedPublisher/displayName | |||||
web/homePageUrl | |||||
web/redirectUris/any(p:p) |
Contract properties
Property | eq | startsWith |
---|---|---|
customerId | ||
defaultDomainName | ||
displayName |
Device properties
FormattedProperty | eq | startsWith | ge/le | eq Null | eq Count 0 |
---|---|---|---|---|---|
accountEnabled | |||||
alternativeSecurityIds/any(a:a/identityProvider) | |||||
alternativeSecurityIds/any(a:a/type) | |||||
approximateLastSignInDateTime | |||||
deviceId | |||||
displayName | |||||
extensionAttributes/extensionAttribute1 | |||||
extensionAttributes/extensionAttribute10 | |||||
extensionAttributes/extensionAttribute11 | |||||
extensionAttributes/extensionAttribute12 | |||||
extensionAttributes/extensionAttribute13 | |||||
extensionAttributes/extensionAttribute14 | |||||
extensionAttributes/extensionAttribute15 | |||||
extensionAttributes/extensionAttribute2 | |||||
extensionAttributes/extensionAttribute3 | |||||
extensionAttributes/extensionAttribute4 | |||||
extensionAttributes/extensionAttribute5 | |||||
extensionAttributes/extensionAttribute6 | |||||
extensionAttributes/extensionAttribute7 | |||||
extensionAttributes/extensionAttribute8 | |||||
extensionAttributes/extensionAttribute9 | |||||
hostnames/any(p:p) | |||||
isCompliant | |||||
isManaged | |||||
mdmAppId | |||||
onPremisesLastSyncDateTime | |||||
onPremisesSyncEnabled | |||||
operatingSystem | |||||
operatingSystemVersion | |||||
physicalIds/$count | |||||
physicalIds/any(p:p) | |||||
registrationDateTime | |||||
systemLabels/$count |
Directory role properties
Property | eq | startsWith | eq Null |
---|---|---|---|
description | |||
displayName | |||
roleTemplateId |
Group properties
Property | eq | startsWith | ge/le | eq Null | eq Count 0 |
---|---|---|---|---|---|
appRoleAssignments/any(a:a/id) | |||||
assignedLicenses/any(a:a/skuId) | |||||
classification | |||||
createdByAppId | |||||
createdOnBehalfOf/id | |||||
description | |||||
displayName | |||||
expirationDateTime | |||||
hasMembersWithLicenseErrors | |||||
infoCatalogs/any(p:p) | |||||
isAssignableToRole | |||||
mailEnabled | |||||
mailNickname | |||||
membershipRule | |||||
onPremisesLastSyncDateTime | |||||
onPremisesProvisioningErrors/$count | |||||
onPremisesProvisioningErrors/any(o:o/category) | |||||
onPremisesProvisioningErrors/any(o:o/propertyCausingError) | |||||
onPremisesSamAccountName | |||||
onPremisesSecurityIdentifier | |||||
onPremisesSyncEnabled | |||||
owners/$count | |||||
preferredLanguage | |||||
proxyAddresses/$count | |||||
proxyAddresses/any(p:p) | |||||
renewedDateTime | |||||
resourceBehaviorOptions/any(p:p) | |||||
resourceProvisioningOptions/any(p:p) | |||||
securityEnabled | |||||
settings/any(s:s/displayName) | |||||
settings/any(s:s/id) |
Organizational contacts properties
Property | eq | startsWith | ge/le | eq Null | eq Count 0 |
---|---|---|---|---|---|
companyName | |||||
department | |||||
displayName | |||||
givenName | |||||
jobTitle | |||||
mailNickname | |||||
manager/id | |||||
onPremisesLastSyncDateTime | |||||
onPremisesProvisioningErrors/$count | |||||
onPremisesProvisioningErrors/any(o:o/category) | |||||
onPremisesProvisioningErrors/any(o:o/propertyCausingError) | |||||
onPremisesSyncEnabled | |||||
proxyAddresses/$count | |||||
proxyAddresses/any(p:p) | |||||
surname |
Service principal properties
Property | eq | startsWith | ge/le | eq Null | eq Count 0 |
---|---|---|---|---|---|
accountEnabled | |||||
alternativeNames/any(p:p) | |||||
appId | |||||
appOwnerOrganizationId | |||||
appRoleAssignedTo/any(a:a/id) | |||||
appRoleAssignmentRequired | |||||
appRoleAssignments/any(a:a/id) | |||||
applicationTemplateId | |||||
createdObjects/any(c:c/id) | |||||
delegatedPermissionClassifications/any(d:d/id) | |||||
description | |||||
displayName | |||||
federatedIdentityCredentials/$count | |||||
federatedIdentityCredentials/any(f:f/issuer) | |||||
federatedIdentityCredentials/any(f:f/name) | |||||
federatedIdentityCredentials/any(f:f/subject) | |||||
homepage | |||||
info/logoUrl | |||||
info/termsOfServiceUrl | |||||
oauth2PermissionGrants/any(o:o/id) | |||||
ownedObjects/$count | |||||
owners/$count | |||||
preferredTokenSigningKeyEndDateTime | |||||
publisherName | |||||
servicePrincipalNames/any(p:p) | |||||
servicePrincipalType | |||||
tags/any(p:p) | |||||
verifiedPublisher/displayName |
User properties
FormattedProperty | eq | startsWith | ge/le | eq Null | eq Count 0 |
---|---|---|---|---|---|
accountEnabled | |||||
ageGroup | |||||
appRoleAssignments/any(a:a/id) | |||||
assignedLicenses/$count | |||||
assignedLicenses/any(a:a/skuId) | |||||
assignedPlans/any(a:a/capabilityStatus) | |||||
assignedPlans/any(a:a/service) | |||||
assignedPlans/any(a:a/servicePlanId) | |||||
authorizationInfo/certificateUserIds/any(p:p) | |||||
businessPhones/any(p:p) | |||||
city | |||||
companyName | |||||
consentProvidedForMinor | |||||
country | |||||
createdDateTime | |||||
createdObjects/any(c:c/id) | |||||
creationType | |||||
department | |||||
displayName | |||||
employeeHireDate | |||||
employeeId | |||||
employeeOrgData/costCenter | |||||
employeeOrgData/division | |||||
employeeType | |||||
externalUserState | |||||
faxNumber | |||||
givenName | |||||
identities/any(i:i/issuer) | |||||
imAddresses/any(p:p) | |||||
infoCatalogs/any(p:p) | |||||
isResourceAccount | |||||
jobTitle | |||||
licenseDetails/any(l:l/id) | |||||
mailNickname | |||||
manager/id | |||||
mobilePhone | |||||
oauth2PermissionGrants/any(o:o/id) | |||||
officeLocation | |||||
onPremisesExtensionAttributes/extensionAttribute1 | |||||
onPremisesExtensionAttributes/extensionAttribute10 | |||||
onPremisesExtensionAttributes/extensionAttribute11 | |||||
onPremisesExtensionAttributes/extensionAttribute12 | |||||
onPremisesExtensionAttributes/extensionAttribute13 | |||||
onPremisesExtensionAttributes/extensionAttribute14 | |||||
onPremisesExtensionAttributes/extensionAttribute15 | |||||
onPremisesExtensionAttributes/extensionAttribute2 | |||||
onPremisesExtensionAttributes/extensionAttribute3 | |||||
onPremisesExtensionAttributes/extensionAttribute4 | |||||
onPremisesExtensionAttributes/extensionAttribute5 | |||||
onPremisesExtensionAttributes/extensionAttribute6 | |||||
onPremisesExtensionAttributes/extensionAttribute7 | |||||
onPremisesExtensionAttributes/extensionAttribute8 | |||||
onPremisesExtensionAttributes/extensionAttribute9 | |||||
onPremisesImmutableId | |||||
onPremisesLastSyncDateTime | |||||
onPremisesProvisioningErrors/$count | |||||
onPremisesProvisioningErrors/any(o:o/category) | |||||
onPremisesProvisioningErrors/any(o:o/propertyCausingError) | |||||
onPremisesSamAccountName | |||||
onPremisesSecurityIdentifier | |||||
onPremisesSyncEnabled | |||||
otherMails/$count | |||||
otherMails/any(p:p) | |||||
ownedObjects/$count | |||||
passwordPolicies | |||||
passwordProfile/forceChangePasswordNextSignIn | |||||
passwordProfile/forceChangePasswordNextSignInWithMfa | |||||
postalCode | |||||
preferredLanguage | |||||
provisionedPlans/any(p:p/provisioningStatus) | |||||
provisionedPlans/any(p:p/service) | |||||
proxyAddresses/$count | |||||
proxyAddresses/any(p:p) | |||||
scopedRoleMemberOf/any(s:s/id) | |||||
showInAddressList | |||||
state | |||||
streetAddress | |||||
surname | |||||
usageLocation | |||||
userPrincipalName | |||||
userType |
The following table shows support for $filter
by extension properties on the user object.
Extension type | eq | startsWith | eq null |
---|---|---|---|
onPremisesExtensionAttributes 1-15 | |||
Schema extensions | |||
Open extensions | |||
Directory extensions |
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.
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 is not 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/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.
GET 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.
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"
},
]
}
See also
Feedback
Submit and view feedback for