Query your work tracking data using OData Analytics

Article
10/01/2021
8 minutes to read

Azure DevOps Services | Azure DevOps Server 2020 | Azure DevOps Server 2019

Using Analytics for Azure DevOps, you can construct basic and filtered queries to return work items of interest. You can run these queries directly in your browser.

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 in Preview. 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. It is supported for use in production. 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. 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.

In this article, the base root URL is scoped to a project as shown:

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

https://{ServerName}:{Port}/tfs/{CollectionName}/{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.

All other URL parts are specified as an extra part of the query string.

Prerequisites

You'll need to have a project in Azure DevOps. If you don't have one, see Sign up for free.
If you haven't been added as a project member, get added now.
Have the View Analytics permission set to Allow. See Grant permissions to access Analytics.
You'll have to have defined several work items. See Plan and track work.

Verify that Analytics] is installed, and if not, then enable it. You must be an account owner or a member of the Project Collection Administrator group to add extensions or enable the service.
You must be a member of a project. If you don't have a project yet, create one.
If you haven't been added as a project member, get added now.
Have the View Analytics permission set to Allow. See Grant permissions to access Analytics.
You'll have to have defined several work items. See Plan and track work.

Construct a basic query

You construct a basic query by entering the OData URL into a supported web browser. In the examples provided, replace {OrganizationName} and {ProjectName} with your organization name and the name of the project that you want to query.

You construct a basic query by entering the OData URL into a supported web browser. 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 v4.0-preview. For more information, see OData API versioning.

Query a single entity set

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

For example, you query Areas by adding /Areas. The full URL is:

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

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 work items, it may take several seconds. If you've more than 10000 work items, server-side paging will be enforced.

Select specific columns or fields

Return specific field data by adding a $select clause.

For example, to return only the Work Item ID, Work Item Type, Title, and State of work items, add this clause to your query:

/WorkItems?$select=WorkItemId,WorkItemType,Title,State

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/WorkItems?$select=WorkItemId,WorkItemType,Title,State

It's equivalent to selecting all rows in the entity, but returning only these specific fields.

Note

Field names don't contain any spaces. Your query will fail if you add spaces. OData queries require attention is paid to both spacing and casing.

Filter your data

You can filter data by providing a query filter clause. Building on the last query, you would add the following filter clause to only return those work items to return the state "In Progress".

/WorkItems?$select=WorkItemId,WorkItemType,Title,State&$filter=State eq 'In Progress'

With the full OData query:

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/WorkItems?$select=WorkItemId,WorkItemType,Title,State&$filter=State eq 'In Progress'

Instead, you can exclude the $select clause altogether and just filter the results as follows:

/WorkItems?$filter=State eq 'In Progress'

With the full OData query:

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/WorkItems?$filter=State eq 'In Progress'

You can also apply multiple filters by concatenating two or more filters. Here, we filter for In Progress tasks:

/WorkItems?$filter=WorkItemType eq 'Task' and State eq 'In Progress'

With the full OData query:

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/WorkItems?$filter=WorkItemType eq 'Task' and State eq 'In Progress'

Additionally, you can apply various functions such as contains, startswith, endswith and more. See the Supported OData features and clauses, Supported functions.

Date range queries

The following example returns work items whose Changed Date is greater than equal to January 1, 2021.

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/WorkItems?$select=WorkItemId,WorkItemType,Title,State&$filter=ChangedDate ge 2021-01-01Z

The following example returns work items whose Changed Date occurs during the week of April 26 through April 30, 2021.

https://analytics.dev.azure.com{OrganizationName}/{ProjectName}/_odata/{version}/WorkItems?$select=WorkItemId,WorkItemType,Title,State&$filter=ChangedDate ge 2021-04-26Z&ChangedDate le 2021-04-30Z

Querying work items is helpful, but you'll eventually want to filter by other data such as the Iteration Path, Area Path, or project. To do so, you need to understand the navigation properties of the entity model. You can get metadata using /$metadata URL. For details, see Explore Analytics OData metadata

Here's a partial view of the metadata for the Work Items entity:

    <Property ...>
    <Property Name="RequirementType" Type="Edm.String"/>
    <Property Name="RequiresReview" Type="Edm.String"/>
    <Property Name="RequiresTest" Type="Edm.String"/>
    <Property Name="RootCause" Type="Edm.String"/>
    <Property Name="SubjectMatterExpert1" Type="Edm.String"/>
    <Property Name="SubjectMatterExpert2" Type="Edm.String"/>
    <Property Name="SubjectMatterExpert3" Type="Edm.String"/>
    <Property Name="TargetResolveDate" Type="Edm.DateTimeOffset"/>
    <Property Name="TaskType" Type="Edm.String"/>
    <Property Name="UserAcceptanceTest" Type="Edm.String"/>
    <Property Name="Count" Nullable="false" Type="Edm.Int32"/>
    <NavigationProperty Name="Revisions" Type="Collection(Microsoft.VisualStudio.Services.Analytics.Model.WorkItemRevision)"/>
    <NavigationProperty Name="BoardLocations" Type="Collection(Microsoft.VisualStudio.Services.Analytics.Model.BoardLocation)"/>
    <NavigationProperty Name="Project" Type="Microsoft.VisualStudio.Services.Analytics.Model.Project"/>
    <NavigationProperty Name="Area" Type="Microsoft.VisualStudio.Services.Analytics.Model.Area"/>
    <NavigationProperty Name="Iteration" Type="Microsoft.VisualStudio.Services.Analytics.Model.Iteration"/>

The navigation properties appear towards the bottom of the metadata, which includes Revisions, BoardLocations (Kanban metadata), Project, Area, and Iteration.

How do you use navigation properties to filter results?

Use the following clause to filter work items based on a specific iteration, for example Iteration 1:

/WorkItems?$filter=Iteration/IterationPath eq 'Project Name\Iteration 1'

With the full OData query:

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/WorkItems?$filter=Iteration/IterationPath eq 'Project Name\Iteration 1'

In this example, Iteration is the navigation property name and IterationPath corresponds to the full path for the iteration. To use another entity as a filter, put the navigation property followed by a slash followed by the name of the field to filter on.

How do you use navigation properties to select related fields?

The username for Custom fields based on an Identity isn't directly accessible using a $select statement. The following query uses a $expand statement to retrieve the user name:

/WorkItems?$expand=MyIdentityField($select=UserName)

Note

You can't use the navigation property directly in a $select statement. Instead, you will need to use $expand.

The previous filtering example for the Iteration Path doesn't return the iteration path in the results because it's contained in a related entity. To return data in a related entity, add an $expand statement:

/WorkItems?$select=WorkItemId,WorkItemType,Title,State&$filter=WorkItemId eq 10000&$expand=Iteration

It returns the following JSON:

{
  "@odata.context":"https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/$metadata#WorkItems(WorkItemId,WorkItemType,Title,State,Iteration)",
  "value":[
    {
      "WorkItemId":10000,
      "WorkItemType":"Task",
      "Title":"Some title",
      "State":"Completed",
      "Iteration":{
        "IterationId":"7a2c246e-fc62-41af-ad18-62332017bc46",
        "Name":"Sprint 55",
        "Number":13021,
        "IterationPath":"Fabrikam\\Sprints\\Sprint 55",
        "StartDate":"2013-09-23T00:00:00Z",
        "EndDate":"2013-10-11T00:00:00Z",
        "IterationLevel1":"Fabrikam",
        "IterationLevel2":"Sprints",
        "IterationLevel3":"Sprint 55",
        "Level":2,
        "IsDeleted":false
      }
    }
  ]
}

As you can see, the Iteration Path is expanded in the JSON result and all of the iteration data is returned. It's probably more data than you want.

To return less data, add a $select statement against the iteration as well:

/WorkItems?$select=WorkItemId,WorkItemType,Title,State&$filter=WorkItemId eq 10000&$expand=Iteration($select=Name,IterationPath)

It returns the following JSON:

{
  "@odata.context":"https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/$metadata#WorkItems(WorkItemId,WorkItemType,Title,State,Iteration,Iteration(Name,IterationPath))",
  "value":[
    {
      "WorkItemId":10000,
      "WorkItemType":"Task",
      "Title":"Some title",
      "State":"Completed",
      "Iteration":{
        "Name":"Sprint 55",
        "IterationPath":"Fabrikam\\Sprints\\Sprint 55"
      }
    }
  ]
}

In OData, you can nest $expand statements. For example, you can write the previous query statement to display the project the iteration is part of:

/WorkItems?$filter=WorkItemId eq 10000&$expand=Iteration($expand=Project)

It returns the following JSON:

{
  "@odata.context":"https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/$metadata#WorkItems",
  "value":[
    {
      "WorkItemId":10000,
      "Revision":3,
      "Watermark":283397,
      "Title":"Production deployment and testing for Entitlement API v2 and Subscriber database",
      "WorkItemType":"Task",
      "ChangedDate":"2014-07-10T19:29:58.41Z",
      "CreatedDate":"2014-04-19T22:44:58.31Z",
      "State":"Completed",
      "Reason":"Completed",
      "Priority":2,
      "CompletedWork":10.0,
      "OriginalEstimate":20.0,
      "Count":1,
      "Iteration":{
        "IterationId":"7a2c246e-fc62-41af-ad18-62332017bc46",
        "Name":"Sprint 55",
        "Number":13021,
        "IterationPath":"Fabrikam\\Sprints\\Sprint 55",
        "StartDate":"2013-09-23T00:00:00Z",
        "EndDate":"2013-10-11T00:00:00Z",
        "IterationLevel1":"Fabrikam",
        "IterationLevel2":" Sprints",
        "IterationLevel3":"Sprint 55",
        "Level":2,
        "IsDeleted":false,
        "Project":{
          "ProjectId":"b924d696-3eae-4116-8443-9a18392d8544",
          "ProjectName":"Fabrikam",
          "IsDeleted":false
        }
      }
    }
  ]
}

You can also combine $expand and $select statements. For example, you can change the previous query to only return the Iteration Name and Iteration Path:

/WorkItems?$filter=WorkItemId eq 10000&$expand=Iteration($select=IterationId,IterationPath;$expand=Project)

It returns the following JSON:

{
  "@odata.context":"https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/$metadata#WorkItems(Iteration(IterationId,IterationPath,Project))",
  "value":[
    {
      "WorkItemId":10000,
      "Revision":3,
      "Watermark":283397,
      "Title":"Production deployment and testing for Entitlement API v2 and Subscriber database","WorkItemType":"Task",
      "ChangedDate":"2014-07-10T19:29:58.41Z",
      "CreatedDate":"2014-04-19T22:44:58.31Z",
      "State":"Completed",
      "Reason":"Completed",
      "Priority":2,
      "CompletedWork":10.0,
      "OriginalEstimate":20.0,
      "Count":1,
      "Iteration":{
        "IterationId":"7a2c246e-fc62-41af-ad18-62332017bc46","IterationPath":"Fabrikam\\Sprints\\Sprint 55",
        "Project":{
          "ProjectId":"b924d696-3eae-4116-8443-9a18392d8544",
          "ProjectName":"Fabrikam",
          "IsDeleted":false
        }
      }
    }
  ]
}

Notice that the result here shows only the IterationId and IterationPath and that the Project is a nested object within the JSON result. Another key item to note is the URL itself. When using a $select statement and an $expand clause, you must use a semi-colon (;) before the $expand. Anything else will result in an error.

Sort results

You can sort OData results using the $orderby clause. You can apply this clause to any OData query as shown:

/WorkItems?$orderby=WorkItemId

You can sort in ascending or descending order using keywords asc or desc correspondingly:

/WorkItems?&$orderby=WorkItemId desc

And, you can order by multiple items:

/WorkItems?$orderby=WorkItemType,State

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

Project & organization-scoped queries

Query your work tracking data using OData Analytics

Prerequisites

Construct a basic query

Query a single entity set

Select specific columns or fields

Filter your data

Date range queries

Filter by a navigation property

Sort results

Enforce server-side paging

Next steps

Feedback

Query your work tracking data using OData Analytics

Please rate your experience

Prerequisites

Construct a basic query

Query a single entity set

Select specific columns or fields

Filter your data

Date range queries

Filter using related entities

Filter by a navigation property

Return data from related entities

Sort results

Enforce server-side paging

Next steps

Related articles

Feedback