Edit

Construct OData queries for Analytics in Azure DevOps

  • Article

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019

Analytics, the reporting platform for Azure DevOps, can answer quantitative questions about the past or present state of your projects. Analytics supports OData queries of its metadata and entity set data. By exercising simple queries from your web browser, you can get familiar with the data model and query process.

In this article you'll learn the following:

  • How to construct an Analytics OData query for the cloud or on-premises
  • How to query Analytics metadata
  • How to query Analytics OData for an entity set
  • Which query options are supported and the recommended sequence
  • When server-side paging is enforced

You can query Analytics from any supported web browser. For other tools you can use to query Analytics, see Analytics query tools.

Note

OData, an application-level protocol for interacting with data via RESTful (where REST=Representational State Transfer) interfaces), supports the description of data models as well as editing and querying of data according to those models. The Entity Data Model (EDM) or metadata describes the information available from Analytics, including the entities, entity types, properties, relationships, and enumerations you use to query the data to build reports. For an overview of OData, see Welcome to OData.

Prerequisites

  • To view Analytics data and query the service, you need to be a member of a project with Basic access or greater. By default, all project members are granted permissions to query Analytics and define Analytics views.
  • To learn about other prerequisites regarding service and feature enablement and general data tracking activities, see Permissions and prerequisites to access Analytics.

URL components to query the metadata

Analytics exposes the entity model at the metadata URL, formed by appending $metadata to the service root URL. Analytics provides service roots for a project or an entire organization in Azure DevOps.

You can look up any of the following data elements by querying the metadata.

  • Entity types and entity sets
  • Properties and navigation properties
  • Surrogate keys
  • Enumerated lists
  • EntitySet
  • Containers
  • Filter functions (Org.OData.Capabilities.V1.FilterFunctions)
  • Supported aggregations (Org.OData.Aggregation.V1.ApplySupported)
  • Batch support (Org.OData.Capabilities.V1.BatchSupportType)

The URL you use depends on whether you are querying data for Azure DevOps Services (cloud) or an on-premises Azure DevOps Server.

To query the metadata for an organization or project hosted in the cloud, enter the URL syntax as shown below in a web browser. Replace {OrganizationName} and {ProjectName} with your organization name and the name of the project that you want to query. To return all metadata for the organization, don't specify the project name.

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/version/$metadata 
\______________________________/\______________________________/\______________/\_________/
               |                                 |                       |           |
    Analytics service root URL         Organization/Project        OData version  return metadata

Note

The latest Analytics OData version is v4.0-preview. You can use this version for all queries against the hosted service. For more information on Analytics versions and available data, see Data model for Analytics.

Here's an example for the fabrikam organization that is hosted on Azure DevOps Services.

https://analytics.dev.azure.com/fabrikam/_odata/v4.0-preview/$metadata

Interpret the metadata response

Analytics returns an XML file of the data model. Use your browser search function to find information specific to the entity of interest.

Tip

Depending on the browser you're using, this file may or may not be formatted in a readable manner. If it isn't formatted, you can find a free online XML formatter through a web browser search.

The two main schemas defined in the Analytics metadata are Microsoft.VisualStudio.Services.Analytics.Model, which defines the entity types and enumerated types and their members, and the Default schema, which defines the entity containers and entity sets and supported OData filter, transformation, and custom aggregation functions. For more information, see Analytics OData metadata.

<?xml version="1.0" encoding="UTF-8"?>
<edmx:Edmx xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx" Version="4.0">
    <edmx:DataServices>
        <Schema xmlns="http://docs.oasis-open.org/odata/ns/edm" Namespace="Microsoft.VisualStudio.Services.Analytics.Model">
           <EntityType Name="Entity Name"/>
        </Schema>
        <Schema xmlns="http://docs.oasis-open.org/odata/ns/edm" Namespace="Default">
           <EntityContainer Name="Container"/>
        </Schema>
    </edmx:DataServices>
</edmx:Edmx>

All entity types are associated with properties and navigation properties. You can filter your queries of entity sets using both these types of properties. These are listed in the metadata under the EntityType as a Property or NavigationalProperty. You use related entities to specify additional filters, such as Iteration Paths, Area Paths, or Teams.

The following code snippet provides a partial view of the metadata for the WorkItem entity. Properties correspond to a work item field as well as specific data captured by Analytics, such as LeadTimeDays and CycleTimeDays. Navigation properties correspond to other entity sets, or specific Analytics data captured for the entity type, such as Revisions, Links, Children, and Parent.

<Key>
   <PropertyRef Name="WorkItemId"/>
</Key>
<Property Name="WorkItemId" Type="Edm.Int32" Nullable="false">
   <Annotation Term="Ref.ReferenceName" String="System.Id"/>
   <Annotation Term="Display.DisplayName" String="Work Item Id"/>
</Property>
<Property Name="InProgressDate" Type="Edm.DateTimeOffset">
   <Annotation Term="Display.DisplayName" String="InProgress Date"/>
   </Property>
<Property Name="CompletedDate" Type="Edm.DateTimeOffset">
   <Annotation Term="Display.DisplayName" String="Completed Date"/>
   </Property>
<Property Name="LeadTimeDays" Type="Edm.Double">
   <Annotation Term="Display.DisplayName" String="Lead Time Days"/>
</Property>
<Property Name="CycleTimeDays" Type="Edm.Double">
   <Annotation Term="Display.DisplayName" String="Cycle Time Days"/>
</Property>
<Property Name="InProgressDateSK" Type="Edm.Int32"/>
<Property Name="CompletedDateSK" Type="Edm.Int32"/>
<Property Name="AnalyticsUpdatedDate" Type="Edm.DateTimeOffset"/>
<Property Name="ProjectSK" Type="Edm.Guid" Nullable="false"/>
<Property Name="WorkItemRevisionSK" Type="Edm.Int32" Nullable="false"/>
...
<NavigationProperty Name="BoardLocations" Type="Collection(Microsoft.VisualStudio.Services.Analytics.Model.BoardLocation)"/>
<NavigationProperty Name="Teams" Type="Collection(Microsoft.VisualStudio.Services.Analytics.Model.Team)"/>
<NavigationProperty Name="InProgressOn" Type="Microsoft.VisualStudio.Services.Analytics.Model.CalendarDate">
<ReferentialConstraint Property="InProgressDateSK" ReferencedProperty="DateSK"/>
</NavigationProperty>
<NavigationProperty Name="CompletedOn" Type="Microsoft.VisualStudio.Services.Analytics.Model.CalendarDate">
<ReferentialConstraint Property="CompletedDateSK" ReferencedProperty="DateSK"/>
</NavigationProperty>
<NavigationProperty Name="Revisions" Type="Collection(Microsoft.VisualStudio.Services.Analytics.Model.WorkItemRevision)"/>
<NavigationProperty Name="Links" Type="Collection(Microsoft.VisualStudio.Services.Analytics.Model.WorkItemLink)"/>
<NavigationProperty Name="Children" Type="Collection(Microsoft.VisualStudio.Services.Analytics.Model.WorkItem)"/>
<NavigationProperty Name="Parent" Type="Microsoft.VisualStudio.Services.Analytics.Model.WorkItem">
<ReferentialConstraint Property="ParentWorkItemId" ReferencedProperty="WorkItemId"/>
</NavigationProperty>
<NavigationProperty Name="Processes" Type="Collection(Microsoft.VisualStudio.Services.Analytics.Model.Process)"/>
<NavigationProperty Name="Descendants" Type="Collection(Microsoft.VisualStudio.Services.Analytics.Model.WorkItem)"/>
<NavigationProperty Name="Project" Type="Microsoft.VisualStudio.Services.Analytics.Model.Project" Nullable="false">
<ReferentialConstraint Property="ProjectSK" ReferencedProperty="ProjectSK"/>
<Annotation Term="Display.DisplayName" String="Project"/>
...

URL components to query entities

To query Analytics data and build reports, you typically query an entity set. For an overview of supported entities, see Analytics OData metadata.

The following URL is used to query a specific EntitySet, such as WorkItems, WorkItemSnapshot, and PipelineRuns.

https://analytics.dev.azure.com/OrganizationName/ProjectName/_odata/version/EntityType?{Query-options}
\______________________________/\__________________________/ \____________/\_________/\_____________/
               |                             |                    |               |          |
    Analytics service root URL     Organization/Project      OData version    Entity   Query parts

Here's an example for the fabrikam organization that returns the count of work items defined for the Fabrikam Fiber project.

https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/WorkItems?%20$apply=aggregate($count%20as%20Count)

The example return 1399 work items.

{
"@odata.context": "https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/$metadata#WorkItems(Count)",
"value": [
   {
   "@odata.id": null,
   "Count": 1399
   }
 ]
}

Note

If you don't include a $select or $apply clause, you'll receive a warning, such as "VS403507: The specified query does not include a $select or $apply clause which is recommended for all queries. Details on recommended query patterns are available here: https://go.microsoft.com/fwlink/?linkid=861060." It's equivalent to performing a select statement on the entity set and returning everything, all columns and all rows. If you have a large number of records, it may take several seconds. If you've more than 10,000 work items, server-driven paging is enforced.

To avoid running into usage limits, always include a $select or $apply clause.

For entity metadata property and relationship information, see the following articles:

Example: Query a specific entity set

To query a specific entity set, such as WorkItems, Areas, or Projects, add the name of the entity set: /WorkItems, /Areas, or /Projects. For a full list of entity sets, see Data model for Analytics.

For example, you can get a list of projects defined for your organization by querying /Projects and selecting to return the ProjectName property. For the fabrikam organization, the URL is as shown below.

  https://analytics.dev.azure.com/fabrikam/_odata/v4.0-preview/Projects?$select=ProjectName

Analytics returns the project names of those projects defined for the fabrikam organization.

{
@odata.context	"https://analytics.dev.azure.com/fabrikam/_odata/v4.0-preview/$metadata#Projects(ProjectName)",

"value": [
   {
      "ProjectName": "Basic Fabrikam"
   },
   {
      "ProjectName": "Fabrikam Fiber"
   },
   {
      "ProjectName": "MyFirstProject"
   },
   {
      "ProjectName": "Fabrikam Test"
   },
   {
      "ProjectName": "MyPublicProject"
   }
 ]
}

Query options

A query option is a set of query string parameters applied to a resource that can help control the amount of data being returned for the resource in the URL.

Query options should be specified in the order listed in the following table.

Query option Notes
$apply Set of transformations that you can apply to a query, such as: filter, groupby, aggregate, compute, expand, concat
For examples, see Aggregate work tracking data using Analytics.
$compute A supported OData function that you can specify to define computed properties that can be used in a $select,$filter, or $orderby expression.
$filter Use to filter the list of resources that are returned. The expression specified with $filter is evaluated for each resource in the collection, and only items where the expression evaluates to true are included in the response. Resources for which the expression evaluates to false or to null, or which reference properties that are unavailable due to permissions, are omitted from the response.
For examples, see Query work tracking data using Analytics .
$orderby Use to specify the sequence in which records should be returned.
For examples, see Query work tracking data using Analytics.
$top/$skip Use to limit the number of records returned.
For examples, see Project and organization-scoped queries.
$select/$expand Use $select to specify the columns you need to build your report. Use $expand to nest other query options. Each expandItem is evaluated relative to the entity containing the navigation or stream property being expanded.

Semicolon-separated list of query options, enclosed in parentheses, to the navigation property name. Allowed system query options are $filter, $select, $orderby, $skip, $top, $count, $search, and $expand.
For examples, see Query work tracking data using Analytics.
$skiptoken Use to skip a specified number of records.
$count or $count=true Enter $count to only return the number of records. Enter $count=trueto return both a count of the record and the queried data.
For examples, see Aggregate work tracking data using Analytics.

Tip

Avoid mixing $apply and $filter clauses in a single query. To filter your query, you have two options: (1) use a $filter clause or (2) use a $apply=filter() combination clause. Each one of these options works great on its own, but combining them together might lead to some unexpected results.

Enforce server-side paging

Analytics forces paging when query results exceed 10000 records. In that case, you'll get first page of data and link to follow to get next page. Link (@odata.nextLink) can be found at the end of the JSON output. It will look like an original query followed by $skip or $skiptoken. For example:

{
  "@odata.context":"https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/$metadata#WorkItems",
  "value":[
   // 10000 values here
  ],
  "@odata.nextLink":"https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/WorkItems?$skiptoken=10000"
}

Note

When pulling data into client tools such as Power BI Desktop or Excel, tools will automatically follow next link and load all required records.

Next steps

Construct basic queries using OData Analytics