Query trend data

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

Examining trends in data and making period-over-period comparisons are important aspects of reporting and data analysis. Analytics supports these capabilities.

Note

The Analytics service is automatically enabled for all Azure DevOps Services. It is supported for use in production. Power BI integration and access to the OData feed of the Analytics Service are generally available. We encourage you to use it and provide us feedback.

Note

The Analytics service is automatically installed on all new project collections for Azure DevOps Server 2020 and later versions. It is supported for use in production. Power BI integration and access to the OData feed of the Analytics Service are generally available. We encourage you to use it and provide us feedback. If you upgraded from Azure DevOps Server 2019, then you're provided with the option to install the Analytics service during upgrade.

Note

The Analytics service is in preview for Azure DevOps Server 2019. You access Analytics by enabling or installing it for a project collection. Power BI integration and access to the OData feed of the Analytics Service are in Preview. We encourage you to use it and provide us feedback.

Trend data is exposed in the WorkItemSnapshot and WorkItemBoardSnapshot entity sets. They're constructed so every work item, from the day it was created until today, exists for each day. For an organization with only one work item that was created a year ago, there are 365 rows in this entity. For large projects, these entities would be impractical to use with client tools.

What is the solution? Use the Aggregation Extensions.

In this article you'll learn:

  • How to construct a basic query for trend data

Using the OData Aggregation Extensions, you can return aggregated data from Azure DevOps that is conducive to reporting. For example, you could show bug trend for the month of March. Bug trends are a common and critical part of managing any project so you can put it to good use immediately.

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.

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

Note

Data available is version-dependent. The latest supported version is v2.0, and the latest preview version is v4.0-preview. To learn more, see OData API versioning.

Construct a basic query for trend data

There are some basic requirements you need to effectively query the WorkItemSnapshot table:

  • Filter the data by date.
  • The aggregation should group by, at the least, date. If not, response will have warning.

The query to create a bug trend report looks like the following example:

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}//WorkItemSnapshot?
  $apply=
    filter(DateValue ge 2016-03-01Z and DateValue le 2016-03-31Z and WorkItemType eq 'Bug')/
    groupby((DateValue,State), aggregate($count as Count))
  &$orderby=DateValue

It returns a result similar to the following example:

{
  "@odata.context": "https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}//$metadata#WorkItemSnapshot(DateValue,State,Count)",
  "value": [
    {
      "@odata.id": null,
      "State": "Active",
      "DateValue": "2016-03-01T00:00:00-08:00",
      "Count": 2666
    },
    {
      "@odata.id": null,
      "State": "Closed",
      "DateValue": "2016-03-01T00:00:00-08:00",
      "Count": 51408
    }
  ]
}

This query will produce at most 31 * (number of bug states). The default bug has three states:

  • Active
  • Resolved
  • Closed

At most, this query will return 93 rows no matter how many thousands of records actually exist. It provides a much more compact form of returning data.

Let's look at a variation on this example. You want to see the bug trend for an iteration or a release that starts with one iteration and ends with another.

To construct that query, do the following example:

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}//WorkItemSnapshot?
  $apply=
    filter(WorkItemType eq 'Bug')/
    filter(Iteration/IterationName eq 'Sprint 99')/
    filter(DateValue ge Iteration/StartDate and (Iteration/EndDate eq null or DateValue le Iteration/EndDate))/
    groupby((DateValue, State), aggregate($count as Count))
  &$orderby=DateValue

It returns a result similar to the following example:

{
  "@odata.context": "https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}//$metadata#WorkItemSnapshot(DateValue,State,Count)",
  "value": [
    {
      "@odata.id": null,
      "State": "Active",
      "DateValue": "2016-04-04T00:00:00-07:00",
      "Count": 320
    },
    {
      "@odata.id": null,
      "State": "Closed",
      "DateValue": "2016-04-04T00:00:00-07:00",
      "Count": 38
    }
  ]
}

In this query, there are two key differences. We added a filter clause to filter the data to a specific iteration and the dates are now being compared to the iteration start and end dates versus a hard-coded date.

Note

If aggregation is not used in your query on snapshot tables, you will see the warning "The specified query does not include a $select or $apply clause which is recommended for all queries." in the response.