Query your work tracking data using OData Analytics

Azure DevOps Services | 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 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 is in Preview. 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.

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 additional URL parts are specified as an additional part of the query string.

Prerequisites

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 v3.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, simply 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 

This is 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, this may take several seconds. If you have 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

This is 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'

Alternatively, 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'

Also, you can apply multiple filters by concatenating two or more filters. For example, 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.

Querying work items is helpful, but you will eventually want to filter by other data such as the Iteration Path, Area Path, or project. To do this, 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 is 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 include Revisions, BoardLocations (Kanban metadata), Project, Area, and Iteration.

Filter by a navigation property

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 is not 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 is 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

This returns the following:

{
  "@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. This is 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)

Which returns the following:

{
  "@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)

This results in:

{
  "@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)

This results in:

{
  "@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 will 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.

Try this next