Skip to content

Latest commit

 

History

History
543 lines (426 loc) · 41.1 KB

aad-advanced-queries.md

File metadata and controls

543 lines (426 loc) · 41.1 KB
Raw
title description author ms.author ms.reviewer ms.topic ms.localizationpriority ms.subservice ms.custom ms.date
Advanced query capabilities on Microsoft Entra ID objects
Microsoft Entra ID objects support advanced query capabilities to efficiently access data.
FaithOmbongi
ombongifaith
Luca.Spolidoro
concept-article
high
entra-applications
graphiamtop20, scenarios:getting-started
09/28/2023

Advanced query capabilities on Microsoft Entra ID objects

As Microsoft Entra 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 Microsoft Entra ID 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 Microsoft Entra ID to increase support and improve the performance of the query requests. However, these advanced query capabilities aren't available by default but, the requestor must also 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, that is, the request doesn't require the advanced query parameters.

HTTP

GET https://graph.microsoft.com/v1.0/users?$filter=accountEnabled eq false

C#

[!INCLUDE sample-code] [!INCLUDE sdk-documentation]

CLI

[!INCLUDE sample-code] [!INCLUDE sdk-documentation]

Go

[!INCLUDE sample-code] [!INCLUDE sdk-documentation]

Java

[!INCLUDE sample-code] [!INCLUDE sdk-documentation]

JavaScript

[!INCLUDE sample-code] [!INCLUDE sdk-documentation]

PHP

[!INCLUDE sample-code] [!INCLUDE sdk-documentation]

PowerShell

[!INCLUDE sample-code] [!INCLUDE sdk-documentation]

Python

[!INCLUDE sample-code] [!INCLUDE sdk-documentation]

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.

HTTP

GET https://graph.microsoft.com/v1.0/users?$filter=accountEnabled ne true&$count=true
ConsistencyLevel: eventual

C#

[!INCLUDE sample-code] [!INCLUDE sdk-documentation]

CLI

[!INCLUDE sample-code] [!INCLUDE sdk-documentation]

Go

[!INCLUDE sample-code] [!INCLUDE sdk-documentation]

Java

[!INCLUDE sample-code] [!INCLUDE sdk-documentation]

JavaScript

[!INCLUDE sample-code] [!INCLUDE sdk-documentation]

PHP

[!INCLUDE sample-code] [!INCLUDE sdk-documentation]

PowerShell

[!INCLUDE sample-code] [!INCLUDE sdk-documentation]

Python

[!INCLUDE sample-code] [!INCLUDE sdk-documentation]

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

These advanced query capabilities are supported only on 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
    		•

    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

    • Works by default. Does not require advanced query parameters. The $filter operator works by default for that property.
    • 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.

    [!INCLUDE filter-directory-objects]

    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

    • Works by default. Does not require advanced query parameters. The $orderby operator works by default for that property.
    • 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
    administrativeUnit deletedDateTime
    administrativeUnit displayName
    application createdDateTime
    application deletedDateTime
    application displayName
    orgContact createdDateTime
    orgContact displayName
    device approximateLastSignInDateTime
    device createdDateTime
    device deletedDateTime
    device displayName
    group deletedDateTime
    group displayName
    servicePrincipal createdDateTime
    servicePrincipal deletedDateTime
    servicePrincipal displayName
    user createdDateTime
    user deletedDateTime
    user displayName
    user userPrincipalName
    [all others] [all others]

    Error handling for advanced queries on directory objects

    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 is used or silently ignores the $count query parameter (?$count=true) if it's used.

    HTTP

    GET https://graph.microsoft.com/v1.0/users/$count

    C#

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    CLI

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    Go

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    Java

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    JavaScript

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    PHP

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    PowerShell

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    Python

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    {
    "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.

    HTTP

    GET https://graph.microsoft.com/v1.0/applications?$search="displayName:Browser"

    C#

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    CLI

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    Go

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    Java

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    JavaScript

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    PHP

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    PowerShell

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    Python

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    {
    "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.

    HTTP

    GET https://graph.microsoft.com/beta/users?$filter=endsWith(userPrincipalName,'%23EXT%23@contoso.com')

    C#

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    CLI

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    Go

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    Java

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    JavaScript

    [!INCLUDE snippet-not-available] [!INCLUDE sdk-documentation]

    PHP

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    PowerShell

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    Python

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    {
    "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 hasn't been indexed to support a query parameter, even if the advanced query parameters are specified, the request returns an error.

    HTTP

    GET https://graph.microsoft.com/beta/groups?$filter=createdDateTime ge 2021-11-01&$count=true
ConsistencyLevel: eventual

    C#

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    CLI

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    Go

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    Java

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    JavaScript

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    PHP

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    PowerShell

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    Python

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    {
    "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.

    HTTP

    GET https://graph.microsoft.com/v1.0/users?$count=true

    C#

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    CLI

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    Go

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    Java

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    JavaScript

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    PHP

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    PowerShell

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

    Python

    [!INCLUDE sample-code] [!INCLUDE sdk-documentation]

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

    Related content