Advanced query capabilities on Azure AD directory objects

Artikel
06/28/2022
8 menit untuk membaca

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

// See https://docs.microsoft.com/graph/sdks/create-client?tabs=CS
var user = await graphClient.Users.Request()
    .Filter("accountEnabled eq false")
    .GetAsync();

// See https://docs.microsoft.com/graph/sdks/create-client?tabs=Javascript
let users = await client.api('/users')
  .filter('accountEnabled eq false')
  .get();

// See https://docs.microsoft.com/graph/sdks/create-client?tabs=Objective-C
NSMutableURLRequest *urlRequest = [NSMutableURLRequest requestWithURL:[NSURL URLWithString:[MSGraphBaseURL stringByAppendingString:@"/users?$filter=accountEnabled eq false"]]];
[urlRequest setHTTPMethod:@"GET"];

MSURLSessionDataTask *usersDataTask = [httpClient dataTaskWithRequest:urlRequest
completionHandler: ^(NSData *data, NSURLResponse *response, NSError *nserror) {

  NSError *jsonError = nil;
  MSCollection *collection = [[MSCollection alloc] initWithData:data error:&jsonError];
  MSGraphUser *user = [[MSGraphUser alloc] initWithDictionary:[[collection value] objectAtIndex: 0] error:&nserror];

}];

[usersDataTask execute];

// See https://docs.microsoft.com/en-us/graph/sdks/create-client?tabs=Java
UserCollectionPage users = graphClient.users()
    .buildRequest()
    .filter("accountEnabled eq false")
    .get();

// See https://docs.microsoft.com/graph/sdks/create-client?tabs=Go
requestParameters := &msgraphsdk.UsersRequestBuilderGetQueryParameters{
    Filter: "accountEnabled eq false",
}

options := &msgraphsdk.UsersRequestBuilderGetOptions{
    Q: requestParameters,
}

result, err := client.Users().Get(options)

Import-Module Microsoft.Graph.Users

Get-MgUser -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

// See https://docs.microsoft.com/graph/sdks/create-client?tabs=CS
var user = await graphClient.Users.Request()
    .Request(new Option[] { new QueryOption("$count", "true")})
    .Header("ConsistencyLevel", "eventual")
    .Filter("accountEnabled ne true")
    .GetAsync();

// See https://docs.microsoft.com/graph/sdks/create-client?tabs=Javascript
let users = await client.api('/users')
  .header('ConsistencyLevel','eventual')
  .filter('accountEnabled ne true')
  .count(true)
  .get();

// See https://docs.microsoft.com/graph/sdks/create-client?tabs=Objective-C
NSMutableURLRequest *urlRequest = [NSMutableURLRequest requestWithURL:[NSURL URLWithString:[MSGraphBaseURL stringByAppendingString:@"/users?$filter=accountEnabled ne true&$count=true"]]];
[urlRequest setHTTPMethod:@"GET"];
[urlRequest setValue:@"eventual" forHTTPHeaderField:@"ConsistencyLevel"];

MSURLSessionDataTask *usersDataTask = [httpClient dataTaskWithRequest:urlRequest
completionHandler: ^(NSData *data, NSURLResponse *response, NSError *nserror) {

  NSError *jsonError = nil;
  MSCollection *collection = [[MSCollection alloc] initWithData:data error:&jsonError];
  MSGraphUser *user = [[MSGraphUser alloc] initWithDictionary:[[collection value] objectAtIndex: 0] error:&nserror];

}];

[usersDataTask execute];

// See https://docs.microsoft.com/en-us/graph/sdks/create-client?tabs=Java
LinkedList<Option> requestOptions = new LinkedList<Option>();
requestOptions.add(new HeaderOption("ConsistencyLevel", "eventual"));
requestOptions.add(new QueryOption("$count", "true"))

UserCollectionPage users = graphClient.users()
    .buildRequest(requestOptions)
    .filter("accountEnabled ne true")
    .get();

// See https://docs.microsoft.com/graph/sdks/create-client?tabs=Go
requestParameters := &msgraphsdk.UsersRequestBuilderGetQueryParameters{
    Filter: "accountEnabled ne true",
    Count: true,
}

headers := map[string]string{
    "ConsistencyLevel": "eventual"
}

options := &msgraphsdk.UsersRequestBuilderGetOptions{
    Q: requestParameters,
    H: headers,
}

result, err := client.Users().Get(options)

Import-Module Microsoft.Graph.Users

Get-MgUser -Filter "accountEnabled ne true" -CountVariable CountVar -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 in advanced queries, 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 queries on mail, otherMails, userPrincipalName, and proxyAddresses properties.
The not and ne negation operators are supported only with advanced queries.
- 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

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 Name	eq	startsWith	ge	le	null value
description
displayName
isMemberManagementRestricted

Application properties

Property Name	eq	startsWith	ge	le	null value
appId
createdDateTime
createdOnBehalfOf/deletedDateTime
createdOnBehalfOf/id
description
disabledByMicrosoftStatus
displayName
identifierUris/any(p:p)
info/logoUrl
info/termsOfServiceUrl
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)

Device properties

Property Name	eq	startsWith	ge	le	null value
accountEnabled
alternativeSecurityIds/any(a:a/identityProvider)
alternativeSecurityIds/any(a:a/type)
approximateLastSignInDateTime
deviceId
displayName
extensionAttributes1-15
hostnames/any(p:p)
isCompliant
isManaged
mdmAppId
onPremisesLastSyncDateTime
onPremisesSyncEnabled
operatingSystem
operatingSystemVersion
physicalIds/any(p:p)
registrationDateTime

Group properties

Property Name	eq	startsWith	ge	le	null value
assignedLicenses/any(a:a/skuId)
classification
createdByAppId
createdOnBehalfOf/deletedDateTime
createdOnBehalfOf/id
description
displayName
expirationDateTime
hasMembersWithLicenseErrors
infoCatalogs/any(p:p)
isAssignableToRole
mail
mailEnabled
mailNickname
membershipRule
onPremisesLastSyncDateTime
onPremisesProvisioningErrors/any(o:o/category)
onPremisesProvisioningErrors/any(o:o/propertyCausingError)
onPremisesSamAccountName
onPremisesSecurityIdentifier
onPremisesSyncEnabled
preferredLanguage
proxyAddresses/any(p:p)
renewedDateTime
resourceBehaviorOptions/any(p:p)
resourceProvisioningOptions/any(p:p)
securityEnabled

Organizational contacts properties

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

Service principal properties

Property Name	eq	startsWith	ge	le	null value
accountEnabled
alternativeNames/any(p:p)
appId
appOwnerOrganizationId
appRoleAssignmentRequired
applicationTemplateId
createdObjects/any(c:c/id)
description
displayName
homepage
info/logoUrl
info/termsOfServiceUrl
preferredTokenSigningKeyEndDateTime
publisherName
servicePrincipalNames/any(p:p)
tags/any(p:p)

User properties

Property Name	eq	startsWith	ge	le	null value
accountEnabled
ageGroup
assignedLicenses/any(a:a/skuId)
assignedPlans/any(a:a/capabilityStatus)
assignedPlans/any(a:a/service)
assignedPlans/any(a:a/servicePlanId)
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
mail
mailNickname
manager/deletedDateTime
manager/id
mobilePhone
officeLocation
onPremisesExtensionAttributes/extensionAttribute1-15
onPremisesImmutableId
onPremisesLastSyncDateTime
onPremisesProvisioningErrors/any(o:o/category)
onPremisesProvisioningErrors/any(o:o/propertyCausingError)
onPremisesSamAccountName
onPremisesSecurityIdentifier
onPremisesSyncEnabled
onPremisesUserPrincipalName
otherMails/any(p:p)
passwordPolicies
passwordProfile/forceChangePasswordNextSignIn
passwordProfile/forceChangePasswordNextSignInWithMfa
postalCode
preferredLanguage
provisionedPlans/any(p:p/provisioningStatus)
provisionedPlans/any(p:p/service)
proxyAddresses/any(p:p)
showInAddressList
state
streetAddress
surname
usageLocation
userPrincipalName
userType

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"
    },
  ]
}