Project and organization-scoped queries

Azure DevOps Services | Azure DevOps Server 2019

Using Analytics for Azure DevOps, you can construct project or organization-scoped queries to return work items of interest. You run these queries directly in your browser.

Project-scope queries help answer questions about a single project whereas organization-scope queries allow you to answer questions which cross project boundaries. Organization scoped queries require broader user permissions or careful scoping restrictions to ensure that your query isn't blocked due to a lack of project permissions.

Note

The Analytics service is generally available for all organizations using Azure DevOps Services. It provides several advanced widgets.

Power BI integration and access to the OData feed of the Analytics Service are in Preview. It is supported for use in production. We encourage you to use it and provide us feedback. As we add features, we will post them on the Microsoft DevOps Blog.

If you are looking for information about Azure Analysis Services, see Azure Analysis Services.

Note

The Analytics service is in preview for Azure DevOps Server 2019. While in preview, it is available to everyone free of charge. We encourage you to use it and provide us feedback. As we add features, we will post them on the Microsoft DevOps Blog.

You access Analytics by enabling or installing it for a project collection. The Analytics service provides several advanced widgets, Power BI integration, and access to the OData feed.

If you are looking for information about Azure Analysis Services, see Azure Analysis Services.

Prerequisites

Project-scoped queries

You construct a query by entering the OData URL into a supported web browser.

The base URL for project level queries is:

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/

In the examples provided, replace {OrganizationName} and {ProjectName} with your organization name and the name of the project that you want to query.

https://{servername}:{port}/tfs/{OrganizationName}/{ProjectName}/_odata/{version}/

Note

The examples shown in this document are based on a Azure DevOps Services URL, you will need to substitute in your Azure DevOps Server URL.

In the examples provided, make the following replacements:

  • analytics.dev.azure.com with {ServerName}:{Port}/tfs/
  • {OrganizationName} with your project collection name (default is DefaultCollection)
  • {ProjectName} with the name of the project that you want to query.

Note

The {version} value is formatted as v1.0. The latest supported version is v2.0, and the latest preview version is v3.0-preview. For more information, see OData API versioning.

Return a count of work items

For example, the following project-scoped query will return the count of work items for a specific project:

https://analytics.dev.azure.com/{OrganizationName}/ProjectA/_odata/v1.0/WorkItems/$count

Note

If you don't have access to all projects in an organization, it is recommended to apply a project filter to all of your queries. When pulling data into client tools such as Power BI Desktop or Excel, using the project path syntax is the best way to ensure that all your data is constrained by the given project. We recommend you use organization-scoped queries only when you need to report on two or more projects.

Return the areas defined for a project

Likewise, the following query string will return the areas for a specific project:

https://analytics.dev.azure.com/{OrganizationName}/ProjectA/_odata/v1.0/Areas

This is equivalent to the following filter on an organization-scoped query:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/Areas?
  $filter=Project/ProjectName eq 'ProjectA'

Use of the $expand option

When using a project-scoped query with an $expand option you aren't required to provide additional filters.

For example, the following project-scoped filter:

https://analytics.dev.azure.com/{OrganizationName}/ProjectA/_odata/v1.0/WorkItems?
  $expand=Parent

is filtered automatically to enforce security:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $filter=ProjectName eq 'ProjectA'
  &$expand=Parent($filter=ProjectName eq 'ProjectA')

Organization-scoped queries

The Base URL for organization level queries is as shown:

https://analytics.dev.azure.com/{OrganizationName}/_odata/v1.0

When using an organization-scoped query with an $expand option you must provide an additional filter.

For example, the following organization-scoped query, which uses an $expand to retrieve the children of all work items…

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $filter=Project/ProjectName eq 'ProjectA'
  &$expand=Children

…requires an additional filter to verify the children are limited to the specified project:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $filter=Project/ProjectName eq 'ProjectA'
  &$expand=Children($filter=Project/ProjectName eq 'ProjectA')

Return the parent of all work items

The following query, which uses an $expand option to retrieve the parent of all work items…

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $filter=Project/ProjectName eq 'ProjectA'
  &$expand=Parent

requires an additional filter to verify the parent is limited to the specified project:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $filter=Project/ProjectName eq 'ProjectA'
  &$expand=Parent($filter=Project/ProjectName eq 'ProjectA')

Without the additional filter, the request will fail if the parent of any work item references work items in a project that you do not have read access to.

Project-level security restrictions

Analytics has a few additional restrictions on query syntax related to project level security.

The any or all filters apply to the base Entity on an $expand. For filters based on a Project we explicitly ignore the filter when using an $expand:

For example, the following query…

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $filter=ProjectName eq 'ProjectA'
  &$expand=Children($filter=Project/ProjectName eq 'ProjectA')

…is interpreted as:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $filter=ProjectName eq 'ProjectA'
  &$expand=Children

and will fail if you don't have access to all projects.

To workaround the restriction, you need to add an extra expression in the $filter:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $filter=ProjectName eq 'ProjectA' and Children/any(r: r/ProjectName eq 'ProjectA')
  &$expand=Children

Using $level is only supported if you have access to all projects in the collection or when using a project-scoped query:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $expand=Children($levels=2;$filter=ProjectName eq 'ProjectA')

Analytics does not support any cross-level reference for projects using $it alias. As an example, the following query references the root work item's ProjectName using $it alias, which isn't supported:

https://analytics.dev.azure.com/{OrganizationName}/_odata/{version}/WorkItems?
  $expand=Links(
    $expand=TargetWorkItem;
    $filter=TargetWorkItem/Project/ProjectName eq $it/Project/ProjectName)

Try this next