Query trend data with OData aggregation

Azure DevOps Services | Azure DevOps Server | Azure DevOps Server 2022

Analytics stores daily snapshots of every work item in two entity sets: WorkItemSnapshot (tracks field values like state and effort) and WorkItemBoardSnapshot (tracks board column positions). Because each entity contains one row per work item per day, these tables grow quickly. Use OData aggregation extensions to filter by date and group results before returning data to a client tool.

This article shows how to build trend queries by date range and by iteration, using $apply with filter, groupby, and aggregate.

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

Prerequisites

Query trend data by date range

When you query snapshot tables, follow two requirements:

1. Filter by date — each table contains one row per work item per day, so an unfiltered query returns a very large result set.
2. Group by date — if you omit the date grouping, the response includes a warning.

The following query returns a daily bug count by state for March 2016:

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

Returns:

{
  "value": [
    { "DateValue": "2016-03-01T00:00:00-08:00", "State": "Active", "Count": 2666 },
    { "DateValue": "2016-03-01T00:00:00-08:00", "State": "Closed", "Count": 51408 }
  ]
}

This query returns at most 31 days multiplied by the number of bug states (Active, Resolved, Closed) - 93 rows maximum, regardless of how many work items exist.

Query trend data by iteration

Instead of hard-coding dates, filter by iteration and reference its start and end dates so the date range adjusts automatically. The Iteration/EndDate eq null check handles iterations that don't have an end date yet.

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

Returns:

{
  "value": [
    { "DateValue": "2016-04-04T00:00:00-07:00", "State": "Active", "Count": 320 },
    { "DateValue": "2016-04-04T00:00:00-07:00", "State": "Closed", "Count": 38 }
  ]
}

Next step

Related content

• Aggregate work tracking data using Analytics
• Define basic queries using OData Analytics
• Construct OData queries for Analytics
• OData Analytics query guidelines
• OData Extension for Data Aggregation Version 4.0